VB Excel - populatin a list box with sheet names

[nieironsio]

Hello there

i am trying to populate a list box with the names of the sheets in a workbook. I know a way to do this if i know how many sheets there are but cannot do it for x amount of sheets.
Ideally i would sue this macro many of my workbooks and it would count how many sheets i have and display them in a userform listbox so i can select them for individual editing.

Thanks

 

[PHV]

A 2 seconds google search:

http://www.developerfusion.co.uk/show/2607

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nieironsio]

do you know - i have even seen that before and somehow managed to miss its significance

thanks - hopefully this will end my headache

 

[nieironsio]

for some reason i cannot make this code work - it produces an error.

 

[PHV]

So, what have you tried so far ?
Which error message and which line of code highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nieironsio]

sorry PHV - had logged off


This line produces and error

[ComboBox1.AddItem (test.Name)'Put the worksheets name in the combobox]

if i replace test.name with a sheet name like "sheet1" then my combo box just lists "sheet1" over and over and i need to list all sheets present.

thanks

 

[PHV]

could you please post your actual code and the error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nieironsio]

i got it to work about 10 minutes ago thanks - just took a bit of fiddling - thanks for you time

 

[stevexff]

The code in the linked example seems unnecessarily complicated. Can

Dim tempWorksheet As Worksheet 
Dim x, totalSheets 
x = 1

totalSheets = ThisWorkbook.Sheets.Count 'counts the sheets in the workbook 
Do While x <= totalSheets 
  Set tempWorksheet = Sheets(x) 'The tempWorksheets has now the content of the x worksheet 
  ComboBox1.AddItem (test.Name)'Put the worksheets name in the combobox 
  x = x + 1 'next worksheet! 
Loop

be replaced by

For Each s In Worksheets
   ComboBox1.AddItem (s.Name)
Next

to populate the combo box? It seems to work OK for me...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]