Error in WorkBook_Open code: Activate method of Worksheet class failed

[Gaurangg]

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
------------------------------------------------------

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

 

[Joe4]

If you hit the "debug" option on the error message that pops up, what line of code does it highlight?

 

[Tim_Excel_]

Please don't use Activate, nor Select. A better method:

'....
ThisWorkbook.Sheets("START").Range("F9").ClearContents
End Sub

 

[Joe4]

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]

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

 

[Joe4]

Did you try what I suggested in my last post, and just change that line to:

[COLOR=#333333]Sheets.("Start").Activate[/COLOR]

 

[Gaurangg]

Yes, and received the same worksheet class failed error.

 

[Joe4]

Can you confirm that the "START" sheet is indeed, visible, at that point?

 

[Gaurangg]

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.

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