Combobox in Excel

[ramankumar]

I have a combobox in my excel spreat sheet. I want to use a macro to fill the combobox with data. In the format Control option dialog I want to use a macro in InputRange field. BUt i am not able to use a macro for this. Any help is appreciated.

 

[Andrew Poulsom]

Controls from the Control Toolbox are different from the old Excel controls (Forms Toolbar).

Activate the Control Toolbox and click the Design icon (top left). Right click your combo box and choose Properties. Set the ListFillRange property eg A1:A10 and close the Properties window. Click the Design icon again to exit design mode.

 

[ramankumar]

I want to display a range which is dynamic in nature. In this case how do i get the data filled in combobox.

 

[Andrew Poulsom]

You can use a named range for the ListFillRange property eg MyList. However, you may find that it does not refresh properly when the length of the list changes. You can force the refresh by using:

ListBox1.ListFillRange = "TheList"

in a suitable Sheet event procedure.