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]

I have rethought my strategy for what I need the macro to do when the Pause button is clicked, and have reduced the code to something much more simple.

Private Sub PauseButton1_Click()
Me.Hide 'Clicking "Pause" closes the "Request Information" data form.
End Sub

I am still having the issue of the userform reloading when I click the Pause button, and needing to click the button a second time to close the userform. I do not want to end all of the macro processes when I click Pause. Otherwise the user will have to start all over just like they did previously, and that defeats the purpose. With such a simple code I have no idea what might be causing this.

 

[Trevor_S]

When you first show the userform, could you show it as modeless? This then allows the user to go to other documents, copy/paste etc. without even needing a pause button. And as the form has never been closed, nothing will have been lost.

On whatever code line first shows the userform, change .Show to .Show (vbModeless)

 

[JakeB8]

Thank you, Trevor_S! That seems to have done the trick. I'm still fairly new to coding VBA, and I didn't see anything like that in web searches so I wasn't aware of that possibility.

 

[Trevor_S]

Glad that it's worked. I only found it a couple of years ago. I don't know why Microsoft haven't made it the default setting!

 

[JakeB8]

Unfortunately this isn't as solved as I had hoped it would be. The (vbModeless) idea works great when the userform is initiated. However, there are some steps in the userform code that provide an error to the user if they don't provide all of the necessary information to complete the userform. Example code is below. The idea is to have a message box come up informing the user of the error. Once they click OK on the message box the userform is supposed to reopen so that the user can add the missing information. If (vbModeless) is used here the userform doesn't reopen. I can't have this because we need to require that all necessary information is provided in the userform without a loophole to leave it out. Any ideas? I've also briefly tried removing the "Me.Hide" and "userform.Show" lines but didn't have the response from the macro that I was looking for. Thank you!

If Me.txtName.Value = "" Then
      MsgBox "Please add the missing information to the form.", vbOKOnly, "Error"
      Me.Hide
            If Me.txtName.Value = "" Then
                Me.txtName.BackColor = RGB(255, 225, 225)
                'If this is the missing value then the textbox is turned pink.  This test is repeated for all information boxes on the userform.
                ElseIf Me.txtName.Value <> "" Then
                Me.txtName.BackColor = RGB(255, 255, 255)
            End If
      ChgMgmtRqstForm1.Show (vbModeless) 'If vbModeless is left in the code here then the userform doesn't reopen.
End If

 

[Trevor_S]

I've done a simple example to test this. One form (UserForm1) containing one text box (TextBox1) and one button (CommandButton1).

This code is in the ThisWorkbook section:

Sub macro()
UserForm1.Show (vbModeless)
End Sub

This code is in the UserForm1 section:

Private Sub CommandButton1_Click()
If TextBox1.Value = "" Then
  MsgBox ("Please complete the text box, the form will be left open")
Else
  MsgBox ("Text box is completed, rest of process will be completed, then the form will be closed")
  ' Put the code for the rest of the process here
  UserForm1.Hide
End If
End Sub

Run the first macro to show the form. When the button is clicked, if the text box is empty, an error message is shown and the form is still visible when the message is closed. If the text box isn’t empty, a notification message is shown – once this is closed, the rest of the process is run (which in this example involves closing the form).

It seems to work - can you adapt this to fit within your code?

 

[JakeB8]

I'm really stumped on this now.

I started a new workbook and added the code you suggested to the ThisWorkbook section. I added a command button to the worksheet so that I could use it to call Userform1 (this is how my project is set up). I created Userform1 with two text boxes so that I could test multiple fields when clicking the Next (command) button on the userform, just like my project must. I also adapted some of my code into your Userform1 example. After testing it out I discovered that I needed to have the userform defined as modeless when calling it from the command button on the worksheet, and further testing showed that because of this I no longer needed to have the code in the ThisWorkbook section. So here's what the test workbook code looks like.

ThisWorkbook
**blank**

Sheet1

Private Sub commandbutton1_click()
UserForm1.Show (vbModeless)
End Sub

Userform1

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"
End If
End Sub

Everything works exactly like I want it to. Upon opening the userform it is modeless; if one of the text boxes is left blank the error message appears when the Next button is clicked, and upon clicking OK the userform is still visible and the text box that is missing information turns pink; best of all the userform is still modeless; and once both text boxes are filled out the second message appears when the Next button is clicked.

So I further adapted my project code to follow this example, and for whatever reason it's still not working. The actual userform in my project contains 10 fields that are a combination of text boxes and combo boxes. 9 of the fields are required to have information, so all 9 are checked upon clicking the Next button and the error message does still work. Once the OK button on the error message is clicked the program moves on to the next step in the process (which would be located after the End If in the example above). If the example is working properly, why can't I get my project to work the same way?

 

[JakeB8]

Further testing update.

I added another userform to the test workbook to be called by the Next button on Userform1.

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"
End If
UserForm2.Show
End Sub

It's placed where it shouldn't open until Userform1 is completely filled out, but once the OK button on the error message is clicked the code moves straight to Userform2. So the issue isn't necessarily about re-calling a userform as modeless, it's something to do with writing the code this way. I have no idea why this is a problem though.

 

[Trevor_S]

As I read it, the macro checks whether one or both text boxes are blank ("outer" If statement). If so it displays an error message and colours the boxes accordingly (indented If statements).

But I can't see anything then stopping it flowing on through to the EndIf line, then showing the second userform. EndIf doesn't stop macro execution, it just marks the end of the code within the conditional statement - i.e. it's where the macro jumps to if it needs to skip any Else code.

I think you need a line that just says End immediately before each of the two indented EndIf lines, just to stop it flowing on after the colour changes.

As another thought ... an option might be to forget the error message? You could disable the Next button before showing userform 1:

UserForm1.NextButton1.Enabled = False

Then each time text is input into a text box, check whether the required boxes are completed - and if they are, enable the Next button, otherwise disable it again (in case the boxes had been ok prior to the latest change). That way, if the user is able to click the Next button, the form must be ok.