Delay VBA before running

[Russk68]

Hi all
I am using this code (Courtesy of Fluff)

Private Sub Worksheet_Activate()
Range("A:A").AutoFilter 1, "<>0"
End Sub

The issue that I'm having is that when I open the sheet with this code, it runs before the sheet is calculated. The reason for that is that I have calculation set to manual and the sheet calculates when I leave another sheet.

I would just like to delay this code for about 1 sec.

Thank you!

Russ

 

[Yongle]

perhaps

Application.Wait Now + TimeValue("00:00:01")

 

[Russk68]

Hi Yongle
Thanks for posting
This code works but it's not doing what I expected. I think it's delaying the calculation as well, which is a calculation macro from the other sheet. I have to change sheets twice for the calculation to take effect. I'm going to post a new question about re-activating a sheet.
Thank you!

 

[Yongle]

You may be able to use the Worksheet_Calculate event
I think it triggers after the sheet calculates
Try putting the auto-filter code there instead to see if the problem is resolved.
May need to add a condition later to only make it do this once after sheet selected
Let me know how you get on

 

[Yongle]

this introduces a delay

Private Sub Worksheet_Activate()
    Dim a As Long    
    For a = 1 To 500
        Me.Activate
    Next a[COLOR=#006400][/COLOR]  
    Range("A:A").AutoFilter 1, "<>0"
End Sub

amended to measure the delay

Private Sub Worksheet_Activate()
    Dim a As Long, b As Double: b = Timer    
    For a = 1 To 500
        Me.Activate
    Next a
    Debug.Print Timer - b      [I][COLOR=#006400] 'see delay time in seconds in immediate window[/COLOR][/I]
    Range("A:A").AutoFilter 1, "<>0"
End Sub

 

[Russk68]

Hi Yongle,
I was having the same issue with all the options. I just decided to let the sheet calculate with a change event.
Thank you for your help!