How to get a list of sheets name from a workbook (by selections, but not to
open it) so that I could make a selection on a sheet name I want.

Thanks in advance.

Hi Simon,

To make a list of sheets, try:

'================
Sub ListSheets()
Dim WB1 As Workbook, WB2 As Workbook
Dim SH As Worksheet
Dim i As Long

Set WB1 = ActiveWorkbook '<<=== CHANGE
Set WB2 = Workbooks("BbookToList.xls") '<<=== CHANGE

With WB1
Set SH = .Sheets.Add(after:=.Sheets(.Sheets.Count))
End With

For i = 1 To WB2.Sheets.Count
SH.Cells(i, "A").Value = WB2.Sheets(i).Name
Next i

End Sub
'<<================

To show a selectable popup list of sheets in the activeworkbook, try:

'================
Sub ShowSheetList()
'Jim Rech
On Error Resume Next
If ActiveWorkbook.sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs"). _
ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs"). _
Controls("More Sheets...").Execute
End If
On Error GoTo 0
End Sub
'<<================

---
Regards,
Norman



"Simon Chang" wrote in message
...
How to get a list of sheets name from a workbook (by selections, but not
to
open it) so that I could make a selection on a sheet name I want.

Thanks in advance.

Actually, I use "Application.GetOpenFilename" code to browse my workbooks
and pick any of them (but not to open them) which is from there I tries to
retrieve the sheets name. So I tried your codes by adding my codes but it
not respond to my code.

This is what I've tried:

Sub ShowSheetList()
'Jim Rech
mybook = Application.GetOpenFilename("Pick any workbook (*.xls), *.xls",
vbApplicationModal, "Good Luck")

On Error Resume Next
If mbook.Sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs"). _
ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs"). _
Controls("More Sheets...").Execute
End If
On Error GoTo 0
End Sub

Your respond is on my appreciations and thanks in advance.

Hi Simon,

The suggested code will not operate on a closed workbook.

If your intention is that the workbook should not be seen, perhaps you could
turn off screen updating, open the requisite workbook, list the sheet names,
close the workbook and then restore screen updating.

---
Regards,
Norman



"Simon Chang" wrote in message
...
Actually, I use "Application.GetOpenFilename" code to browse my workbooks
and pick any of them (but not to open them) which is from there I tries to
retrieve the sheets name. So I tried your codes by adding my codes but it
not respond to my code.

This is what I've tried:

Sub ShowSheetList()
'Jim Rech
mybook = Application.GetOpenFilename("Pick any workbook (*.xls), *.xls",
vbApplicationModal, "Good Luck")

On Error Resume Next
If mbook.Sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs"). _
ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs"). _
Controls("More Sheets...").Execute
End If
On Error GoTo 0
End Sub

Your respond is on my appreciations and thanks in advance.