Change the Worksheet Object Name

michaeldh

Board Regular
Joined
Jun 11, 2002
Messages
201
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top