1

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
Pil John
  • 11
  • 5
  • Is this wat you are looking for?: https://stackoverflow.com/questions/18602979/how-to-give-a-time-delay-of-less-than-one-second-in-excel-vba/18603933 – Luuk Feb 11 '21 at 14:06
  • maybe this answers your question: https://www.reddit.com/r/excel/comments/4sc3ig/allow_user_interaction_with_your_spreadsheet_mid/ – MGP Feb 11 '21 at 14:10

2 Answers2

1

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
Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • Hey. So `MacroPart2` is the one that'll reset the color of the cell? – Pil John Feb 11 '21 at 14:54
  • Yeah, So you would have the first macro the same as before, then you have the pause using OnTime which would call the second sub which does all the code you wanted to happen after the pause. Reset the color or whatever else you need. If you need the second sub to share variables with the first, declare them as module level variables. – Toddleson Feb 11 '21 at 15:22
0

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

Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20