VBA Events: load workbook before running code using workbook_open

Question

Hi Everyone,

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?

Answer 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

Answer 2

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

Private Sub Workbook_Activate()
    ' Code goes here
End Sub