Excel VBA - Combobox-CodePudding

I have a userform with 2 Comboboxes (Combobox1 and Combobox2) as well as a "Save" and a "Cancel" Button. My goal is now, whenever I select one item of one combobox the other one should be "blocked" or disabled. So it can only save one item from one of these two comboboxes, when the save button is pressed.

This is how far I've come:

If ComboBox1.Text = "" Then Cancel = 1 
    MsgBox "Data couldn't be saved. Insert item."
ElseIf Combobox1.Value > 0 And Combobox2.Text = "" Then 
    If Combobox2.Text = "" Then Cancel = 1 MsgBox "Data couldn't be saved. Insert item."
ElseIf Combobox2.Value > 0 And Combobox1.Text = "" Then
    If Combobox1.Value > 0 And Combobox2.Value > 0 Then Cancel = 1 MsgBox "Select only one item."

The issue now is, when I select one item for combobox1 and one for combobox2 it still saves it.

CodePudding user response：

In your userform use the following:

Option Explicit

Private Sub CancelButton_Click()
    'reset boxes
    Me.ComboBox1.Value = vbNullString
    Me.ComboBox2.Value = vbNullString
End Sub

Private Sub ComboBox1_Change()
    ' disable box 2 if box 1 has a value
    Me.ComboBox2.Enabled = (Me.ComboBox1.Value = vbNullString)
End Sub

Private Sub ComboBox2_Change()
    ' disable box 1 if box 2 has a value
    Me.ComboBox1.Enabled = (Me.ComboBox2.Value = vbNullString)
End Sub

Private Sub SaveButton_Click()
    If Me.ComboBox1.Value <> vbNullString Then
        MsgBox "Box 1 has the value"
    ElseIf Me.ComboBox2.Value <> vbNullString Then
        MsgBox "Box 2 has the value"
    Else
        MsgBox "In no box was selected a value"
    End If
End Sub