# Solved: Combo Box VBA Excel



## mamdani99 (Oct 21, 2008)

Dear All,

Please find an attached file "ComboBox.zip".

I have a userform with 1 Combo Box and 2 text items. I want to fill 3 text boxes as per database on sheet 1 upon selection of particular item from Combo Box.

Please help me how can I do this?

I hope you will respond me soon.


Thanks


----------



## computerman29642 (Dec 4, 2007)

mamdani99,

Please take a look at the attached workbook. The solution I have will work, but someone with more experience in Excel maybe able to give you a better way to perform what you want.


----------



## Rollin_Again (Sep 4, 2003)

The solution posted works but the code will be very tedious to maintain since you would have to "hard code" each possible value scanerio in the macro. If you don't have many possible combobox values this won't be a problem. A better option is to use the *INDEX/MATCH* or *VLOOKUP* function to return your desired column values or rowindex to use in the macro. Also keep in mind that you are setting the initial combobox values by using a named range in the workbook. Using this method requires you to insert new lines into the existing range if you want to add more values. In my opinion it would be easier to just loop through column A and add the values to the combobox on the fly. You could do this by adding a simple loop or using an array. The code to populate the combobox would then run when the form is activated or initialized. Let me know if you would like a sample posted.

Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

Thanks, Rollin. I knew there would have to be a better way.


----------



## computerman29642 (Dec 4, 2007)

Rollin, I know this is not my thread, but I would like to see an example of what you are explaining.


----------



## Rollin_Again (Sep 4, 2003)

A little more detailed explanation - Currently the form is set up in the following way. The combobox "Rowsource" property which populates the combobox is set up using a named range (A2:A5)

If the user adds another combobox value they would normally add the new value in cell A6. In order to have the new value appear they would either need to modify the named range to include the newly added cell or they would have to use Excel's insert row feature to add a new row somewhere within the existing named range. By using the insert method Excel will automatically expand the named range to include the newly added row. While easier than redefining the named range each time, this method is still kinda cumberson in my opinion and creates potential for problems from non-savy users. That is why I suggested having the combobox loop through the first column and add each value automatically on the fly when the form is initialized. Once the values are added we can create the logic to find the appropriate match and return it's value as well as the adjacent cells. Will post example shortly.

Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

Thanks, Rollin.


----------



## Rollin_Again (Sep 4, 2003)

Samples attached.

Both samples update the list of available combobox values when the form is initialized by using VBA to reset the RowSource property dynamically. All you need to do to add more values to the combobox is simply continue in the next blank cell in the A column.

One of the samples uses the combox_change event and the *VLOOKUP* method. The second example uses the *MATCH* method to grab the rownumber of the matching value and set it to a variable which is used to populate the textboxes.

Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

Rollin, the code looks really good. Can you explain what is taking place within the codes?


----------



## mamdani99 (Oct 21, 2008)

Thank Rollin_Again & Computerman29642 so much for your replies.

It really works very nice. But I have changed define Name which is not good.
I mean I searched on net for Dynamic Names Define now I have searched it on net. 

I hope you will like it. I attach my file for your ready reference.


Thanks both of you again.

Mamdani


----------



## Zack Barresse (Jul 25, 2004)

Why did your defined name change? And if it's an issue, why don't you code a dynamic range into your initialize event? Personally I don't use the RowSource/Vlookup method, but it works.


----------

