27-Dec-06 11:52 PM

Hi there

how can i retrive all the sheets names (or Caption) available in a workbook
and how can i find if a sheet exsist on the workbook

thak all it is a great newsgroup

28-Dec-06 04:59 AM

Enter and run this small macro:

Sub sheetlist()
Dim s As String
s = ""
For Each w In Worksheets
s = s & Chr(10) & w.Name
Next
MsgBox (s)
End Sub
--
Gary's Student

28-Dec-06 07:00 AM

On Thu, 28 Dec 2006 06:52:38 +0200, yosi_lb wrote in microsoft.public.excel:


Sub ListAllSheets(wbkBook As Workbook)

Dim shtSheet As Worksheet

For Each shtSheet In wbkBook.Sheets
Debug.Print shtSheet.Name
Next shtSheet

End Sub
'========
Function SheetExists(wbkBook As Excel.Workbook, strSheetName As String) As Boolean

Dim shtSheet As Worksheet

SheetExists = True
For Each shtSheet In wbkBook.Sheets
' This is case sensitive; making it not is left as an exercise for the reader.
If shtSheet.Name = strSheetName Then Exit Function
Next shtSheet
SheetExists = False
End Function

--
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"

29-Dec-06 09:48 AM

Thank you for your macro. It lists the first 55 worksheets. Is there any way
to get more? I have a file with around 80 sheets! Also, is there a way to get
a list of the hidden sheets as well??

29-Dec-06 11:58 AM

Try this version.

Will give a list of all sheet names starting at A1 on the active sheet.

Insert a new sheet and have that sheet active when you run the macro.

Will list all sheets including hidden sheets.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben  MS Excel MVP

On Fri, 29 Dec 2006 06:48:00 -0800, Traveller

29-Dec-06 12:54 PM

Hi Gord,

I guess I'm one of those who knows just enough to be dangerous!

When I entered your code without the word "Private" at the beginning, it
worked perfectly. Otherwise, I couldn't find it under my list of macros!?
How does it work with the word "Private" at the beginning?

29-Dec-06 01:55 PM

Private Subs are not listed in Tools>Macro>Macros because they
are.........Private and not for viewing by snoops.

It would still work, you would just have to type the name into the dialog box
then run it.

Or assign to a button or shortcut key combo.

My error in leaving the sub as Private when posting.


Gord


On Fri, 29 Dec 2006 09:54:01 -0800, Traveller

29-Dec-06 02:19 PM

Hi Gord,

Thanks again for the explanation. Thanks to generous folks like yourself,
people like me are able to learn and use some of the finer points of Excel
and other applications a little bit at a time, making our lives a whole lot
easier.

29-Dec-06 02:26 PM

I am sorry . . . another stupid question: if it is a Private Sub, which dialog
box do you type the name into??

29-Dec-06 02:38 PM

Tools|Macro|macros...
Type it into the Macro Name box.




--

Dave Peterson

29-Dec-06 02:39 PM

it is me again!

I figured it out. THANKS AGAIN!

29-Dec-06 02:54 PM

Gord Dibben wrote...

Since all procedure names are visible when viewing XLS files with a hex
editor, Private only prevents viewing by the village idiots, not
dedicated and knowledgeable snoops.

That said, better to make the procedure a function which could return
the list of worksheet names either as an array or as a long text
string. Much, much more flexible.


Function wslst(Optional rettxt As Boolean = False) As Variant
Dim rv As Variant, k As Long, wsc As Sheets

If TypeOf Application.Caller Is Range Then
Set wsc = Application.Caller.Parent.Parent.Sheets
Else
Set wsc = ActiveWorkbook.Sheets
End If

If Not rettxt Then ReDim rv(1 To wsc.Count, 1 To 1)

For k = 1 To wsc.Count
If rettxt Then
rv = rv & vbLf & wsc(k).Name
Else
rv(k, 1) = wsc(k).Name
End If
Next k

If rettxt Then
wslst = Mid(rv, 2)
Else
wslst = rv
End If

End Function

29-Dec-06 02:55 PM

Thanks, Dave. It was not working for me until I realized you need to type the
sheet number as part of the name of the macro. That did the trick!

29-Dec-06 03:15 PM

Michael Bednarek wrote...
...
...

Wouldn't

=ISNUMBER(ROWS(INDIRECT("'"&WorksheetNameHere&"'!A1")))

accomplish the same thing without VBA?

29-Dec-06 03:29 PM

I would put this kind of macro in a General module--not behind a worksheet and
not behind ThisWorkbook.


--

Dave Peterson

Excel - How to list all sheets

Asked By Michael Bednarek

29-Dec-06 11:02 PM

On 29 Dec 2006 12:15:03 -0800, Harlan Grove wrote in
microsoft.public.excel:


Indeed it would. Thanks for that.

--
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"