Error in WorkBook_Open code: Activate method of Worksheet class failed

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
125
Hi Falks,

I have written a code in workbook module in a file with Workbook_Open activity and I am receiving the below error while I am opening the file from sent email or sometimes from main file.

Can anyone help me to resolve the below error

------------------------------------------------------
Run-time error '1004':

Activate method of worksheet class failed
------------------------------------------------------

Code:
Private Sub Workbook_Open()
'Runs after Workbook is opened to unhide data sheets


    'Step 1: Ensure that START sheet is visible
    Sheets("START").Visible = xlSheetVisible
    
    'Step 2: Hide all other workSheets
    Sheets("Form_Input").Visible = xlVeryHidden
    Sheets("Form").Visible = xlVeryHidden
    Sheets("Collation").Visible = xlVeryHidden
    
    
    ThisWorkbook.Sheets("START").Activate
    Range("F9").Select
    Range("F9").ClearContents
    
End Sub
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
If you hit the "debug" option on the error message that pops up, what line of code does it highlight?
 

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Please don't use Activate, nor Select. A better method:

Code:
'....
ThisWorkbook.Sheets("START").Range("F9").ClearContents
End Sub
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
The issue may also be with "ThisWorkbook". You may want to try "ActiveWorkbook" instead.
Or better yet, leave it off altogether. I don't think it is necessary. By default, it will run against whatever the active workbook is at the time it hits that line of code.
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
125

ADVERTISEMENT

Hi Joe4,

After hitting the "debug' button, the below line gets highlighted-

ThisWorkbook.Sheets.("Start").Activate

Also I have checked with ActiveWorkbook event and got the error as below

Run-time error '91':

Object variable or With block variable not set
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Did you try what I suggested in my last post, and just change that line to:
Code:
[COLOR=#333333]Sheets.("Start").Activate[/COLOR]
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
125

ADVERTISEMENT

Yes, and received the same worksheet class failed error.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Can you confirm that the "START" sheet is indeed, visible, at that point?
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
125
Yes. I have renamed the sheet with "Main" and it is visible although.

Currently I am using below code which is working "ok" but sometimes when It got crashed when I close the file after updating any data into the input sheet.

All the excels got closed after not responding for sometime. and asks that "Close the program". Can anyone help me where I am doing mistake in my code.

Code:
Private Sub Workbook_Open()
'Runs after Workbook is opened to unhide data sheets


    Dim WB As Workbook
    Set WB = ThisWorkbook
    'WB.Activate
    'Step 1: Ensure that START sheet is visible
    Sheets("MAIN").Visible = xlSheetVisible
    
    'Step 2: Hide all other workSheets
    Sheets("Audit Form_Input").Visible = xlVeryHidden
    Sheets("Audit Form").Visible = xlVeryHidden
    Sheets("Audit Collation").Visible = xlVeryHidden
    
    
    Sheets("MAIN").Visible = xlSheetVisible
    
    'Worksheets("MAIN").Activate
    ThisWorkbook.Sheets("MAIN").Range("F9").ClearContents
    'Sheets("START").Select
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,923
Messages
5,525,653
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top