Excel tab sheet names vs. Visual Basic sheet names

Question

It seems that Visual Basic can not reference sheets according to user-modified sheet names. The worksheet tabs can have their names changed, but it seems that Visual Basic still thinks of the worksheet names as Sheet1, etc., despite the workbook tab having been changed to something useful.

I have this:

TABname = rng.Worksheet.Name  ' Excel sheet TAB name, not VSB Sheetx name.

but I would like to use sheet names in Visual Basic routines. The best I could come up so far is to Select Case the Worksheet Tab vs. Visual Basic names, which doesn't make my day. Visual Basic must know the Sheet1, Sheet2, etc., names. How can I get these associated with the Excel tab names so that I don't have to maintain a look-up table which changes with each new sheet or sheet tab re-naming? Thanks in advance for your replies.

Answer 1

You should be able to reference sheets by the user-supplied name. Are you sure your referencing the correct Workbook? If you have more than one workbook open at the time you refer to a sheet, that could definitely cause the problem.

If this is the problem, using ActiveWorkbook (the currently active workbook) or ThisWorkbook (the workbook that contains the macro) should solve it.

For example,

Set someSheet = ActiveWorkbook.Sheets("Custom Sheet")

Answer 2

There are (at least) two different ways to get to theWorksheet object

• via the Sheets or Worksheets collections as referenced by DanM
• by unqualified object names

When a new workbook with three worksheets is created there will exist four objects which you can access via unqualified names: ThisWorkbook; Sheet1; Sheet2; Sheet3. This lets you write things like this:

Sheet1.Range("A1").Value = "foo"

Although this may seem like a useful shortcut, the problem comes when the worksheets are renamed. The unqualified object name remains as Sheet1 even if the worksheet is renamed to something totally different.

There is some logic to this because:

• worksheet names don't conform to the same rules as variable names
• you might accidentally mask an existing variable

For example (tested in Excel 2003), create a new Workbook with three worksheets. Create two modules. In one module declare this:

Public Sheet4 As Integer

In the other module put:

Sub main()

Sheet4 = 4

MsgBox Sheet4

End Sub

Run this and the message box should appear correctly.

Now add a fourth worksheet to the workbook which will create a Sheet4 object. Try running main again and this time you will get an "Object does not support this property or method" error

Answer 3

I have had to resort to this, but this has issues with upkeep. Function sheet_match(rng As Range) As String ' Converts Excel TAB names to the required VSB Sheetx names. TABname = rng.Worksheet.Name ' Excel sheet TAB name, not VSB Sheetx name. Thanks, Bill Gates. ' Next, match this Excel sheet TAB name to the VSB Sheetx name: Select Case TABname 'sheet_match Case Is = "Sheet1": sheet_match = "Sheet1" ' You supply these relationships Case Is = "Sheet2": sheet_match = "Sheet2" Case Is = "TABnamed": sheet_match = "Sheet3" 'Re-named TAB Case Is = "Sheet4": sheet_match = "Sheet4" Case Is = "Sheet5": sheet_match = "Sheet5" Case Is = "Sheet6": sheet_match = "Sheet6" Case Is = "Sheet7": sheet_match = "Sheet7" Case Is = "Sheet8": sheet_match = "Sheet8" End Select End Function

Answer 4

In the Excel object model a Worksheet has 2 different name properties:

Worksheet.Name

Worksheet.CodeName

the Name property is read/write and contains the name that appears on the sheet tab. It is user and VBA changeable

the CodeName property is read-only

You can reference a particular sheet as Worksheets("Fred").Range("A1") where Fred is the .Name property or as Sheet1.Range("A1") where Sheet1 is the codename of the worksheet.

Answer 5

Actually "Sheet1" object / code name can be changed. In VBA, click on Sheet1 in Excel Objects list. In the properties window, you can change Sheet1 to say rng.

Then you can reference rng as a global object without having to create a variable first. So debug.print rng.name works just fine. No more Worksheets("rng").name.

Unlike the tab, the object name has same restrictions as other variables (i.e. no spaces).