×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Initialising ComboBox in an Excel user Form

Initialising ComboBox in an Excel user Form

Initialising ComboBox in an Excel user Form

(OP)
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




RE: Initialising ComboBox in an Excel user Form

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").Range("A1").Value = "1 broccoli"
Worksheets("Sheet1").Range("A2").Value = "2 carrots"
Worksheets("Sheet1").Range("A3").Value = "3 corn"
Worksheets("Sheet1").Range("A4").Value = "4 peas"

ComboBox1.ListIndex = x1
End Sub

Private Sub OptionButton2_Click()
x2 = ComboBox1.ListIndex

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

ComboBox1.ListIndex = x2
End Sub

Private Sub UserForm_Initialize()
ComboBox1.ListIndex = 0

End Sub

RE: Initialising ComboBox in an Excel user Form

(OP)
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").Range("A1").Value = "1 broccoli"
Worksheets("Sheet1").Range("A2").Value = "2 carrots"
Worksheets("Sheet1").Range("A3").Value = "3 corn"
Worksheets("Sheet1").Range("A4").Value = "4 peas"
End Sub

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources