Get Sheet Object Caption and Codename

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.

External Reference: 
Here is an article that discussed topic about changing Worksheet codename.