Initialising ComboBox in an Excel user Form 1

[mgp]

Hi

Can anyone help me with this one:
I have a User form with a Drop down ComboBox where the RowSource property refers to a range on the worksheet.
Then I have a set of two options buttons, each of which changes the values of the same range on the worksheet.
The default value of the ComboBox (when the form is initialised) is the first value of the worksheet range.
Now when I press the options button the worksheet value changes, but the initial ComboBox value stays the same until I actually hit the drop down. How can I make the ComboBox update dynamically?

Thanks
Mogens

 

[cryoguy]

I found your dilemma to be another oddity of VBA. The following workaround should solve your problem. I found that setting the listindex to an object (x1 or x2) and then
setting the object to the listindex after the changing worksheet values to be one solution to your problem.
The following code uses two option buttons (OptionButton1 and OptionButton2) and a Combobox (Combobox1). The code replaces a list of fruit with a list of vegetables and vice-versa. (the RowSource property was set at a1:a4).


Private Sub OptionButton1_Click()
x1 = ComboBox1.ListIndex

Worksheets("Sheet1&quot.Range("A1&quot.Value = "1 broccoli"
Worksheets("Sheet1&quot.Range("A2&quot.Value = "2 carrots"
Worksheets("Sheet1&quot.Range("A3&quot.Value = "3 corn"
Worksheets("Sheet1&quot.Range("A4&quot.Value = "4 peas"

ComboBox1.ListIndex = x1
End Sub

Private Sub OptionButton2_Click()
x2 = ComboBox1.ListIndex

Worksheets("Sheet1&quot.Range("A1&quot.Value = "1 apples"
Worksheets("Sheet1&quot.Range("A2&quot.Value = "2 bananas"
Worksheets("Sheet1&quot.Range("A3&quot.Value = "3 cherries"
Worksheets("Sheet1&quot.Range("A4&quot.Value = "4 pineapples"

ComboBox1.ListIndex = x2
End Sub

Private Sub UserForm_Initialize()
ComboBox1.ListIndex = 0

End Sub

 

[mgp]

Thanks Cryoguy

I tried out your code and it worked fine.
I then also searched help for the ListIndex and found that by changing the BoundColumn property for the ComboBox from 1 to 0, it works the same way. The code for your example then would be

Private Sub OptionButton1_Click()
Worksheets("Sheet1&quot.Range("A1&quot.Value = "1 broccoli"
Worksheets("Sheet1&quot.Range("A2&quot.Value = "2 carrots"
Worksheets("Sheet1&quot.Range("A3&quot.Value = "3 corn"
Worksheets("Sheet1&quot.Range("A4&quot.Value = "4 peas"
End Sub

Private Sub OptionButton2_Click()
Worksheets("Sheet1&quot.Range("A1&quot.Value = "1 apples"
Worksheets("Sheet1&quot.Range("A2&quot.Value = "2 bananas"
Worksheets("Sheet1&quot.Range("A3&quot.Value = "3 cherries"
Worksheets("Sheet1&quot.Range("A4&quot.Value = "4 pineapples"
End Sub

Private Sub UserForm_Initialize()
ComboBox1.ListIndex = 0
End Sub