combobox update

[Alex]

I have 2 comboboxes on a userform. If the one is chosen with data the second
one should be empty and vice versa.

I'm trying to do the following code but to chose the data I need to click
twice on a combobox if another one is with some data. The after-update gives
the same result.

Private Sub cboBox1_Change()
If Not IsNull(cboBox1.Value) Then
If Not IsNull(cboBox2) Then
cboBox2= Null
End If
cboBox1.SetFocus
End If
End Sub
Private Sub cboBox2_Change()
.....

How could I make it to get data right after the clicking.

Thanks

 

[Dave Peterson]

How about something like:

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
Call CheckComboBoxes(1)
End Sub
Private Sub ComboBox2_Change()
If BlkProc = True Then Exit Sub
Call CheckComboBoxes(2)
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
'some test data
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem "A" & iCtr
Next iCtr
For iCtr = 11 To 20
Me.ComboBox2.AddItem "B" & iCtr
Next iCtr
End Sub
Sub CheckComboBoxes(WhichOne As Long)

Dim OtherOne As Long

If WhichOne = 1 Then
OtherOne = 2
Else
OtherOne = 1
End If

If Me.Controls("combobox" & WhichOne).Value = "" Then
'do nothing, it's empty
Else
BlkProc = True
Me.Controls("combobox" & OtherOne).Value = ""
BlkProc = False
End If

End Sub

The BlkProc almost works like .enableevents on a worksheet. But the code just
checks that variable and gets the heck out if the program is changing the
combobox.

(I did rely on the comboboxes having nice names--but you could pass it the name
of the current combobox and use that in the checkcomboboxes procedure.)

 

[Alex]

Thank you, Dave. It's working great.

How about something like:

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
Call CheckComboBoxes(1)
End Sub
Private Sub ComboBox2_Change()
If BlkProc = True Then Exit Sub
Call CheckComboBoxes(2)
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
'some test data
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem "A" & iCtr
Next iCtr
For iCtr = 11 To 20
Me.ComboBox2.AddItem "B" & iCtr
Next iCtr
End Sub
Sub CheckComboBoxes(WhichOne As Long)

Dim OtherOne As Long

If WhichOne = 1 Then
OtherOne = 2
Else
OtherOne = 1
End If

If Me.Controls("combobox" & WhichOne).Value = "" Then
'do nothing, it's empty
Else
BlkProc = True
Me.Controls("combobox" & OtherOne).Value = ""
BlkProc = False
End If

End Sub

The BlkProc almost works like .enableevents on a worksheet. But the code just
checks that variable and gets the heck out if the program is changing the
combobox.

(I did rely on the comboboxes having nice names--but you could pass it the name
of the current combobox and use that in the checkcomboboxes procedure.)