Pause macro for user to do something in sheet

[gifariz]

I have a quite big macro, but some procedures need to be done manually by user because of API limitation of other program.
I wonder whether it is possible to pause macro for user to do something in sheet then continue macro while maintaining previous variables?
If I split the macro by subs for pausing, I would lose previous variables. Apparently MsgBox or Application.Wait doesn't let user to use the excel.
Thank you.

 

[aaewalsh]

Here's an example of using the "Application.OnTime" method to pause a macro and allow the user to interact with the spreadsheet before continuing:

Sub ExampleMacro()
    ' Code to be run before the pause
    ' ...
    
    ' Schedule the CheckForCompletion subroutine to run in 5 minutes
    Application.OnTime Now + TimeValue("00:05:00"), "CheckForCompletion"
    
    ' Pause the macro until the CheckForCompletion subroutine runs
    DoEvents
End Sub

Sub CheckForCompletion()
    ' Check if the user has completed the necessary actions
    If Range("A1").Value = "Completed" Then
        ' Code to be run after the user completes the necessary actions
        ' ...
    Else
        ' Schedule the CheckForCompletion subroutine to run again in 5 minutes
        Application.OnTime Now + TimeValue("00:05:00"), "CheckForCompletion"
    End If
End Sub

This example defines a macro called "ExampleMacro" that does some initial processing and then pauses for 5 minutes using the "Application.OnTime" method. The method schedules a subroutine called "CheckForCompletion" to run in 5 minutes. The macro then enters a loop using the "DoEvents" method, which allows Excel to continue processing events (such as user input) while the macro is paused. When the "CheckForCompletion" subroutine runs, it checks the value of cell A1 to see if the user has completed the necessary actions. If the value is "Completed", the macro continues with the next step. If the value is not "Completed", the "CheckForCompletion" subroutine is scheduled to run again in 5 minutes.

You can also use "Application.InputBox" method, here is an example of how it can be used:

Sub ExampleMacro()
    ' Code to be run before the pause
    ' ...
    result = Application.InputBox("Please enter some data", "Data Entry", Type:=1)
    If result = "Cancel" Then
        Exit Sub
    Else
        ' Code to be run after the user enters data
        ' ...
    End If
End Sub

In this example, the InputBox method prompts the user to enter some data and the entered data is stored in "result" variable. If the user clicks "Cancel" the macro exits otherwise it continues with the next step.

And also you can use "Application.GetOpenFilename" method, here is an example of how it can be used:

Sub ExampleMacro()
    ' Code to be run before the pause
    ' ...
    filepath = Application.GetOpenFilename("Text Files (*.txt),*.txt")
    If filepath = "False" Then
        Exit Sub
    Else
        ' Code to be run after the user selects file
        ' ...
    End If
End Sub

In this example, the GetOpenFilename method opens a file dialog box and allows the user to select a file. The selected file path is stored in the "filepath" variable. If the user clicks "Cancel" the macro exits otherwise it continues with the next step.

Please note that, you need to adjust the time and cell range according to your requirement.

 

[gifariz]

Here's an example of using the "Application.OnTime" method to pause a macro and allow the user to interact with the spreadsheet before continuing:

Sub ExampleMacro()
    ' Code to be run before the pause
    ' ...
  
    ' Schedule the CheckForCompletion subroutine to run in 5 minutes
    Application.OnTime Now + TimeValue("00:05:00"), "CheckForCompletion"
  
    ' Pause the macro until the CheckForCompletion subroutine runs
    DoEvents
End Sub

Sub CheckForCompletion()
    ' Check if the user has completed the necessary actions
    If Range("A1").Value = "Completed" Then
        ' Code to be run after the user completes the necessary actions
        ' ...
    Else
        ' Schedule the CheckForCompletion subroutine to run again in 5 minutes
        Application.OnTime Now + TimeValue("00:05:00"), "CheckForCompletion"
    End If
End Sub

This example defines a macro called "ExampleMacro" that does some initial processing and then pauses for 5 minutes using the "Application.OnTime" method. The method schedules a subroutine called "CheckForCompletion" to run in 5 minutes. The macro then enters a loop using the "DoEvents" method, which allows Excel to continue processing events (such as user input) while the macro is paused. When the "CheckForCompletion" subroutine runs, it checks the value of cell A1 to see if the user has completed the necessary actions. If the value is "Completed", the macro continues with the next step. If the value is not "Completed", the "CheckForCompletion" subroutine is scheduled to run again in 5 minutes.

You can also use "Application.InputBox" method, here is an example of how it can be used:

Sub ExampleMacro()
    ' Code to be run before the pause
    ' ...
    result = Application.InputBox("Please enter some data", "Data Entry", Type:=1)
    If result = "Cancel" Then
        Exit Sub
    Else
        ' Code to be run after the user enters data
        ' ...
    End If
End Sub

In this example, the InputBox method prompts the user to enter some data and the entered data is stored in "result" variable. If the user clicks "Cancel" the macro exits otherwise it continues with the next step.

And also you can use "Application.GetOpenFilename" method, here is an example of how it can be used:

Sub ExampleMacro()
    ' Code to be run before the pause
    ' ...
    filepath = Application.GetOpenFilename("Text Files (*.txt),*.txt")
    If filepath = "False" Then
        Exit Sub
    Else
        ' Code to be run after the user selects file
        ' ...
    End If
End Sub

In this example, the GetOpenFilename method opens a file dialog box and allows the user to select a file. The selected file path is stored in the "filepath" variable. If the user clicks "Cancel" the macro exits otherwise it continues with the next step.

Please note that, you need to adjust the time and cell range according to your requirement.

Sorry for my very late response. Thank you for answering, but I have tried and it doesn't solve my problem.
The Application.InputBox and Application.GetOpenFileName don't let me use excel while pausing.
And the Application.OnTime in your ExampleMacro divides the macro process into multiple Subs (first code is before OnTime in ExampleMacro Sub, and second code is inside the CheckForCompletion Sub), which causes my previous problem: variables are deleted in between Subs.
It is possible to bypass the variables into another subs, but my macro is big, so it is not easy to keep changing code structure just because of pausing work.
But thank you anyway.