Results 1 to 5 of 5
  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 set worksheet Tabs based on Language

    Hi All

    Based on an issue that Steve helped me to resolve, I have attached a file demonstrating how to have Excel rename Tab sheets according to a table of values, and a choice made by the user.
    This makes use of sheet codenames.

    Sheet codenames can be defined and used in vba to move about between sheets in an Excel Workbook.
    The advantage of using sheet codenames in vba code is that it doesn't matter if someone 'renames' a worksheet tab.
    Usually, if someone 'renames' as worksheet tab, the code will not work.
    For example, if your code says..
    Worksheet("Data").Select
    ..and the user has 'renamed' the sheet to "Input", then the previous line will fail.

    So you can assign a codename to the worksheet (via the Project Explorer to select the sheet, and the Properties window to set the 'internal' name).
    If you select the sheet with tab name "Data" in the vbe, and assign the codename as, for example, shtData, then you can use this in your vba code:
    shtData.Select
    ..and it won't matter what the actual Tab name is.

    So the advantage of using codenames for the sheets in your vba is you can write vba routines which will work regardless of what the displayed Tab names are.
    If it doesn't matter what the displayed tab names are, then maybe we can change them to be whatever we want.

    My example file uses Language choice for this demo.
    Clicking a Flag sets the user choice (a number from 1 to 10 in this example).

    So this attached demo file shows how we can change tab names to be displayed in
    English, French, Polish, German, Spanish, Arabic, Dutch, Russian, Afrikaans, Chinese etc etc.
    ..just by clicking on a corresponding Flag.

    I hope someone may find this useful.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2013-08-18 at 08:13. Reason: typo

  2. #2
    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
    Zeddy,

    Cool! Love the flags real nice touch.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Until I did this, I'd never seen Arabic, Chinese or Russian tab names!

    zeddy

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Purely FYI, if you have trusted access to the VBA Project set in the macro security/Trust Center, you can use the more direct approach:
    Code:
    Function getTabNameFromCodename(zCodename)
       getTabNameFromCodename = ThisWorkbook.VBProject.VBComponents(zCodename).Properties("Name")
    End Function
    although for distribution purposes, your loop is safer.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Thanks for the info on another method of getting the current Tab name based on the vba codename for the worksheet.

    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
  •