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

Question

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

Please include the VBA for your timer here in your question, otherwise we won't be able to help much. — TylerH
– TylerH, Commented Feb 8, 2019 at 17:33
Hard to tell without seeing any of your code. I'm guessing your timer involves some kind of loop and DoEvents? — Mathieu Guindon
– Mathieu Guindon, Commented Feb 8, 2019 at 17:36
Please edit your post to include the "timer loop" in question. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 8, 2019 at 17:38
All we can say without seeing your code, is that VBA code is running on the UI/main thread, and for as long as that thread is busy running your loop, it's not doing anything else. You need to get into an asynchronous paradigm, so that the UI thread isn't monopolized. Having DoEvents in the loop body is not a good solution; look into Application.OnTime — Mathieu Guindon
– Mathieu Guindon, Commented Feb 8, 2019 at 17:40
Depends how the form is displayed. Please include your code. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 8, 2019 at 17:56

Mathieu Guindon · Accepted Answer · 2019-02-08 19:43:37Z

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.

Hello, wonderful detailed answer, many thanks for your patience with me and retracting the down votes. I will have access to my pc again on Wednesday so will next get a chance to play with what you have said, will reply and or accept the answer. Thanks again

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related