User Defined Function Needed

[Salut39]

I want to list all worksheets name in one sheet. To do that I thought to add variable to the function I found before (where it says 1 I thought to change it to row number so when I copy function it will list the sheets names. Appreciate any help or new suggestions how to acheive that.

Returning The Name Of The Next Worksheet

The following function will return the name of the next worksheet.

Function NextSheetName() As String
Application.Volatile True
With Application.Caller.Parent
NextSheetName = _
.Parent.Worksheets((.Index Mod .Parent.Worksheets.Count) + 1).Name
End With
End Function

 

[xlbo]

If you want to list all worksheets, ther eis no need to have a function to find the next - simply iterate through the collection:


i=2
for each ws in thisworkbook.worksheets
sheets("Sheetname").cells(i,1).value = ws.name
i = i + 1
next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[abaldwin]

This will put each sheet name in the current workbook into column A of the sheet from which you run the macro.

Public Sub ListSheets()
Dim iSheetCounter As Integer

For iSheetCounter = 1 To Sheets.Count
Sheets(1).Cells(iSheetCounter, 1).Value = Sheets(iSheetCounter).Name

Next iSheetCounter
End Sub

Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[abaldwin]

Sorry xlbo I had not apparently hit my submit button the first time. Did not mean to step on your toes.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[xlbo]

no worries - many many many ways to do this one - the more the merrier !!!!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Salut39]

Thank you, Guys. Brilliant Stuff! All working great.

 

[firefytr]

Also take a look at these ...

http://vbaexpress.com/kb/getarticle.php?kb_id=402

http://vbaexpress.com/kb/getarticle.php?kb_id=401

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Salut39]

Thanks Zack, I preferred function as it updates itself if adding a new sheet. Cheers.

 

[Salut39]

I notice some problem with function: when I open a new workbook function pick up sheet names from that sheet. You have to run calculation again. Do you know fix it? Cheeers.

 

[firefytr]

Ensure it has ..

Application.Volatile = True

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Salut39]

I use this and it still does recalculation.

Option Explicit

Public Function Sheet(wsIndex As Long) As String
Application.Volatile True
Sheet = Worksheets(wsIndex).Name
End Function

 

[misterstick]

if all you want is some method for the user to click a cell or a button to go to the next sheet (whatever it may be) rather than faffing about with vba put the following text in cell A1:

Press Control-Page Down to go to the next sheet.

mr s. <

 

[Salut39]

No I want a function which lists sheet names and relates to this workbook only. I will be adding more sheets and need them to be updated as I add them.

 

[misterstick]

why do you need to list the sheet names?

the user can see them along the bottom of the workbook, and if there are too many the user can right click the sheet navigator.

why duplicate information?


mr s. <

 

[Salut39]

I do that to create summary and combine sheet name with the formula to pick up relevant infomation from sheets.

=INDIRECT( "'" & $A72 & "'" & "!$A$39")

 

[misterstick]

you're not going to be inserting rows along with the sheet, so perhaps you should rewrite the first sheet when a new sheet is inserted.

' in workbook code pane

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim wks As Worksheet
Dim rng As Range
Me.Worksheets("summary").UsedRange.ClearContents
Set rng = Me.Worksheets("summary").Range("a1")
For Each wks In Me.Worksheets
  If wks.Name <> "summary" Then
    rng.Value = wks.Name
    rng.Range("b1").Formula = "='" & wks.Name & "'!YOURFORMULA"
    Set rng = rng.Range("a2")
  End If
Next
' you may want to add a summary formula here
End Sub

what you do when one is deleted i don't know. you could always just update the summary sheet on save.


mr s. <

 

[Salut39]

Thanks I'll try that.

 

[firefytr]

Are you just wanting a table of contents then? ...

http://vbaexpress.com/kb/getarticle.php?kb_id=120

To use this function ...

Public Function Sheet(wsIndex As Long) As String
    Application.Volatile True
    Sheet = Worksheets(wsIndex).Name
End Function

.. for the workbook the formula is entered in, change it to this ...

Public Function Sheet(wsIndex As Long) As String
    Dim wb As Workbook
    On Error Resume Next
    Application.Volatile True
    Set wb = Workbooks(Application.Caller.Parent.Parent.Name)
    Sheet = "ERROR!"
    Sheet = wb.Worksheets(wsIndex).Name
End Function

This will now handle an invalid sheet index number and use only the parent workbook.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Salut39]

That's perfect, works well. Thanks a lot, Zack!

 

[firefytr]

You're very welcome Salut39.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci