need a userform to multiselect sheets and run loop macro

[boon-yao.tek]

I have tried creating a userform that able to show a list of worksheetnames in the listbox.

Please Login or Register  to view this content.

However I have no idea how I can run a macro for each of the sheets that have been selected in my listbox one by one.

I don't want the macro to run all at once, instead i need it to loop sheet by sheet.

I have tried many ways, and failed miserably.

[Trebor76]

Hi boon-yao.tek,

Firstly you need to change the MultiSelect property of your form from fmMultiSelectSingle to fmMultiSelectMulti so the user(s) can select more then option from the ListBox1 listbox. Then use this code:

Please Login or Register  to view this content.

Regards,

Robert

[boon-yao.tek]

I tried your code, still doesn't work.

I have attached a sample file here.

Can you help to have a look?

basically my macro codes is to append all the sheets table into one tab called "sales". I want a userform to select which sheets I need to combine basically because in the real file, there is many more sheets and some I don't need to combine.

sample.xlsm

i know the append function can be easily achieve with using Microsoft Access but my current company disallow us to use that. =(

[boon-yao.tek]

okay

i have added this line

Please Login or Register  to view this content.

now it works for a single sheet.
when i select a few sheets, the debug error prompts out stating run-time error '9', subscript out of range and it highlighted 'Sheets.(intSelectionIndex).Select' .

[boon-yao.tek]

Thanks alot Trebor76

I figured it out.

Somehow this is how I needs to modify the file.

First of all, I need to use fmMultiSelectMulti and not fmMultiSelectExtended for the listbox

Secondly, I need to tweak my code to look like this. Not sure why though.

Please Login or Register  to view this content.

[Trebor76]

when i select a few sheets, the debug error prompts out stating run-time error '9', subscript out of range and it highlighted 'Sheets.(intSelectionIndex).Select'

The there is no tab in the workbook with the exact same name that is being selected from the listbox.

First of all, I need to use fmMultiSelectMulti and not fmMultiSelectExtended for the listbox

That's what I said to do in my first post

Secondly, I need to tweak my code to look like this. Not sure why though.

The actual two lines of code (the rest are comments) are what I also provided in my first post

Robert

[boon-yao.tek]

Thanks Trebor76.

Here is my finalized sample file.
Combine Success1.xlsm
Hope others might find it useful.

[Trebor76]

Thanks Trebor76.

You're welcome and thanks for marking the thread as solved and for adding to my reputation

Regards,

Robert