Allow User To Edit Worksheet At Certain Point During Macro

[Draperies]

I have a macro that I would like to let a user edit the worksheet and make changes at a certain point. I have tried creating a modeless userform, but the userform only pops up and the code continues running without waiting for input (or running any of the code in the userform). Does anyone know of either another way I can accomplish this or what I would be doing wrong with the userform?

 

[Misca]

Try ending the first sub with the modeless userform open and when they click the button on the userform another macro starts from there.

If you want the "Edit mode" to happen only on certain condition you can force the macro to end with something like:

Sub TheFirstMacro()

Dim Ask As Integer

Ask = MsgBox("Yes or No?", vbYesNo)
    'Yes=6, No=7

If Ask = 7 Then
    MsgBox "You chose No." & vbNewLine & "Macro ends here."
    Exit Sub
End If

Call TheOtherMacro

End Sub

Sub TheOtherMacro()

MsgBox "You chose Yes so this is the other macro."

End Sub

 

[Draperies]

Thank you for the response! Unfortunately when I set the userform to vbModeless the userform only pops up onto the screen, but the code immediately continues to execute and does not allow me to select any buttons.

I did incorporate the message boxes, and that does work great! I just can't get the code to wait once it shows the userform...

 

[Misca]

Try replacing the MsgBox with your modeless userform followed by Exit Sub: That should do the same trick.

 

[Jaafar Tribak]

Try this and see if it works for you :

Sub Test()

    UserForm1.Show vbModeless
    
    Do
        DoEvents
    Loop While VBA.UserForms.Count > 0
    
    MsgBox "Your code is now resuming."
    
    'rest of your code goes here...

End Sub

You should be able to edit the worksheet as well as run any code in the userform.

 

[Draperies]

Wow! That works absolutely perfectly! Thank you both so much!

 

[Miceal]

Jaafar, I had the same problem and looked for hours to find a solution. Your solution was so simple and perfect for my situation.... Nice work!

 

[greegan]

Same here, pausing and continuing the same code really worked for what I'm doing here. Thanks for the assist.

-- g