Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    vba to rename Tabs based on sheet codename

    Hi

    I have a number of worksheets.
    I want to set the displayed Tab names, based on a selection choice (1 to 5)
    I know the sheet codenames for the relevant Tabs.
    I have an array zBlock containing the sheet codename, and the corresponding required names for each choice.
    Some of the sheets may be hidden or not.

    So, starting with something like..
    zChoice = 3
    zBlock = [namesData]
    j = Ubound(zBlock] 'number of sheets

    ..how do I loop through and change the Tab names???

    zeddy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure of your setup, exactly. You can loop through the zBlock, the 2D array containing the codenames and new names.

    You can loop like (warning aircode):
    from j = lbound(zblock,1) to ubound(zblock,1)
    worksheets(zblock(j,1)).name = zblock(j,2)
    next

    to go through the "rows" [I presume that zblock is x rows and 2 columns, if you used 2 rows by x columns, then use l and ubound (zblock,2) and change the j,1/j,2 to 1,j and 2,j]

    Steve
    PS I am not sure how the "choice" enters into the array are these additional dimensions so that the line should be:
    worksheets(zblock(j,1)).name = zblock(j,zChoice)
    or
    worksheets(zblock(j,1)).name = zblock(j,zChoice+1)
    depending on how it is setup...

    PPS if the array is zerobased you may need:
    worksheets(zblock(j,0)).name = ...
    Last edited by sdckapr; 2013-08-17 at 23:40.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Steve

    Many thanks for your reply.
    It has helped me sort the routine out.

    zblock(j,1) will return the codename for the sheet, e.g. "shtXXData"
    zblock(j,zChoice+1) will return the chosen value for setting the new Tab name e.g. "Report"

    So your vba line..
    worksheets(zblock(j,1)).name = zblock(j,zChoice+1)
    ..could be interpreted as..
    worksheets("shtXXData").name = "Report"

    ..but you can't rename a sheet like that, based on the codename of a sheet.

    So I had to create a Function to return the current tabsheet name of a specified codesheet name:

    Code:
    Function getTabNameFromCodename(zCodename)
    
    For Each zSht In ThisWorkbook.Worksheets
    If StrComp(zSht.CodeName, zCodename, vbTextCompare) = 0 Then
    getTabNameFromCodename = zSht.Name
    Exit Function
    End If
    Next
    
    End Function
    So now, I could use it like this..
    Code:
    zCodename = zblock(i, 1)                            'e.g. "shtXXXXXXX"
    zTabSht = getTabNameFromCodename(zCodename)         'e.g. "what current tab is"
    zNewTabName = zblock(i, zChoice + 1)                'e.g. "what it should be"
    Worksheets(zTabSht).Name = zNewTabName              'reset Tab name to required name

    zeddy

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am glad you got it worked out. I haven't had the need to work a lot with codenames and did not realize you could not use the codename directly to get a worksheet object (but from the worksheet object you could get a codename). An option without the function that may also work would be to loop through all the worksheets (instead of looping through the zBlock elements, get the codename of each and do a vlookup in the zblock array to find the new name from the lookedup codename. If the codename was not found you would not change the worksheet name.

    Steve

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    I'm a little late to the party as it took me a while to work this out but maybe this will help someone else understand it in a working example.
    Code:
    Option Explicit
    
    Sub TabsRename()
    
       Dim zMySheetNames(1 To 3, 0 To 3) As String
       Dim iLangSelection                As Integer
       Dim iShtCntr                      As Integer
       Dim objShtCodeNames(1 To 3)       As Object
       
       Set objShtCodeNames(1) = Sheet1    'Note use sheet CODE names here!
       Set objShtCodeNames(2) = Sheet2
       Set objShtCodeNames(3) = Sheet3
      
       
       zMySheetNames(1, 1) = "EngSht1": zMySheetNames(1, 2) = "EngSht2": zMySheetNames(1, 3) = "EngSht3"
       zMySheetNames(2, 1) = "FrSht1": zMySheetNames(2, 2) = "FrSht2": zMySheetNames(2, 3) = "FrSht3"
       zMySheetNames(3, 1) = "GrSht1": zMySheetNames(3, 2) = "GrSht2": zMySheetNames(3, 3) = "GrSht3"
       
       iLangSelection = Val(InputBox("Enter a Language 1-3", "Test Selection:"))
       
       For iShtCntr = 1 To UBound(zMySheetNames)
              objShtCodeNames(iShtCntr).Name = zMySheetNames(iLangSelection, iShtCntr)
       Next iShtCntr
       
    End Sub
    Use Alt+F8 & run the TabsReName macro.
    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    many thanks for your example.
    I posted an example workbook under a new thread, showing how to use the renaming of Tabs.
    Hope you take a look at it, as I value your opinion.

    zeddy

Posting Permissions

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