How to allow user interaction with spreadsheet when UserForm or MsgBox is displayed in Excel

Question

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?

@braX That still executes the next code while userform is still shown — West, Feb 23 '22 at 03:37
Then you will have to code for something it can check to pause it. Something in the form maybe? In other words, it's not a simple thing you are missing. — braX, Feb 23 '22 at 03:38
@braX by pausing you mean maybe some `while` loop that goes forever till some condition is satisfied? Will this have performance hit on computer? — West, Feb 23 '22 at 03:43
Something like that, yes, maybe using a global variable the loop checks to exit. Put `DoEvents` in the loop to help with performance issues. It also may be worthwhile to re-evaluate why you need something like this. — braX, Feb 23 '22 at 03:45
@braX I was just hoping there is some simple built-in way to avoid moving/changing code around but you right I think I'll have to re-evaluate and do that instead of loops. Thanks — West, Feb 23 '22 at 03:55
"I need the messagebox to only show when the userform is exited" - the [`QueryClose`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/queryclose-event) event? — BigBen, Feb 23 '22 at 03:57
May be use the `Application.Inputbox(Type:=8)` to ask user to select a cell/range. That way the code is not executed until user selects a range. Not an exact solution but I think it might work. — Charlie, Feb 23 '22 at 04:03
@BigBen The actual code is a loop Run bunch of code > Msgbox > Run bunch of code again. Since Msgbox was blocking ui, I was hoping to substitute it with subform. I could have the second bunch of code in the `QueryClose` event but then the loop will just carry on — West, Feb 23 '22 at 04:11
@Charlie Thats pretty close. Its almost what I need as it stops further execution while allowing interaction, a bummer that its only possible with the input box — West, Feb 23 '22 at 04:18
Please see this post which treats the same subject the other way round :otherhttps://stackoverflow.com/questions/1783906/why-does-showing-a-userform-as-modal-stop-code-execution — , Feb 23 '22 at 04:26
You'd need to split your code into two parts - first part ends by displaying the non-modal userform: second part is triggered when the user OK's or Cancels out of the userform after providing input. You may have to store some state in between those two sections (e.g. in Global/Static variables, etc) — Tim Williams, Feb 23 '22 at 04:44

West · Accepted Answer · 2022-02-23T05:42:24.813

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")

Charlie · Answer 2 · 2022-02-23T04:53:07.510

Not an exact solution, but you can replace the Msgbox with an Inputbox and sort of stop code from further execution until an input is received from user. The below code deletes the sheet, of which the user has selected a cell.

Sub AlowDeletion()

    On Error Resume Next
      Set rng = Application.InputBox( _
      Title:="Range Selector", _
      Prompt:="Select any cell in the sheet you want to delete", _
      Type:=8)

      Application.DisplayAlerts = False
      ActiveWorkbook.Sheets(rng.Parent.Name).Delete
      Application.DisplayAlerts = True
    On Error GoTo 0

End Sub

How to allow user interaction with spreadsheet when UserForm or MsgBox is displayed in Excel

2 Answers2