Change ComboBox to ListBox To Write To Individual Sheets

[ScotyB]

Hi all,

in a training database, there is a userform for writing data to selected sheets using an assortment of comboboxes, listboxes and textboxes - I'd like to change the functionality of the userform a little. Code is attached for perusal...

Please Login or Register  to view this content.

The savedata routine is as follows...

Please Login or Register  to view this content.

Using 'select case' with combobox4 means I have to click the command button up to 28 times (for 28 staff members) and I wonder if someone could show me what I need to do in terms of coding if I change the combobox to a multi-select listbox - I'm thinking a for-next loop to cycle through each of the sheets and write data but I'm not quite sure how to accomplish that.

Cheers,
AJ

[Norie]

You don't need to change to a listbox if want to loop through all the sheets.

You can loop through the list in the combobox.

Please Login or Register  to view this content.

[ScotyB]

Thanks for taking a look, Norie,

does that mean I can delete all the individual case statements and simply replace them with the above code?

Cheers,

AJ

[Norie]

Will you still want to have the code run on individual sheets which you select with the combobox?

If you do then you could replace the Select Case with this.

Please Login or Register  to view this content.

[ScotyB]

Norie,

the answer to your last question is yes, however, being a numpty I couldn't find anywhere in the combobox properties that would allow me to choose a multi-select option so I went ahead and changed it to a listbox, modified your code accordingly, and hey presto, it seemed to work. When I checked the sheets however, I found that it had written data to sheets that WEREN'T selected and not only that, it duplicated the data some 20+ times on the last sheet in the loop. So, because I'm still learning, I checked ny other snippet of listbox code and using that as an example, added an if/then statement to what you gave me, as follows...

Please Login or Register  to view this content.

This worked a treat on the cut-down version I had on my USB stick, so tomorrow, when I'm back at work, I'll try it on the full version of my training database, and if there are no problems, I'll be more than happy to come back, mark this as solved, and give you the deserved gold star.

Thanks, mate, for pointing me in the right direction.

Cheers,

AJ

[Norie]

The problem is actually more likely to be with the sub you are calling not referencing the correct worksheet(s).

[ScotyB]

No worries, Norie, I'll check into that aspect for any future work. For now, however, the amended code is working fine on my work database so thanks again for pointing me in the right direction - I appreciate your time and help.

Cheers,

AJ