开发者

VBA Events: load workbook before running code using workbook_open

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.Wo开发者_高级运维rksheets
'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?


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.


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

Private Sub Workbook_Activate()
    ' Code goes here
End Sub


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜