Worksheet Name & CodeName
In Excel, the worksheets will have two names. One is like its caption & other is object Name. In the VB editor, in project explorer (Ctrl+D), both these names will be displayed.
For example: If a sheet has name “Settings” in Excel, the VB editor will show it as Sheet1 (Settings).
Here Sheet1 is called code name. Settings is sheet name.
Code name is a read only property. Its name can only be modified manually, by right clicking on the sheet1 and click on properties. Then enter new name in the field “(Name)”.
In the following macro we will learn how to read both the names using a VBA code.
VBA Code – Worksheet Name & Codename
Here is the code to get codename or sheet object name with VBA.
Sub sheetObjectName() 'Define Dim i As Double Dim sh As Worksheet 'Loop each Sheet For Each sh In ThisWorkbook.Sheets i = i + 1 Sheets("Sheet1").Range("A" & i) = sh.Name Sheets("Sheet1").Range("B" & i) = sh.CodeName Next End Sub
Remember only the sheet name can be changed from a macro, code name cannot be changed.
Here is an article that discussed topic about changing Worksheet codename.