30 second delay

[Rubber Beaked Woodpecker]

Hi. The following code works very well but I would like a slight modification. When S1 = 1 I would like a 30 second delay before the code is executed. I've tried in several way using Application.Wait but cannot get this to work.

Any advice please

Thanks
RBW

Option Explicit

Dim currentMarket As String
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").Calculate
 If Target.Columns.Count = 16 Then
    Application.EnableEvents = False
    Range("Q1").Value = WorksheetFunction.CountIf(Range("H5:H50"), ">0")
    Range("Q2") = Range("W2")
     Range("U1") = Range("U2")
      Range("T1") = Range("T2")
       Range("AA1") = Range("Z1")
                      If Range("S1") = 1 Then

                '30 second delay    

          Call ThisSheetlogBalance
                  
          Call Completed
         
         
                           ElseIf Range("X2").Value = "Yes" Then
             Call R1
                                   
        End If
   End If
  
   Application.EnableEvents = True
End Sub

 

[Micron]

I use this for Access but I think it also works in Excel:

Sub pause(sngSecs As Single)
Dim endTime As Long

endTime = Timer
Do Until Timer > endTime + sngSecs
Loop

End Sub

You'd call it as

Range("AA1") = Range("Z1")
If Range("S1") = 1 Then
   Pause 30
   Call ThisSheetlogBalance

 

[Rubber Beaked Woodpecker]

Thank you

 

[Micron]

Thanks for the acknowledgement. Forgot to say that by using single as the data type it allows you to use fractional seconds, such as 5.5
Glad I could help.