CODEWITHSUNDEEP
vbaexcel
anonymous noob
anonymous noob

Reputation: 145

Use Application.Wait immediately after a workbook opens

I need my workbook to display a certain color after I open it. Then after 5 seconds I would like the color to change. So I implemented to following code:

Private Sub Workbook_Open()
    Application.ActiveSheet.Cells.Interior.ColorIndex = 4    
    Application.Wait (Now + TimeValue("0:00:05"))
    Application.ActiveSheet.Cells.Interior.ColorIndex = 5
End Sub

However when I click on my file, it loads for 5 seconds and then opens and displays only the second color. How would I fix this while still using application.wait.

Upvotes: 0

Views: 3028

Answers (2)

Porcupine911
Porcupine911

Reputation: 928

To directly answer the posed question, here's a workaround without using OnTime if you really, really had to...

It is possible to fire an event immediately after Workbook_Open. You could force a Worksheet_Activate event by manipulating the active sheet in the Workbook_Open sub. Then you could use your original Application.Wait code inside of Worksheet_Activate.

Private Sub Workbook_Open()
    Application.ActiveSheet.Cells.Interior.ColorIndex = 4
    Sheets(2).Activate
    Sheets(1).Activate
End Sub

Private Sub Worksheet_Activate()
    Application.Wait (Now + TimeValue("0:00:05"))
    Application.ActiveSheet.Cells.Interior.ColorIndex = 5
End Sub

Upvotes: 0

Jonathan
Jonathan

Reputation: 663

I tested this and it worked.

Use this in the Workbook_Open 

Private Sub Workbook_Open()
    '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).
    Application.OnTime Now + TimeValue("00:00:01"), "DoThis"
End Sub

Put this in a module. Not Thiswork or it won't work

Private Sub DoThis()
    Application.ActiveSheet.Cells.Interior.ColorIndex = 4
    Application.Wait (Now + TimeValue("0:00:05"))
    Application.ActiveSheet.Cells.Interior.ColorIndex = 5
End Sub

Upvotes: 3

Related Questions