8/16/2023 0 Comments Run time error 1004 excel macro![]() The correct way is to name the sheet you wish to protect and then use only one line of code in place of two:- Worksheets("My Tab").Protect Of course, it is far too complicated to first make a sheet active and then refer to it as the ActiveSheet, to wit, Worksheets(ActiveSheet.Name).Select The presumed purpose of the line throwing the error is to make sure that the correct worksheet is being protected in the next line. If no workbooks are opened there test whether you get the same result when only a single workbook is open when you run your code. Look for changes made in the sub routines CurrentBaseline, TemplateTotal and TemplateGraph. ![]() Nor would you be able to select one that is hidden. It stands to reason that you wouldn't be able to select a worksheet in a workbook that isn't active. Perhaps the intervening subs change the ActiveWorkbook. Therefore the ActiveSheet is the worksheet active in the ActiveWorkbook. My guess is that this has to do with the fact that your code specifies no workbook. The reason is that the sheet can't be selected. As you say, it used to cause no objection but it does now. Even if it would work it wouldn't do anything.īut it doesn't work. Therefore it is wrongly positioned in the sub you post and, anyway, Worksheets(ActiveSheet.Name).Select doesn't change the ActiveSheet. Logically, reinstating the ActiveSheet (the one which was unprotected) should happen in the sub routine where the new sheet is inserted. Dim MySheet As WorkSheet, NewSheet As Worksheet If you don't want the new sheet to remain active use code like this. This is necessary so that you can access it before it has a name. ![]() Excel will make the new sheet the Activesheet. An exception to the rule is when you insert a new sheet. You can write code like MyTotal = Worksheets("My Tab").Cells(2, "C").Value + Worksheets("Your Tab").Cells(27, "FF").Value. As I said, you can access any worksheet using VBA without making it active. The procedures CurrentBaseline, TemplateTotal and TemplateGraph might change the ActiveSheet. Better code would specify the worksheet, like Worksheets("My Tab").Unprotect. Therefore it is applied to the ActiveSheet. In your code, the Unprotect command specifies no sheet. You (almost) never need the Selection object. Therefore the macro recorder will create a lot of Selection objects but when you write code imitating that method is cumbersome. To put it in a nutshell, Excel's prefers the Selection object for worksheet manipulation while VBA prefers the Range object. But before you use the Selection object you should try to make do with the Range object which is a lot easier to handle and has many methods and properties the Selection object lacks. ![]() Therefore, using VBA, you wouldn't ever select anything unless your intention is to use the Selection object thereby created. The Select method creates a Selection object which has a (very) few methods the more commonly used Range object doesn't have. All parts of the workbook - in fact all loaded workbooks - are accessible to VBA without being either activated or selected. And you don't need to select the ActiveSheet because in VBA you don't need to select anything at all. You don't need to activate the ActiveSheet because it's already active. What's the purpose of Worksheets(ActiveSheet.Name).Selectin your code? Read up on the difference between Activate and Select. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |