Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheets
I would like to list all the worksheet tab names starting in f5 going down.
I would also like it to update if a tab name is changed or the order of the worksheets are rearranged is this possible. Thanks Blue |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheets
Place this code in the VB for the worksheet you want to update and each time
you go to that sheet it will be updated... You did not mention anything about deleting sheets so I have not taken that contingency into account. If you need help with that let me know... Private Sub Worksheet_Activate() Dim rngToUpdate As Range Dim wks As Worksheet Set rngToUpdate = Range("F5") Application.ScreenUpdating = False For Each wks In Worksheets rngToUpdate = wks.Name Set rngToUpdate = rngToUpdate.Offset(1, 0) Next wks Application.ScreenUpdating = True Set rngToUpdate = Nothing End Sub HTH... "Blue" wrote: I would like to list all the worksheet tab names starting in f5 going down. I would also like it to update if a tab name is changed or the order of the worksheets are rearranged is this possible. Thanks Blue |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheets
Blue wrote...
I would like to list all the worksheet tab names starting in f5 going down. I would also like it to update if a tab name is changed or the order of the worksheets are rearranged is this possible. Renaming worksheets triggers recalculation, so a UDF approach would be the most efficient way to do this. Function wsc(Optional rng As Range) As Variant If rng Is Nothing Then Set rng = ActiveCell wsc = rng.Parent.Parent.Worksheets.Count End Function Function wsl(Optional rng As Range) As Variant Dim v As Variant, i As Long, n As Long If rng Is Nothing Then Set rng = ActiveCell With rng.Parent.Parent ReDim v(1 To .Worksheets.Count) For i = 1 To .Worksheets.Count v(i) = .Worksheets(i).Name Next i End With wsl = v End Function Then use it in a multiple cell array formula, e.g., select F5:F104 and enter the array formula =IF(ROW()-ROW($F$5)<wsc(),wsl(),"") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheets
Thanks Jim does just what I want.
If you can remove deleted sheets as well that would an extra bonus. Thanks Blue "Jim Thomlinson" wrote in message ... Place this code in the VB for the worksheet you want to update and each time you go to that sheet it will be updated... You did not mention anything about deleting sheets so I have not taken that contingency into account. If you need help with that let me know... Private Sub Worksheet_Activate() Dim rngToUpdate As Range Dim wks As Worksheet Set rngToUpdate = Range("F5") Application.ScreenUpdating = False For Each wks In Worksheets rngToUpdate = wks.Name Set rngToUpdate = rngToUpdate.Offset(1, 0) Next wks Application.ScreenUpdating = True Set rngToUpdate = Nothing End Sub HTH... "Blue" wrote: I would like to list all the worksheet tab names starting in f5 going down. I would also like it to update if a tab name is changed or the order of the worksheets are rearranged is this possible. Thanks Blue |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheets
Hi Halan
Thanks for the response. I can only get the first tab name to appear. I am testing it in a workbook the four tabs, it names the first tab four times. If I add a fifth worksheet it still only names the first tab four times times. If I chnage the name of the first tab nothing happens. :o( Blue "Harlan Grove" wrote in message oups.com... Blue wrote... I would like to list all the worksheet tab names starting in f5 going down. I would also like it to update if a tab name is changed or the order of the worksheets are rearranged is this possible. Renaming worksheets triggers recalculation, so a UDF approach would be the most efficient way to do this. Function wsc(Optional rng As Range) As Variant If rng Is Nothing Then Set rng = ActiveCell wsc = rng.Parent.Parent.Worksheets.Count End Function Function wsl(Optional rng As Range) As Variant Dim v As Variant, i As Long, n As Long If rng Is Nothing Then Set rng = ActiveCell With rng.Parent.Parent ReDim v(1 To .Worksheets.Count) For i = 1 To .Worksheets.Count v(i) = .Worksheets(i).Name Next i End With wsl = v End Function Then use it in a multiple cell array formula, e.g., select F5:F104 and enter the array formula =IF(ROW()-ROW($F$5)<wsc(),wsl(),"") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheets
"Blue" wrote...
Thanks for the response. I can only get the first tab name to appear. I am testing it in a workbook the four tabs, it names the first tab four times. If I add a fifth worksheet it still only names the first tab four times times. If I chnage the name of the first tab nothing happens. :o( Oops! The return array should be vertical. Change the udf to Function wsl(Optional rng As Range) As Variant Dim v As Variant, i As Long, n As Long If rng Is Nothing Then Set rng = ActiveCell With rng.Parent.Parent ReDim v(1 To .Worksheets.Count, 1 To 1) For i = 1 To .Worksheets.Count v(i, 1) = .Worksheets(i).Name Next i End With wsl = v End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing worksheets from a list | Excel Discussion (Misc queries) | |||
List of worksheets | New Users to Excel | |||
Get a list of worksheets from a WB | Excel Discussion (Misc queries) | |||
Get a List of Worksheets | Excel Programming | |||
VBA list all worksheets | Excel Programming |