Results 1 to 7 of 7

Thread: Tab Names (XP)

  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab Names (XP)

    Hi All,

    I am trying to get a list of all the tabs in a workbook on a single sheet. I'm looking for a function(s) that

    a) tells me the number of tabs (including charts and any other types of tabs you can put in)
    [img]/forums/images/smilies/cool.gif[/img] gives me the name of the tab if I give it the tab index [0, 1, 2, ..., max of answer from a)]

    Do these functions exist in excel or do I need to build them?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tab Names (XP)

    The free ASAP Utilities include a tool to "Create a clickable index sheet. This will work kinda like a menu. Very handy when your dealing with large files with a lot of charts and sheets."

    It isalso possible to create your own custom functions:

    Function NumberOfSheets() As Long
    NumberOfSheets = ActiveWorkbook.Sheets.Count
    End Function

    Function NameOfSheet(i As Long) As String
    NameOfSheet = ActiveWorkbook.Sheets(i).Name
    End Function

    Use in a cell formula as
    =NumberOfSheets()
    =NameOfSheet(3)

    If you store the functions in your Personal.xls:
    =Personal.xls!NumberOfSheets()
    =Personal.xls!NameOfSheet(3)

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Tab Names (XP)

    Attached is something I use when I plan to use the INDIRECT function of on a bunch of sheets. It'll need a calling code such as:

    Sub ListSheetNames()
    If TypeName(Selection) <> "Range" Then Exit Sub
    SelectListSheetNameForm.Show
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Names (XP)

    thx Hans but ... bother bother bother ...

    ActiveWorkbook.Sheets(i).Name

    This gives me the name of the tabs in display order but given that I am changing the order of the tabs all the time (see <post#=467891>post 467891</post#>), how do I extract a list of sheet names that doesn't change order (al la the sheet names in the VBA project listing)
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tab Names (XP)

    You can create a sorted list:

    Sub ListSheets()
    Dim i As Long
    Dim n As Long
    n = ActiveWorkbook.Sheets.Count
    For i = 1 To n
    Range("A" & i) = ActiveWorkbook.Sheets(i).Name
    Next i
    Range("A" & 1 & ":A" & n).Sort Key1:=Range("A1")
    End Sub

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Tab Names (XP)

    In a quick test, I found that changing the VBA sheet code names won't change the print order. Feeding the sheet name array, in the desired order, to the Printout method doesn't work, correct? I think you'll have to develop something like:

    Sheets("Menu").Move Before:=Sheets(1)
    Sheets("Exhibit1").Move Before:=Sheets(2)

    to change the sheet order and corresponding print order.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Names (XP)

    Yup - that should work assuming that the required order of my tab names is alphabetical. Hmmm - I'm thinking that I might hard code a desired order and then re-sort the tabs after my print job.

    Summary: I want to be able to print the tabs in a user defined order, also with the option of excluding some tabs from the print job. When you select multiple tabs, they are printed in the order that they appear on screen. So, I've put in some code to move the tabs into my desired print order. The downside is that this also changes what is sheet(1), sheet(2), etc into that order.

    So, current proposed solution is ...

    1. <LI>before print, save current tab order
      <LI>rearrange tab order so that print order comes out correctly
      <LI>print
      <LI>rearrange tab order back to original
    I'll give this a whirl and see how it goes.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •