Excel VBA - Combobox

Question

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.

Pᴇʜ · Accepted Answer · 2022-03-10 11:06:17Z

1

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

answered Mar 10, 2022 at 11:06

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alok Over a year ago

Hey peH, nice explanation and I really like the gif file. Just curious to know the steps to make that gif file. Thanks

Pᴇʜ Over a year ago

@Alok I used the open source tool ScreenToGif for that.

Collectives™ on Stack Overflow

Excel VBA - Combobox

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related