Help required-VB Excel-list box that contains sheet names

[nieironsio]

Hello there

I am trying to populate a listbox on a userform with the sheet names of the spreadsheet. I need a code that picks up, firstly, how many sheets there are and then puts them into a listbox for selection. I would like the macro to work on any spreadsheet regardless of how many sheets there are.
Currently i can only create a code like this when i know how many sheets there are.

Thanks

Nie

 

[taupirho]

I'm not sure about the VB details but in VBA this is how to cycle thro all the sheets in the activeworkbook - perhaps you can modify the code for VB

For Each sht In ActiveWorkbook.Sheets
MsgBox sht.Name
Next

 

[HughLerwill]

This is VBA and there is an applicable answer for you in thread707-1291033.

regards Hugh,

 

[gmmastros]

You can get a list of sheet names within an excel workbook using ADO. To run this example, you will need to add a reference. Click Project -> References. Scroll to 'Microsoft ActiveX Data Object 2.x Library' and select it.

Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset

Set DB = CreateObject("ADODB.Connection")
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[!]C:\Book1.xls[/!];Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Call DB.Open

Set RS = DB.OpenSchema(adSchemaTables)
While Not RS.EOF
    Debug.Print RS.Fields.Item("TABLE_NAME").Value
    RS.MoveNext
Wend
RS.Close
Set RS = Nothing

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nieironsio]

thanks for the hint although it it's not working - i saved the document and made sure the red text in your example matches my name but it is bringing uo an error saying that the jet database engine could not find the object.not too sure what to do

 

[gmmastros]

I tested this before posting, so I'm sure it works. There must be a problem with your implementation.

1. Did you add a reference to 'Microsoft ActiveX Data Object 2.x Library' ?
2. Do you have the excel document open in excel? If so... close excel and try again.

If the previous suggestions don't work for you, then post the exact error message and indicate which line is causing the problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SkipVought]

Nie,

Are you using VB 5 or 6

OR

are you coding in an Excel workbook using VBA?

taupirho gave you a valid answer. Just reference the workbook object properly.

Skip,

_[red][/red]

 

[nieironsio]

thanks for your time guys - i am coding an excel workbook using vba - i am, as you have probably guessed, a noob with all this. I am just taking george's advice and checking it through again.
I tried taurpirho's answer earlier but unfortunately could not make it work.

 

[SkipVought]

Please post in forum707 VBA Visual Basic for Applications (Microsoft)

Skip,

_[red][/red]

 

[taupirho]

Nie,

my answer won't work in VB6 without modification - I thought I made that clear. Where I have activeworkbook you need to replace that with the name workbook object you have created as Skip implied also.

 

[nieironsio]

apologies - will do

 

[Swi]

I know that nieironsio is using VBA but just in case someone else comes across this post at a later date ADOX is another method:

thread222-939116

Swi