Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By RoryA

Change the Worksheet Object Name

This is a discussion on Change the Worksheet Object Name within the Excel Questions forums, part of the Question Forums category; Hi Guys, Hoping you might be able to assist with this query...in the body of a spreadsheet a worksheet name ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    Australia
    Posts
    202

    Default Change the Worksheet Object Name

    Hi Guys,

    Hoping you might be able to assist with this query...in the body of a spreadsheet a worksheet name might be say Sheet1. When viewing the object in the VBA editor is might have a name of Sheet8. Is it possible to use macro code to rename the VBA editor name to agree to the sheet tab name.

    Look forward to reading your suggestions.

    Thanks.

    Michael.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092

    Default Re: Change the Worksheet Object Name

    No. The CodeName property is Read-only. From Help:

    CodeName Property

    Returns the code name for the object. Read-only String.

    Note The value that you see in the cell to the right of (Name) in the Properties window is the code name of the selected object. At design time, you can change the code name of an object by changing this value. You cannot programmatically change this property at run time.

  3. #3
    New Member _KaRaKuLa_'s Avatar
    Join Date
    Mar 2004
    Location
    TURKEY
    Posts
    13

    Default

    in VBA editor, select sheet name and press F4,
    change (NAME) case in Properties(F4) menu

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Australia
    Posts
    202

    Default Re: Change the Worksheet Object Name

    Cheers for that...had a feeling that was the case.

    Michael.

  5. #5
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329

    Default Re: Change the Worksheet Object Name

    Hi,

    How about:
    Code:
    Sub SheetNames()
        'Sheet1 renamed "Bob" in Excel
        
        MsgBox Worksheets("Bob").Range("A1").Value
        'using Excel sheet name
        
        MsgBox Sheet1.Range("A1").Value
        'using VBE object name / codename
        
        ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Bob"
        'change the codename for Sheet1 to Bob
    
    End Sub
    Richie

  6. #6
    Board Regular Rhodie72's Avatar
    Join Date
    Apr 2016
    Location
    England, in the cotswolds
    Posts
    283

    Default Re: Change the Worksheet Object Name

    Quote Originally Posted by Richie(UK) View Post
    Hi,

    How about:
    Code:
    Sub SheetNames()
        'Sheet1 renamed "Bob" in Excel
        
        MsgBox Worksheets("Bob").Range("A1").Value
        'using Excel sheet name
        
        MsgBox Sheet1.Range("A1").Value
        'using VBE object name / codename
        
        ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Bob"
        'change the codename for Sheet1 to Bob
    
    End Sub
    Even in your example Sheet1 remains Sheet1 with the "caption" changing. I am trying to dynamically change my
    Code:
    Sheet1.codename = "Sheet01"
    except it is as they say: Read Only. It can only be changed manually or with some really fancy deep coding that accesses the Operating System environment which is where the realms of VIRUS act... as we know that most virii are created as "proof of concept" where the situation seems impossible; then some loony tune changes the code top do malicious stuff and we update our anti-vrius program daily...

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    27,866

    Default Re: Change the Worksheet Object Name

    Richie's code works as written. It will not change the tab caption. As with all code that uses the VBProject, you must have trusted access to the VBA project in the Trust Center.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com