VBA exit Loop Until statement early

[leatherhen99]

Hi All! Happy Monday!

I have a unique situation that I can't seem to find a thread for (or a YouTube video ).

I have a macro that completes several pieces of coding... and based on what happens, the user might end up having to copy/paste information from another program into the Excel file for the final output to be complete...
I found that I can use the following code to create a 10-minute loop so that the user has time to copy and paste...
Is there a way that I can add to this code so that they can move forward in the macro if all 10 minutes aren't needed? The point of the macro is to save time...not to make them waste it if it's not necessary

Here's the macro that I call to within the larger macro:

Sub WasteTime(Finish As Long)
 
    Dim NowTick As Long
    Dim EndTick As Long
 
    EndTick = GetTickCount + (Finish * 1000)
     
    Do
 
        NowTick = GetTickCount
        DoEvents
 
    Loop Until NowTick >= EndTick
 
End Sub

Here's the portion of the code where I'm calling to 10 minutes to give the user time:

'Ask user if NoActivity/Future dates need to be added
    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Past, present and/or future dates highlighted will *not* be available on the Timesheet Tab." & _
    " Do you need to add 'No Activity' or future dates to the FutureWorking tab?", vbYesNo + vbQuestion + vbDefaultButton2, "No Activity Dates")
'If past/future dates need to be manually copied/pasted, give user 10 minutes
    If Answer = vbNo Then
'if no additional updates, move to Timesheetv8 macro
    Call Timesheetv8
'if yes, wait for 10 minutes while the information is inputted...
    If Answer = vbYes Then
    WasteTime (600)
        
    End Sub

Thank you!
Heather

 

[Eric W]

Create a modeless user form that says "Please paste your data, and click 'End' when you're done" and have a button on it that says "End".

 

[leatherhen99]

Hey Eric,

Oh my goodness! This is great!!! Now I need a bit more help... I can't figure out how to get the 'End' button to have functionality. I've watched some videos, and I've added the following to the commandbutton; however, how do i call to a private sub?

Private Sub CommandButton1_Click()
Unload UserForm1

End Sub

 

[leatherhen99]

For clarity - I'm trying to call to the CommandButton1_Click() that's inside the userform from within the module1 sub-routine.

 

[Eric W]

I imagine your UserForm looks something like this:

If you've gotten this far, you've added the Userform, added the caption, and added the CommandButton, and modified them with the mouse and/or Properties box. Now to add the code, just right-click on the CommandButton, select View Code, and put your code there, just as you showed in post 3. The code for the UserForm is stored in the UserForm. You can also right-click on the UserForm1 label in the navigation pane on the left, but if you click on the object, it'll add the header/trailer for you.

I assume you've figured out that you load the UserForm like this:

UserForm1.Show vbModeLess

The vbModeLess parameter lets you continue to work in Excel while the UserForm is showing.