CODEWITHSUNDEEP
excelvba
Pil John
Pil John

Reputation: 11

Wait some time without pausing the worksheet

I am working on a game that paint a random cell and the user has to click on that specific cell before it get painted for its default color (white). I want to let the user wait for 1.5 seconds without resetting the color of the cell. However, I can't find a way to implement it, because when I use Application.Wait it just pause the whole worksheet without letting me click on the cell.

How can I wait 2 seconds without pausing the worksheet?

Sub Game()
    For i = 1 To 5 ' 5 Rounds '
        PaintRandomCell
        ' todo: Pause app for 2 seconds '
        UnPaintCell
    Next i
End Sub

Upvotes: 1

Views: 212

Answers (2)

Toddleson
Toddleson

Reputation: 4457

I think you're looking for the OnTime method. It is able to delay the execution of a macro by an amount of time and will resume the rest of the sheet for user editing until that specified time occurs.

Split your macro into two subs, with the stuff that should happen after the pause being in the second sub.

Application.OnTime Now + 1.5/86400, "MacroPart2"

If you're having a hard time with the "Cannot run the macro" error. Try adding the module/sheet name to the macro reference (eg. Sheet1.MacroPart2 or ThisWorkbook.MacroPart2). It doesn't have to be a public procedure for OnTime to call it.

Edit: An example on how to apply it as a Loop.

Dim i As Integer
Sub Start()
    If i < 5 Then
        Call MacroPart1
    End If
End Sub

Private Sub MacroPart1()
    PaintRandomCell
    Application.OnTime Now + 1.5 / 86400, "sheet2.MacroPart2"
End Sub

Private Sub MacroPart2()
    UnPaintCell
    i = i + 1
    Call Start
End Sub

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

In agreement eith Toddleson, you could have something like

In a normal module

Public arrPaint(4) As Excel.Range
Public intCounter As Integer

Public Function StartUp()

Set arrPaint(0) = Range("a1")
Set arrPaint(1) = Range("b2")
Set arrPaint(2) = Range("c3")
Set arrPaint(3) = Range("d4")
Set arrPaint(4) = Range("e5")

intCounter = 0

Application.OnTime Now + TimeValue("00:00:1"), "Tick"

End Function

Public Function Tick()

Dim tmNextRun As Date

tmNextRun = Now + TimeValue("00:00:01")

    If intCounter < 5 Then
        If intCounter > 0 Then arrPaint(intCounter - 1).Interior.Color = xlNone
        arrPaint(intCounter).Interior.Color = vbRed
        intCounter = intCounter + 1
        Application.OnTime tmNextRun, "Tick"
    Else
        arrPaint(intCounter - 1).Interior.Color = xlNone
    End If
    
End Function

and then deal with the user hits in the worksheet events, like so.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If intCounter < 5 Then
        If Not Intersect(Target, arrPaint(IIf(intCounter > 0, intCounter - 1, 0))) Is Nothing Then
            Range("q1").Value = "HIT"
        Else
            Range("q1").Value = Target.Address
        End If
    End If
End Sub

A button calls start up and populates the cell ranges. This can still be random, i did this for ease.

Hope this helps as a building block for you.

Thanks

Upvotes: 0

Related Questions