CODEWITHSUNDEEP
excelvbauserformmsgbox
West
West

Reputation: 2570

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

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?

Upvotes: 0

Views: 1729

Answers (4)

Nazirakhon Kholturaeva
Nazirakhon Kholturaeva

Reputation: 1

Try the following

do while range("A1")="" do events loop

This way your code will stop and provide as much time as user need and once user finishes editing, user must enter any value in given range(in our case A1) and automatically A1 would not be empty and the code exits the loop and continues the rest code. Hope I was clear and that it will help you

Upvotes: 0

Mike Brown
Mike Brown

Reputation: 1

I have had good results with userforms. The trick is to place the userform.show at the end of a subroutine. The code continues to run after the .show but only to the end of the subroutine. Then it suspends with the userform enabled and global and class variable values still intact. The userform button’s code calls another subroutine that restarts the code. Set the userform to enabled and showmodal to false.

Upvotes: 0

West
West

Reputation: 2570

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

Upvotes: 2

Charlie
Charlie

Reputation: 175

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

Upvotes: 0

Related Questions