udf for returning names of wrkshts in active workbook

[Bassman62]

Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets in
the active workbook?
Thanks.

 

[ryguy7272]

List all Sheets in the Book:
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

List Sheets in the Book, 30 at a time, then shift over, list 30, repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name
iRow = iRow + 1
If iRow > 30 Then
iRow = 2
iCol = iCol + 1
End If
Next
Range("A1").Select
End Sub

Regards,
Ryan---

 

[Bassman62]

List all Sheets in the Book:
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

List Sheets in the Book, 30 at a time, then shift over, list 30, repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name
iRow = iRow + 1
If iRow > 30 Then
iRow = 2
iCol = iCol + 1
End If
Next
Range("A1").Select
End Sub

Regards,
Ryan---

 

[ryguy7272]

Please click 'Yes', if the post was helpful to you.

 

[Tim Zych]

Here's a UDF which returns a collection of worksheet names. It takes an
optional parameter for the workbook. If not specified, it defaults to the
ActiveWorkbook.

Function GetWorksheets(Optional ByVal wkb As Workbook = Nothing) As
Collection
Dim wks As Worksheet
' Default to ActiveWorkbook
If wkb Is Nothing Then Set wkb = ActiveWorkbook
Set GetWorksheets = New Collection
For Each wks In wkb.Worksheets
GetWorksheets.Add wks.Name
Next
End Function

' Sample access code

Sub Tester()
Dim n As Long, c As New Collection
Set c = GetWorksheets ' No workbook specified, so default to
ActiveWorkbook
' Set c = GetWorksheets(ActiveWorkbook) ' Alternative way to get the
ActiveWorkbook
' Set c = GetWorksheets(Workbooks("Book2.xls")) ' Or specify a
particular workbook
For n = 1 To c.Count
Debug.Print c(n)
Next
End Sub

 

[Bassman62]

I regret that my reply did not display. I'll try here.
Thank you for your suggestion.
I was hoping for a User Defined Function so that the reference would remain
updated in the event a worksheet name was changed. I suppose I could trigger
the procedure with a Change Event.
Thank you again for your reply.

 

[Bassman62]

Tim,
Thank you for your reply. I am new to UDFs.
I pasted the function into a module in my workbook.
I'd like to call the function in order to list and refer to the worksheets.
I tried =GetWorksheets but I'm only getting a #Value error.
Can I call the function in cell formulas? How?
Thank you.

 

[Tim Zych]

This returns an array of worksheet names.

To use it, add the function to a regular module.
Then select 1:N cells, either in one row or one column, and type
=GetWorksheets() and press Ctrl + Shift + Enter which creates an array
formula. Be sure to select at least as many cells as there are worksheets or
the result will omit some names.

Function GetWorksheets() As Variant
Application.Volatile
Dim vRet As Variant, i As Integer, wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
ReDim vRet(0 To Application.Caller.Cells.Count - 1)
For i = 0 To UBound(vRet)
If i < wkb.Worksheets.Count Then
vRet(i) = wkb.Worksheets(i + 1).Name
Else
vRet(i) = CVErr(xlErrValue) ' More cells selected than sheets
End If
Next
If Application.Caller.Rows.Count = 1 Then
vRet = Application.Transpose(Application.Transpose(vRet))
Else
vRet = Application.Transpose(vRet)
End If
GetWorksheets = vRet
End Function

To programmatically add the formula:

Sub AddUDF()
With Range("A1").Resize(Worksheets.Count)
.FormulaArray = "=GetWorksheets()"
' .Value = .Value 'optional to convert to values
End With
End Sub

 

[Bassman62]

This works wonderfully!
I've no idea how, so I shall enjoy disecting the code and hopefully gain
some insight.
I've tried for days to set this in motion and have found it difficult to
find applicable documentaion.
Thank you for your prompt and helpful replies.