command buttons not working

[Pookiemeister]

Private Sub cmbSDPFLine_Exit(ByVal Cancel As MSForms.ReturnBoolean)    Dim rtn_ans1 As String


    If cmbSDPFLine.Value = "" Then
        rtn_ans1 = MsgBox("Please select a production line.", vbOKOnly, "Please Select Production Line")
        Select Case rtn_ans1
            Case 1
                Cancel = True
                cmbSDPFLine.SetFocus
         End Select
    End If
End Sub

What I am trying to achieve with the above code is when the user exits the required text box above it will return back to that text box until a selection is made. Which it does. I also have a command button labeled cancel.

Private Sub cmdbtnCancel_Click()    Unload Me
End Sub

The problem that I am having is if the user wants to cancel out of the form without making a selection, and clicks the Cancel Button, the above message box keeps displaying until the user makes a selection and then clicks the cancel button. Also if the user clicks on the white "X" with the red background on the form the message box also displays but once the user acknowledges the OK button the form closes. So what I like is once the user clicks on the white X or the clicks the cancel button I need the form to close without displaying the message box. How can I achieve this? Thank You.

 

[Norie]

You need to stop the code in the Exit event of cmbDSPFLine running when the form is being closed.

To do that you can use a boolean variable to 'disable' events.

Let's call that variable frmEnableEvents and declare it at the top of the userform module.

Private frmEnableEvents As Boolean

In the form's Initialize event we set it's value to True so that all events are enabled.

Private Sub UserForm_Initialize()

    Me.frmEnableEvents = True

    ' other initialize code

End Sub

Now to stop the code in Exit event of cmbDSPFLine running we can change your posted code like this.

Private Sub cmbSDPFLine_Exit(ByVal Cancel As MSForms.ReturnBoolean)    Dim rtn_ans1 As String

    If Me.frmEnableEvents = False Then Exit Sub

    If cmbSDPFLine.Value = "" Then
        rtn_ans1 = MsgBox("Please select a production line.", vbOKOnly, "Please Select Production Line")
        Select Case rtn_ans1
            Case 1
                Cancel = True
                cmbSDPFLine.SetFocus
         End Select
    End If

End Sub

' ... other code

Private Sub cmdbtnCancel_Click()
    Me.frmEnableEvents = False
    Unload Me
    Me.frmEnableEvents = True
End Sub

If there are any other subs that you have code you don't want executed when the form closes just add this at the start.

If Me.frmEnableEvents = False Then Exit Sub