Worksheet names in VBE

Question

I've noticed that my workbook has two different names for each component in the VBE. What is the difference between name1 and name2? Which one should I refer to, so I will be sure my macro will work?

enter image description here

possible duplicate of [Excel tab sheet names vs. Visual Basic sheet names](http://stackoverflow.com/questions/2649844/excel-tab-sheet-names-vs-visual-basic-sheet-names) — Jean-François Corbett, Mar 25 '15 at 11:48
Also related: http://stackoverflow.com/questions/27169070/identifying-a-worksheet-other-than-by-its-name and quite a few more: http://stackoverflow.com/search?q=sheet+name+codename — Jean-François Corbett, Mar 25 '15 at 11:48

score 5 · Accepted Answer · edited May 23 '17 at 10:30

5

Control is the code name of the sheet, whereas Plan 1 is the tab name of the sheet. The latter can be easily changed by the user so it's safer to use the codename if you can - for example, referring to:

control.range("A1:A10")

rather than:

sheets("Plan 1").Range("A1:A10")

Note that you cannot use sheet codenames to refer to sheets in workbooks other than the one containing the code unless you set a reference to the project of that workbook, or use a function that loops through each sheet in the other workbook testing the codename property of each.

edited May 23 '17 at 10:30

Community

1
1

answered Mar 25 '15 at 11:14

Rory

32,730
5
32
35

good shout on the further info about referencing in the same project. – SierraOscar Mar 25 '15 at 11:19

score 2 · Answer 2 · answered Mar 25 '15 at 11:15

"Plan1" is the tab name, which is what appears on the tab at the bottom of the worksheet.

"Control" is the code name, which can be used in VBA to reference that particular worksheet object directly.

Sheets("Plan1").Cells(1, 1).Value and Control.Cells(1, 1).Value will produce the same output.

score 0 · Answer 3 · answered Dec 22 '16 at 22:06

Each document-type vbComponent in the VBE has a Name and a CodeName:

Name is the name that is visible on the sheet tab in the Excel UI.
CodeName is the name that the sheet object can be referred to in your VBA.

Example:

Sub Names()

  Debug.Print Control.Name              'Prints "Plan 1"
  Debug.Print Control.CodeName          'Prints "Control"

  'This approach uses the sheet name in a call to `Sheets`,
  ' which will break if a user changes the name of the sheet
  'The sheets collection returns an object, so you don't get
  ' Intellisense, or compile-time error checking
  Debug.Print Sheets("Plan 1").Name     'Prints "Plan 1"
  Debug.Print Sheets("Plan 1").CodeName 'Prints "Control"

End Sub

bhbp.bg · Answer 4 · 2022-11-06T19:11:22.953

0

Sub example()
'Assume we have Workbook with 2 worksheets.
'Sheet1 have (Name) Sheet_TITLE(1) and Name TITLE1
'Sheet2 have (Name) Sheet_TITLE2) and Name TITLE2
'Name is equal to Caption of command button
'The coderows below have a similar effect - activates Sheet2
Sheets(2).Activate
'OR
Sheet_TITLE2.Activate
'OR
Sheets("Title2").Activate
End Sub
Sub msg_box()
MsgBox Sheet_TITLE2.CodeName 
End Sub

edited Nov 06 '22 at 19:11

answered Nov 06 '22 at 19:06

bhbp.bg

1
3

Worksheet names in VBE

4 Answers4