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

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?

CodePudding user response：

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

CodePudding user response：

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