Timer loop in one workbook disables other workbooks from being edited

[mason0567]

Hello,

Currently, I have a userform that runs a timer upon clicking 'Start' and stops once 'Stop' is pressed. Once stopped, another userform asking for additional information about the timed session will display and be recorded onto the Excel sheet. My problem is that when the timer is running, users cannot edit other workbooks that they have open. Does anybody have another way of doing this that would allow users to edit other workbooks while this loop is running? Here is the code for it:

Dim dteStart As Date, dteFinish As Date
    Dim dteStopped As Date, dteElapsed As Date
    Dim boolStopPressed As Boolean, boolResetPressed As Boolean
    
Private Sub btnReset_Click()
    dteStopped = 0
    dteStart = 0
    dteElapsed = 0
    Label1 = "0:00:00"
    boolResetPressed = True
End Sub

Private Sub btnStart_Click()
btnstart.Enabled = False
btnstop.Enabled = True
Start_timer:
    dteStart = time
    boolStopPressed = False
    boolResetPressed = False
Timer_Loop:
    DoEvents
    dteFinish = time
    dteElapsed = dteFinish - dteStart + dteStopped
    If Not boolStopPressed = True Then
        Label1 = WorksheetFunction.Text(dteElapsed, "[h]:mm:ss")
        If boolResetPressed = True Then GoTo Start_timer
        GoTo Timer_Loop
    Else
        Exit Sub
    End If

End Sub

Private Sub btnStop_Click()
    ActiveWorkbook.Save
    ManualReportUF2.Show
    boolStopPressed = True
    dteStopped = dteElapsed
    Call btnReset_Click
    btnstart.Enabled = True
    btnstop.Enabled = False
    Call UserForm_Initialize
    ActiveWorkbook.Save

End Sub

Private Sub UserForm_Initialize()
    Label1 = "0:00:00"
    btnstop.Enabled = False
End Sub

[mehmetcik]

Try opening the userform as vbmodeless

Userform1.show vbmodeless

[LJMetzger]

Hi mason0567,

It seems like you have two problems:
a. The problem that mehmetcik pointed out. When you open the UserForm as Modal, by definition the rest of the Excel world is locked out in this Instance of Excel until the UserForm Closes. You could access another workbook, if it were opened in a separate instance of Excel.

b. Your endless loop is a resource hog. If you add a delay in the loop, other workbooks should be able to be accessed. I was only able to access other workbooks using View > Switch Windows. See the changes in red below that added a delay:

Option Explicit

#If VBA7 And Win64 Then
    ' 64 bit Excel
    'The following line is supposed to be RED in 32 bit Excel
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    ' 32 bit Excel
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

    Dim dteStart As Date, dteFinish As Date
        Dim dteStopped As Date, dteElapsed As Date
        Dim boolStopPressed As Boolean, boolResetPressed As Boolean
        
    Private Sub btnStart_Click()
    btnStart.Enabled = False
    btnStop.Enabled = True
Start_timer:
        dteStart = Time
        boolStopPressed = False
        boolResetPressed = False
Timer_Loop:
        DoEvents
        dteFinish = Time
        dteElapsed = dteFinish - dteStart + dteStopped
        If Not boolStopPressed = True Then
            Label1 = WorksheetFunction.Text(dteElapsed, "[h]:mm:ss")
            Sleep 250   'Delay for 250 milliseconds
            If boolResetPressed = True Then GoTo Start_timer
            GoTo Timer_Loop
        Else
            Exit Sub
        End If

    End Sub

The delay is for 1/4 of a second, even though your granularity is one second. The reason is to allow the application to seem responsive when you click a CommandButton.

Lewis

[mason0567]

Awesome, thanks for the advice. It works perfectly!