Populate Excel Userform with Sheet names

[PC]

Hi,

I'm using this piece of code to populate an excel userform listbox with the
name of each worksheet in a workbook

i = 1
For Each ws In Worksheets
Worksheets.Select
Sheets(i).Activate
ListBox1.AddItem (ActiveSheet.Name)
i = i + 1
Next ws


Is there a better way I can do this as this visably activates each
sheet each time the listbox is repopulated.

Thanks

...pc

[Leith Ross]

Hello PC,

Paste this code into your program.

I = 1
For Each ws In Worksheets
ComboBox1.AddItem (Sheets(I).Name)
I = I + 1
Next ws

Hope this helps,
Leith Ross

[Dick Kusleika]

PC

For Each ws In ActiveWorkbook.Worksheets
ListBox1.AddItem ws.Name
Next ws

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

PC wrote:
> Hi,
>
> I'm using this piece of code to populate an excel userform listbox
> with the name of each worksheet in a workbook
>
> i = 1
> For Each ws In Worksheets
> Worksheets.Select
> Sheets(i).Activate
> ListBox1.AddItem (ActiveSheet.Name)
> i = i + 1
> Next ws
>
>
> Is there a better way I can do this as this visably activates each
> sheet each time the listbox is repopulated.
>
> Thanks
>
> ..pc

[Shacker]

this is good code

Please Login or Register  to view this content.

now how to add double click on the sheet in the list box and open this sheet ???
any Idea's ??
or is it possible ?

[arlu1201]

Shacker,

This thread is 8 yrs old. Please create a new thread for yourself.

[Shacker]

8 years ???

lol, Sorry , i dodn't notice that :D

will do another one now , thank you