Results 1 to 5 of 5

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
    68,095

    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,330

    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

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