Userform reopens after Command Button calls it to close

[JakeB8]

My apologies if this has been covered previously. I did a quick search but wasn't sure how to summarize this in a search box.

I have a userform that is used to populate information into a workbook. There are three command buttons - Cancel, Pause, and Next. Cancel clears the userform and ends the macro. Next populates the provided information to the workbook and opens the next userform. Both work exactly as needed. I'm trying to add the Pause button so that users can go out to other Excel documents to gather/copy information (for those wondering, there is no specific file the information might come from so it's not possible to retrieve the information via vba code). I'm also adding a Restart button to the workbook to allow the users to pick up where they left off. Both sets of new code seem to be performing what is expected, but for some reason when the Pause button is clicked the userform closes and then opens again. A second click of the Pause button will close the userform and allow the user to go find the information they need. The code for the Pause button is posted below. It is similar to the code used for the Next button, so I'm stumped as to why the userform reopens when the Pause button is clicked. I'm sure it's something simple that I'm overlooking...any ideas? Thank you in advance!

Private Sub PauseButton1_Click() 'Clicking "Pause" performs the functions listed below.
Sheets("Form").Unprotect "change" 'Unprotects the "Form" tab to allow answers to populate the cells.
Sheets("Form").Range("C3").Value = Me.txtName.Value 'Adds the user's name to cell C3.
Sheets("Form").Range("C4").Value = Me.txtDate.Value 'Adds the date to cell C4.
Sheets("Form").Range("C5").Value = Me.cboUserLoc.Value 'Adds the user's location to cell C5.
Sheets("Form").Range("C6").Value = Me.txtCustomer.Value 'Adds the customer name to cell C6.
If Me.txtMake.Value = "" Or Me.txtModel.Value = "" Or Me.txtProgCode.Value = "" Then 'If any of the required information fields are blank then...
    Sheets("Form").Range("C7").Value = "" '...cell C7 is left blank.
    ElseIf Me.txtMake.Value <> "" And Me.txtModel.Value <> "" And Me.txtProgCode.Value <> "" Then 'If all of the required information fields are not blank then...
    Sheets("Form").Range("C7").Value = Me.txtMY.Value & " " & Me.txtMake.Value & " " & Me.txtModel.Value & " " & "(" & Me.txtProgCode.Value & ")"
    '...adds the vehicle information to cell C7 in "MY Make Model (Program Code)" format.
End If
Sheets("Form").Range("C8").Value = Me.txtComps.Value 'Adds the affected components to cell C8.
Sheets("Form").Range("D10").Value = Me.cboRequest.Value 'Adds the source of the change request to cell D10.
Me.Hide 'Closes the "Request Information" data form.
If Me.cboRequest.Value = Sheets("Data").Range("C1").Value Then 'If the source of the change request is the customer...
    Sheets("Form").Range("A11").Value = "What is the customer's Change Number?" 'Adds text to cell A11.
    Sheets("Form").Range("A12").Value = "What is the customer's requested implementation date?" 'Adds text to cell A12.
    Range("A22:H22").Locked = False
    Sheets("Form").Protect Password:="change", DrawingObjects:=False
        'From the two lines above the 2nd line protects the "Form" tab with the password "change" while allowing pictures to be inserted into the form.  The 1st line allows text to be entered into range A22 through H22 even though the tab is protected.
    ElseIf Me.cboRequest.Value = Sheets("Data").Range("C2").Value Then 'If the source of the change request is a supplier...
    Sheets("Form").Range("A11:D11").Interior.Color = RGB(192, 192, 192) 'Turns cells A11 through D11 gray.
    Sheets("Form").Range("A12").Value = "What date will supplier provide new/modified component(s)?" 'Adds text to cell A12.
    Range("A22:H22").Locked = False
    Sheets("Form").Protect Password:="change", DrawingObjects:=False
        'From the two lines above the 2nd line protects the "Form" tab with the password "change" while allowing pictures to be inserted into the form.  The 1st line allows text to be entered into range A22 through H22 even though the tab is protected.
    ElseIf Me.cboRequest.Value = Sheets("Data").Range("C3").Value Then 'If the source of the change request is internal...
    Sheets("Form").Range("A11:D11").Interior.Color = RGB(192, 192, 192) 'Turns cells A11 through D11 gray.
    Sheets("Form").Range("A12").Value = "What is the requested implementation date?" 'Adds text to cell A12.
    Range("A22:H22").Locked = False
    Sheets("Form").Protect Password:="change", DrawingObjects:=False
        'From the two lines above the 2nd line protects the "Form" tab with the password "change" while allowing pictures to be inserted into the form.  The 1st line allows text to be entered into range A22 through H22 even though the tab is protected.
    ElseIf Me.cboRequest.Value = "" Then 'If the source of the change request is not defined then end the loop.
    Range("A22:H22").Locked = False
    Sheets("Form").Protect Password:="change", DrawingObjects:=False
        'From the two lines above the 2nd line protects the "Form" tab with the password "change" while allowing pictures to be inserted into the form.  The 1st line allows text to be entered into range A22 through H22 even though the tab is protected.
End If
End Sub

 

[JakeB8]

Thank you for continuing to work with me on this, Trevor. I think I may have figured out a different method that seems to be working. Moving the call line for Userform2 inside the IF statement seems to be forcing the code to consider everything before calling up Userform2.

Private Sub NextButton1_Click()
If (Me.txtName.Value = "" Or Me.txtDate.Value = "") Then
    MsgBox "Please add the missing information to the form.", vbOKOnly, "Error"
            If Me.txtName.Value = "" Then
                Me.txtName.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtName.Value <> "" Then
                Me.txtName.BackColor = RGB(255, 255, 255)
            End If
            If Me.txtDate.Value = "" Then
                Me.txtDate.BackColor = RGB(255, 225, 225)
                ElseIf Me.txtDate.Value <> "" Then
                Me.txtDate.BackColor = RGB(255, 255, 255)
            End If
    ElseIf (Me.txtName.Value <> "" And Me.txtDate.Value <> "") Then
    MsgBox "Done.", vbOKOnly, "YAY"
    UserForm2.Show 'This line is was previously between "End If" and "End Sub."
End If
End Sub

I'm still not 100% clear on why this syntax forces Userform1 to stay open until all fields are filled, and the original syntax that I posted on Dec. 30 did not. The only explanation that I can think of is a potential difference between how modal and modeless userforms work. I didn't have this problem with the original syntax when the userforms were modal, and I don't know enough about modal/modeless functionality to fully explain how this could happen. The drawback to this newer method is that it requires a vast majority of the code for the userform to be inside a giant IF statement.

Regarding your suggestion, Trevor, disabling the Next button is an interesting idea but I do want to keep the error message as a reminder of why the user cannot proceed. There are over 160 potential users of the form that I created, and training levels will vary between all of those users. I'm concerned that future users may not be trained properly, or current users may forget the requirements.

Thank you again for all of the assistance, Trevor!

 

[Trevor_S]

I wasn't sure that I fully understood your Dec 30 code. In particular, whether the form referred to as Me was the same form as that you were trying to show again near the end? Or is the latter the second form? So that's why I thought it was better to produce a simplified example to demonstrate the process.

There are two ways to make it work - either by inserting the End to stop the macro flowing on to show the second form, or by moving the command that shows it into the If statement. Both methods work equally well.

As far as having a giant If statement goes, I don't see it as a problem provided that you keep the macro lines indented as you already have, and leave plenty of comment lines. That way it will be easy for anyone looking back at the code in the future to follow what is going on.