CODEWITHSUNDEEP
excelvba
Jake C
Jake C

Reputation: 49

Save Using a UserForm, Error: can't show modally

I am trying to prevent users from saving without using the naming conventions established in a Userform. Below is the workbook code:

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
UserForm1.Show
If UserForm1.Visible = True Then
    Cancel = False
    Exit Sub
End If
Cancel = True
MsgBox ("Please use the save form.")
End Sub

I then have a button on the userform that should save the file containing the code:

Sub SaveButton_Click()
Dim FileName As String
    FileName = FileNameTextBox.Value
    ActiveWorkbook.SaveAs FileName:=FileName
End Sub

Unfortunately this gives me a

400 error: "Can't show modally".

I am not sure what this means or how to resolve it. Please let me know if there is a better way to do this.

Upvotes: 0

Views: 345

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

UserForm1.Show is showing the form modally - that means execution will only resume with the next instruction after the form has closed, and this implies If UserForm1.Visible = True will always be False... if you're lucky.

If the user closes the modal form with the red [x] button, then the object gets destroyed, and If UserForm1 re-spawns a new one, and that instance will not be visible either (because it was never shown in the first place).

You could try showing the form modeless:

UserForm1.Show vbModeless

Now execution will resume in this scope immediately after showing the form... making the condition UserForm1.Visible always True.

Remove UserForm1.Show from the BeforeSave handler: that handler's job is to cancel saving if the form isn't visible - not to unconditionally display that form! - you're getting this "can't show modally" error, because you're trying to show a modal form that's already modally displayed.

Read up on userforms and default instances on my Rubberduck blog.

Upvotes: 3

Related Questions