Excel VBA: Error when running a downloaded workbook with automatic startup

Question

I am having a problem with a workbook, that runs a subroutine as soon as the workbook is opened. The problem appears only when the workbook is downloaded and ran for the very first time. I have managed to simplify the problem by just having a workbook with the following code in ThisWorkbook -> Workbook Open:

Private Sub Workbook_Open()
Sheet3.Activate
End Sub

The error displayed is "method 'activate' of object'_worksheets' failed", in my original program (not simplified and not written here as it is extensive) the error is different.

How I can reproduce the error:

upload workbook -> Download workbook -> Run workbook -> Enable content (macros) when asked -> Workbook opens and runs sheet3.activate -> I get error -> close workbook -> open workbook (no error, sheet 3 activates) -> Never get error when opening workbook -> Upload workbook -> Download and run workbook -> ... error again

It's as if the subroutine interrupts Excel while it is setting up the workbook for the first time and it is therefore not set up to activate the sheet.

I would really like to have a working workbook with startup macros (show userform, hide Excel) that I could send others without them encountering errors.

Is there any way to solve this?

edit: there is no other code in the program. Only what I wrote.

edit 2: this might point to the culprit: when I run the workbook for the first time after downloading it, the error pops up and while the error is up I can see two instances of the workbook on the windows task bar, one with [Protected view] after its name. It's as if the subroutine runs before the workbook goes form protected to normal (after clicking enable content...). If this makes any sense.

I am not sure though, DoEvent function may work for you. Sheet3.Activate is not standard practice. you should use ActiveWorkbook.Activate ActiveWorkbook.Sheets(3).Activate DoEvent — Mohan Prajapati
If I use ActiveWorkbook.Activate i get the following error: "Run-time error '91': Object variable or With block variable not set" with debugger pointing at ActiveWorkbook.Activate. Again this only happens the 1st time I open the workbook after downloading it. Afterwards it works just fine. Wrote something in the OP that might help. — Jaka Kos
I have this problem too. If file opens after downloading and allowing editing it give an error in any activeworkbook code in Workbook_Open() as it thinks the workbook is not active.. Only around it is to exit the workbook onerror and restart the it works fine. Any thoughts — user3063775

Guest Guest · Accepted Answer · 2018-07-12T19:55:13

I don't see all your code especially the code in the worksheet that you activate, but perhaps the following might help:

'*******************************************************************************************************************************
' To activate the active worksheet
' Bug in Excel: The Worksheet_Activate event does not fire for a sheet that is currently active or may generate errors
' The solution is to call it explicitely, but make sure that Worksheet_Activate() is declared as Public in the worksheet code
'*******************************************************************************************************************************
Public Sub activateActiveSheet()

On Error Resume Next
ThisWorkbook.ActiveSheet.Worksheet_Activate
Err.Clear: On Error GoTo 0

End Sub

Excel VBA: Error when running a downloaded workbook with automatic startup

1 Answers