MrExcel Publishing
Your One Stop for Excel Tips & Solutions

how can i fill a combo box with values from a file?

Posted by combo on July 19, 2001 8:15 AM

i have list of files with data like:
id,name,more data...

the file length is varid and the names are not unique in the file.

i want to display all the names in the file in a combo box for the user to choose from (after he chose the file of data he wants to work on)

what i did is to import the data into a sheet,
and then used the combobox_gotFocus sub to go over the names
and only add the unique names from it to the combobox.

the problem is that when i click on the combobox
excel imports the data to another place in the sheet do the sub and then i used scrollRow to go back to the combobox, and it makes the screen blink and jump from one place to another.
im sure theres a better way doing it..

Posted by Damon Ostrander on July 19, 2001 11:06 AM

Hi combo,

You really don't even have to import the data into a sheet to do this. Just use VBA file I/O and use the combobox AddItem method to add each data item to the combobox as you read it from the file. If you are not familiar with file I/O in VBA, look at the helps for the Open statement, Input # and Line Input # statements, etc.

Also, you can use your existing method and eliminate the screen jumping, scrolling, etc., by avoiding activating or selecting objects. Other than copy and paste, you really never need to select objects in VBA in order to operate on them. For example, the statements:

Selection.Value = "Hello"

can be accomplished more simply without selecting the cell:

Range("B3").Value = "Hello"

If you really must select or activate things, then you can stop the scrolling temporarily by turning off screen updating.

Application.ScreenUpdating = False

but be sure to turn it back on before exiting your macro.

Happy computing.