Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run time error 1004: Select method of worksheet class failed

0

Hi

After update of excel to the office 365 version I start getting the following error "Run time error 1004: Select method of worksheet class failed" when trying to executed the pasted script . Any ideas what can be wrong?

Sub UpdateTemplate()
Application.ScreenUpdating = False
Unprotect
CurrentBaseline
TemplateTotal
TemplateGraph
Worksheets(ActiveSheet.Name).Select
Protect
Application.ScreenUpdating = True
End Sub

Thanks

Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Tags: Tags were updated to reflect the topic of the question.
Answer
Discuss

Discussion

Is the worksheet protected with a password? If you can upload a sample file, it will take almost no time to give you the exact cause of the issue (probably lol).
don (rep: 1989) Jul 25, '19 at 10:43 am
Hi again

A test file has been created but how do I upload?
ticotion (rep: 2) Jul 26, '19 at 4:21 am
Click "Edit" at the bottom of your question and on the next screen there is a button at the bottom to add files. Once you do that, make sure to submit the changes.
don (rep: 1989) Jul 26, '19 at 8:36 am
Hi

Thank you. There is now uploaded an sample file. Press the update buttom twice in the opening sheet and you will get the error message.
ticotion (rep: 2) Jul 29, '19 at 2:55 am
Hi
Thank you very much for your answer. It helped me do a quick fix for this problem realizing that alot of work has to be done to make the code better.
I basically replaced the protect and unprotect sub called in the Sub update Template () with ActiveWorkbook.ActiveSheet.Unprotect and deleted the Worksheets(sheetname).Select. It seems to work.
Thank you very much for your help
ticotion (rep: 2) Jul 30, '19 at 3:34 am
Add to Discussion

Answers

0
Selected Answer

What's the purpose of Worksheets(ActiveSheet.Name).Select in your code? Read up on the difference between Activate and Select.

You don't need to activate the ActiveSheet because it's already active. And you don't need to select the ActiveSheet because in VBA you don't need to select anything at all. All parts of the workbook - in fact all loaded workbooks - are accessible to VBA without being either activated or selected. The Select method creates a Selection object which has a (very) few methods the more commonly used Range object doesn't have. Therefore, using VBA, you wouldn't ever select anything unless your intention is to use the Selection object thereby created. 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.

To put it in a nutshell, Excel's prefers the Selection object for worksheet manipulation while VBA prefers the Range object. Therefore the macro recorder will create a lot of Selection objects but when you write code imitating that method is cumbersome. You (almost) never need the Selection object. In your code you certainly don't.

In your code, the Unprotect command specifies no sheet. Therefore it is applied to the ActiveSheet. Better code would specify the worksheet, like Worksheets("My Tab").Unprotect.

The procedures CurrentBaseline, TemplateTotal and TemplateGraph might change the ActiveSheet. If they do it isn't good programming. As I said, you can access any worksheet using VBA without making it active. You can write code like MyTotal = Worksheets("My Tab").Cells(2, "C").Value + Worksheets("Your Tab").Cells(27, "FF").Value. An exception to the rule is when you insert a new sheet. Excel will make the new sheet the Activesheet. This is necessary so that you can access it before it has a name. If you don't want the new sheet to remain active use code like this.

Dim MySheet As WorkSheet, NewSheet As Worksheet

Application.ScreenUpdating = False
Set MySheet = ActiveSheet
Set NewSheet = Worksheets.Add
NewSheet.Name = "My new sheet"
MySheet.Activate
Application.ScreenUpdating = True

Logically, reinstating the ActiveSheet (the one which was unprotected) should happen in the sub routine where the new sheet is inserted. Therefore it is wrongly positioned in the sub you post and, anyway, Worksheets(ActiveSheet.Name).Select doesn't change the ActiveSheet. Even if it would work it wouldn't do anything.

But it doesn't work. As you say, it used to cause no objection but it does now. The reason is that the sheet can't be selected. My guess is that this has to do with the fact that your code specifies no workbook. Therefore the ActiveSheet is the worksheet active in the ActiveWorkbook. Perhaps the intervening subs change the ActiveWorkbook. It stands to reason that you wouldn't be able to select a worksheet in a workbook that isn't active. Nor would you be able to select one that is hidden. Look for changes made in the sub routines CurrentBaselineTemplateTotal and TemplateGraph. 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.

The presumed purpose of the line throwing the error is to make sure that the correct worksheet is being protected in the next line. 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
Protect

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

or

ThisWorkbook.Worksheets("My Tab").Protect

or

ActiveWorkbook.Worksheets("My Tab").Protect

As you see, the sheet being unprotected and then protected again doesn't need to be active at any time. But if you choose not to specify the sheet you unprotect, or the workbook it is in, then it is desirable that the ActiveSheet which is being unprotected at the start should still be the same one that is being protected at the end.

Edit 30 Jul 2019 ======================================

Here is the bad news, Mikkel. You will not only actually have to work your way through what I posted above but I have also added lots of comments to your code in the NewCode module. Use the Edit/Find function to look for ''' (3 consecutive apostrophes) which I used to mark all my comments. I think you will find the effort rewarding.

The point is that you use the Select method to activate sheets. Then you use the ActiveSheet object to specify the sheet you want to work on. This idea is wrong on two counts. (1) Don't use Select when you mean Activate. (2) Don't use ActiveSheet when you mean Worksheets("Portfolio"). The latter is the most positive way of identifying a sheet. VBA has made it even easier for you by giving each worksheet a CodeName as well as a Name property. (In the properties window the CodeName is called (Name) - in brackets). In place of Worksheets("Portfolio") you can refer to the sheet simply as Folio. This is the name I have given it in the attached workbook.

By replacing Select with Activate you could get rid of one of the two mistakes. The other needs strict control of which sheet you activate here and there. This effort is error prone and cumbersome. Why not do like VBA intended? Never activate any sheet. Just refer to them by name and do with them whatever you like.

If that's the bad news, which is the good? I think you can delete the offending line without facing punishment or replace the Select with Activate. If this doesn't work then the most likely reason is tha the sheet is hidden. Add Debug.Print Worksheets(SheetName).Visible before the Select statement to test the status. If the sheet is hidden although it shouldn't be then the reason is the faulty code in your procedure HideAllSheets. This I have marked in your code and suggested an alternative.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login