ListBox

[Lourens]

I am new to vba. i have big workbook with 200 sheets - need vba code to populate listbox ( or combobox) with sheetnames. Then i need to go to sheet if i click on sheetname. new sheets are added, old ones deleted. Please help.

[contaminated]

Hi

1. Press Alt+F11 Go to Insert and choose Userform
2. Insert Listbox cotrol to the userform
3. Double click o the userform (VBE will open)
4. Paste this code there

Please Login or Register  to view this content.

1. Close VBE
2. Insert a button and double click it
3. Paste this code
   

   Please Login or Register  to view this content.

4. Close VBE and Press DesignView Button so that button become clickable

Ready. Dummy is attached..
HTH

[snb]

This is a built-In facility in Excel.

go to scrollbuttons on the pagetab, (left lower corner of the window)
rightclick your mouse
choose the desired sheet.

[Lourens]

Hi

1. Press Alt+F11 Go to Insert and choose Userform
2. Insert Listbox cotrol to the userform
3. Double click o the userform (VBE will open)
4. Paste this code there

Please Login or Register  to view this content.

1. Close VBE
2. Insert a button and double click it
3. Paste this code
   

   Please Login or Register  to view this content.

4. Close VBE and Press DesignView Button so that button become clickable

Ready. Dummy is attached..
HTH

Thanks a huge lot - this is exactly what I need - just one more step - if i click for eg on sheet2, i need to work on sheet 2. So how can i close the userform ( with the code) after i clik on eg sheet 2?

[BBGDan]

Hey, you can use multiple ways do close it, either you can just create a button on the UserForm underneath for example the listbox or you can tell it programmatically that once you clicked on one of the selection option in the listbox to close down the UserForm.

This will close down the Userform after selecting a tab:

Please Login or Register  to view this content.

If you use the button approach just set up a button and add the code Unload Me on the actioncode.

[snb]

Maybe this is even more what you need (instead of using Excel's built-in facilities)
It's implemented in the attachment.

1. make a dropdownlist in the MenuBar (scrutinize the name of the personal workbook !)

Please Login or Register  to view this content.

2. Put this code in the workbook you want its sheets being displayed in the dropdownlist in the Menubar

Please Login or Register  to view this content.

3. put this macro in your personal.xls

Please Login or Register  to view this content.