CODEWITHSUNDEEP
excelvba
user1088793
user1088793

Reputation: 653

How do you open a workbook whilst a macro is running in another workbook?

I am experiencing an issue where I created a VBA timer that counts down from a specified time to zero. This macro runs for a while, as such, when I try to open another workbook nothing happens, it is like the macro blocks the other workbook from opening?

My timer sub

Private Sub Timer15_main(play As Boolean)
 Dim UserInput As String

    If play Then
      UserInput = TextBox1.Value 'this is what the user inputs and how long the timer should run
     Else
      timer_15_pause_button = False
      UserInput = "00:15:00" 'this is what the user inputs and how long the timer should run
    End If

        'validate userinput und ensure hh:mm:ss format
        Select Case Len(UserInput) - Len(Replace$(UserInput, ":", ""))
            Case 2 'input format is hh:mm:ss

            Case 1 'input format is mm:ss
                UserInput = "00:" & UserInput
            Case 0 'input format is ss
                UserInput = "00:00:" & UserInput
            Case Else
                MsgBox "invalid input"
                Exit Sub
        End Select

        'we need to convert the string UserInput into a double and
        'convert it into seconds (Timer uses seconds!)
        Dim SecondsToRun As Long
        SecondsToRun = CDbl(TimeValue(UserInput)) * 24 * 60 * 60
         TextBox4.Value = Format$((SecondsToRun / 24 / 60 / 60) + Time(), "hh:mm:ss")
        Dim TimerStart As Double
        TimerStart = Timer 'remember when timer starts

        Do

             If SecondsToRun - (Timer - TimerStart) < 10 Then
             TextBox1.BackColor = RGB(255, 0, 0)
            End If

            TextBox1.Value = Format$((SecondsToRun - (Timer - TimerStart)) / 24 / 60 / 60, "hh:mm:ss")
            'count backwards from 01:15 format as hh:mm:ss

            DoEvents
            If timer_15_pause_button = True Then
            Exit Sub
            End If


        Loop While TimerStart + SecondsToRun > Timer 'run until SecondsToRun are over
        TextBox1.BackColor = RGB(255, 255, 255)

        'TextBox4.Value = ""

        End Sub

Upvotes: 0

Views: 539

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Assuming the form is displayed like this:

UserForm1.Show
DoSomething

Then the form is modal, which means the DoSomething call will not run until the form is closed. While a modal form is displayed, it controls the message loop, and the host application is unavailable all the while: the only thing the user can interact with, is the form.

If the form is displayed like this:

UserForm1.Show vbModeless
DoSomething

Then the form is displayed, and the DoSomething call runs immediately; user can still interact with the host application, and code in the form can run asynchronously.

But a loop like this:

Do
    ' do stuff
    DoEvents
Loop While {condition}

Is bad design: without the DoEvents, the loop would be hijacking the message loop completely, modal or modeless wouldn't make a difference, and the host application would likely go "(not responding)" until the loop finishes. With the DoEvents, that's a busy-loop constantly telling Windows "hey you got anything to run? go ahead then!" - what you want to do, is register a procedure that will be invoked once per second to update the timer label on the form.

That procedure needs to be in a separate, standard module - ideally the same module that's showing the form, and ideally working off the same instance of that form.

Option Explicit
Private theForm As UserForm1

Public Sub ShowTheForm()
    If theForm Is Nothing Then Set theForm = New UserForm1
    theForm.Show vbModeless
End Sub

Public Sub OnTimerTick()
    If theForm Is Nothing Then Exit Sub
    theForm.HandleTimerTick
End Sub

Now the form needs to expose a HandleTimerTick procedure, and schedule the OnTimerTick macro. So you might have a CommandButton control that, on click, begins the scheduling loop:

Dim TimerStart As Double

Private Sub CommandButton1_Click()
    ' validate inputs...
    TimerStart = Timer
    Application.OnTime Now + TimeValue("00:00:01"), "OnTimerTick"
End Sub

Public Sub HandleTimerTick()
    'timer has ticked, we're at least 1 second later.
    Dim secondsElapsed As Double
    secondsElapsed = Timer - TimerStart

    'update the textbox accordingly...
    TextBox1.Text = Format$(secondsToRun - secondsElapsed, "hh:mm:ss")

    'now determine if we need to schedule another tick:
    If Int(secondsToRun - secondsElapsed) > 0 Then
        Application.OnTime Now + TimeValue("00:00:01"), "OnTimerTick"
    End If
End Sub

Notice there's no explicit loop anymore, no DoEvents: just a scheduled macro that tells the form "hey there, tick!" and the form responds by updating itself and, if needed, re-scheduling another tick.

Upvotes: 2

Related Questions