VBA getting error Active method of Worksheet class failed

Question

I tried to look for answers but am not finding anything that has worked so far. I have some code that works for some people and doesn't work for others (using same version of Excel) when running this code:

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Sheets("Discount").Activate
ActiveSheet.Unprotect Password:="01"    
ActiveSheet.Range("G14:O15,O18:O19,D29:I29,D31:I31,D33:I33,D35:I35,D37:I37").ClearContents
ActiveSheet.Shapes("Option Button 31").ControlFormat.Value = xlOn
OptionButton31_Click
Application.ScreenUpdating = True

End Sub

The error shows up at Sheets.("Discount").Activate

the spelling of the worksheet is correct. I also tried

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="01"
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    ThisWorkbook.Sheets("Discount").Activate
    ActiveSheet.Unprotect Password:="01"    
ActiveSheet.Range("G14:O15,O18:O19,D29:I29,D31:I31,D33:I33,D35:I35,D37:I37").ClearContents
ActiveSheet.Shapes("Option Button 31").ControlFormat.Value = xlOn
OptionButton31_Click 
ActiveWorkbook.Protect Password:="01"
Application.ScreenUpdating = True

And still getting the error. I am having a hard time figuring it out because it works for me every time, but doesn't for other people.

Can you try leaving ScreenUpdating on to see if anything strange is happening while you step through the code on the affected users' machines? — Spangen, Dec 19 '17 at 16:56

Vityata · Answer 1 · 2017-12-19T17:14:47.777

3

Solution 1:

Instead of Sheets.("Discount").Activate write Sheets("Discount").Activate and it should work. E.g., remove the dot.

Solution 2:

If this does not work, try to make sure that this sheet is visible. E.g. write before the line with the error the following:

Sheets("Discount").Visible = True

In general, in VBA try to avoid ActiveSheet, ActiveWorkbook, ActiveCell - How to avoid using Select in Excel VBA

As noted by @Mat's Mug, consider using Worksheets("Discount").Visible, when you refer to Worksheets, because the Sheets collection contains Charts as well.

edited Dec 19 '17 at 17:14

answered Dec 19 '17 at 17:00

Vityata

42,633
8
55
100

2

Also you should use the `Worksheets` collection if you're expecting to work on a `Worksheet`. The `Sheets` collection also contains charts. – Mathieu Guindon Dec 19 '17 at 17:13
@Mat'sMug - that's true. – Vityata Dec 19 '17 at 17:15
For solution 1 the dot was not there, it was s mistype in comments but code was clean, so unfortunately that did not help. for Solution 2, that worksheet is always visible, because it works like a user form. I need it to be an active worksheet when the file is opened by a user. I may have just thought of a solution, maybe I will write the activate and rest of this as separate macro and just refer to that macro on open. – Irina Dec 19 '17 at 23:18

score 0 · Answer 2 · answered Dec 19 '17 at 18:15

Try using:

Sheets("Discount").Visible Sheets("Discount").Select

If this doesn't work, let me know and I'll see if there's anything else I can recommend. If you make a note of any error messages, this may help. Also, try running it with screenupdating not turned off as the person above suggested - then you will see if there's a specific action that's making it fall over.

VBA getting error Active method of Worksheet class failed

2 Answers2