combo box clear and fill again

[NIWANut]

Hi,

I have a combo box on a Userform (initiated by clicking a command button)that I want to show all the names of the sheets in a workbook - no problem to achieve this - I added the names using AddItem on the Userform Initialise event, then I show the Userform. The inital value shown in the box is the text from the Value parameter of combobox properties which is

"Choose sheet to copy data to -->"

The idea is the user clicks the down arrow and selects a sheet by its name. This all works.

After the sheet is chosen I have the Combobox.Change event set to do its work, so that it immediately hides the box and does the copy. Later the user might repeat the process and here is where I have a problem. I want the combobox to be cleared and a new list created (the user may have added more sheets which are not in the original list). If I don't clear the list then the last chosen value is shown. If I clear the list the Combobox.change event is triggered, I tried putting "Application.EnableEvents = False" before the Combobox.Clear statement but it still run the change event.

I tried this in both the initialise event for the userform and the activate event with the same results.

Is there any way to clear the comboxbox list and re-populate it without the change event code running?

George

 

[fumei]

Yes. If I understand correctly, when the user makes a change from your default text, the Change event does the work.

If you do not want the last selection to be in the combobox, make a Populate sub.

Populate does:

1. makes a string array myArray (or whatever) of the worksheet names, but start with your default text. This will be ListIndex (0).

2. Combobox1.Clear

3. to the count of worksheets.count, pass through the array, using AddItem to populate the combobox.

4. set ListIndex = 0, setting it back to your default text.

Having the populate routine separate means you can call it at the end of the Change event. It will always clear and repopulate after the change events fire.

Gerry

 

[NIWANut]

Hi Gerry,

Thanks for that, a nice little trick.

George