I have a message box that asks for user input, Yes or No, but need to give the user a chance to cross check another tab if their guess is correct, e.g.
answer = MsgBox("Confirm Deletion?", vbYesNo + vbQuestion)
If answer = vbNo Then
Exit Sub
End If
The messagebox blocks everything and doesnt allow the user to change tabs or click anything else. I also tried creating a UserForm instead and setting its ShowModal to False. This allows the user to interact with the spreadsheet while the form is displayed, but then it also allows code execution to continue while it is still displayed/without waiting for an answer, e.g in code below the message box is shown immediately after the UserForm is shown.
UserForm.Show
MsgBox("Step 2")
I need the messagebox to only show when the userform is exited. How can I achieve this?
Finally found a solution. Once the Userform is displayed you can enable window interaction again using the windows api. The form's ShowModal property should initially be True, which is the default anyways.
Then in the Userform code window you include this code at the top that gets triggered when the form shows.
Private Declare PtrSafe Function EnableWindow Lib "user32.dll" (ByVal Hwnd As Long, ByVal fEnable As Long) As Long
Private Sub UserForm_Activate()
EnableWindow Application.Hwnd, 1
End Sub
On running the code below, this allows the user to still click and move around the spreadsheet while the form is shown, but wont allow any editing of cells, which is perfectly ideal. Code execution only continues to the message box after the form is closed
UserForm.Show
MsgBox("Step 2")