VBA Events: load workbook before running code using workbook_open

Question

I want to run a VBA macro AFTER the workbook has finished opening. I tried to use workbook_open but this runs before the workbook has finished opening. This doesn't work for me as I need to loop through each sheet like so...

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
'do stuff on each sheet
Next ws

End Sub

Does anyone know if there is an event which runs once the workbook has finished opening? Or have any other suggestions on how I can achieve this?

Just a quick note: whatever is the answer you pick below, the code **cannot** be put inside a module, it must be at `This_Workbook` into VBA project window. (or maybe inside a worksheet object?) — Paulo Bueno, Aug 03 '20 at 13:17

Robert · Answer 1 · 2014-08-08T14:24:24.303

7

I had a similar problem that I solved using Application.OnTime.

From the linked MSDN Library Article:

Schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).

You could try using this method to provide the workbook with enough time to open before running the DoStuff procedure:

Private Sub Workbook_Open()

    Application.OnTime Now + TimeValue("00:00:01"), "DoStuff"

End Sub

Ensure the DoStuff procedure is not in a worksheet module:

Private Sub DoStuff()
    'Implementation...
End Sub

The time can be adjusted but one second was satisfactory for me.

edited Aug 08 '14 at 14:24

answered Jul 30 '14 at 15:16

Robert

531
2
7
20

For some reason this is failing and telling me Macro dos not exist! – Snake Aug 08 '14 at 02:55
4

The "DoStuff" Sub or Function must not be in a worksheet module! – Robert Aug 08 '14 at 14:22
Yes, the calling function can be. – Robert Aug 09 '14 at 17:32
@Robert ; A life saving answer :) – AddyProg Dec 12 '14 at 11:20

score 6 · Answer 2 · answered Oct 18 '10 at 13:31

6

Put your code in the Workbook_Activate event. It happens after the Open event.

Private Sub Workbook_Activate()
    ' Code goes here
End Sub

answered Oct 18 '10 at 13:31

Michael

1,646
2
16
21

Thanks for your reply, for some reason this doesn't do anything at all. Once the workbook has loaded it doesn't initiate this? Could it be something to do with the security settings? – samcooper11 Oct 19 '10 at 06:29
Try ThisWorkbook_Activate rather than Workbook_Activate – Jazza Oct 20 '10 at 16:04
It could be a security issue. I just tested code in that event and it seemed to run fine on my machine. – Michael Oct 22 '10 at 13:14
Seems to be working. I have a downloadable spreadsheet and initially I wrote some code on the Open event. I have some code that tries to retrieve the lastRow of a sheet, but it told that it was EMPTY, I assumed the sheet was not 100% loaded when it ran the method call. When using Activiate, it waited for the entire sheet to be loaded before running. Thanks for the tip. – javapadawan Dec 24 '14 at 23:22

score 1 · Answer 3 · answered Oct 18 '10 at 10:57

1

Try using ThisWorkbook rather than ActiveWorkbook:

Private Sub Workbook_Open()
    Dim osht As Worksheet
    For Each osht In ThisWorkbook.Worksheets
        Debug.Print osht.Name
    Next osht
End Sub

answered Oct 18 '10 at 10:57

Charles Williams

23,121
5
38
38

VBA Events: load workbook before running code using workbook_open

3 Answers3

Linked