Sheet1(MyTab)

[Desert Piranha]

Hi guys and ladies,

In the VBE when you look at the worksheet names on the left pane. There is the Sheet Number followed by the tab name. --IE:Sheet1(MyTab)-- You can, in a macro use either of these to refer to a worksheet.
So if i have a macro where i want a reference to not change even if the tab is renamed i want to use the "Sheet1" reference.

Worksheets("Sheet1").Select
Worksheets("MyTab").Select

In these two examples the first doesn't work. The second does. Would you explain a little about this? (why, how to, etc)

Thx

[Jacob Hilderbrand]

The Code Name can be used, but there are no quotes and you don't use Worksheets or Sheets to refer to it.

So for example, just use this.

Sheet1.Select

[MWE]

Hi guys and ladies,

In the VBE when you look at the worksheet names on the left pane. There is the Sheet Number followed by the tab name. --IE:Sheet1(MyTab)-- You can, in a macro use either of these to refer to a worksheet.
So if i have a macro where i want a reference to not change even if the tab is renamed i want to use the "Sheet1" reference.

Worksheets("Sheet1").Select
Worksheets("MyTab").Select

In these two examples the first doesn't work. The second does. Would you explain a little about this? (why, how to, etc)

Thx

I do not think that you can reference a sheet by its "name" in the VBE. You can reference a sheet using either its index or its name. The index is simply the # assigned to it based on the order of tabs, left to right. So the leftmost tab is 1, the next is 2, etc. Thus if the leftmost tab is named "MyTab" then you could reference it in two ways:

Worksheets(1).Select
 Worksheets("MyTab").Select

The indices for sheets will change if you reorder the sheets (tabs). It is not wise to track or refer to a sheet by its index unless you really do not care which sheet it is. For example

For I = 1 to Worksheets.Count
Msgbox Worksheet(I).Name
Next I

would display the name of each sheet. The order is whatever the order of the tabs is. The order of sheets displayed using

Dim wk as Worksheet
For each wk in Worksheets
Msgbox wk.Name
Next wk

is the same, i.e., the order of the tabs.

Part of the confusion stems from the fact that initially the true name of the sheet (as viewed by the user in the lower tab and the name by which the sheet is known in VBA) and the "name" of the sheet in the VBE are the same, e.g., "Sheet1".

The "name" for a sheet in the VBE (your "Sheet1" example) can be changed. Display the properties window in the VBE and click on the relevant sheet. The Name property can then be changed.

Confusing?

EDIT: and then DRJ says you CAN refer to a sheet by its "VBE name" and I feel like a jerk !

[Bob Phillips]

I do not think that you can reference a sheet by its "name" in the VBE.

That is its codename and is exposed to VBA so is useable.

[TonyJollans]

To clarify, or muddy the waters (delete whichever is not applicable) ...

The CodeName is an object reference - use it just as if you had declared it something like this:

Set Sheet1CodeName = Sheets("Sheet1TabName")

It can be changed as MWE says via the VBE, but not by code so it gives a guaranteed reference to the sheet in question. It's the equivalent, for a sheet, to ThisWorkbook (which can also be renamed via the VBE properties box) for a workbook.

[johnske]

Hi Piranha,

Perhaps this'll help you? > http://www.vbaexpress.com/forum/arti...ticle&artid=35

HTH,
John

[Bob Phillips]

It can be changed as MWE says via the VBE, but not by code so it gives a guaranteed reference to the sheet in question.

ThisWorkbook.VBProject.VBComponents("Sheet3").Properties("_CodeName").Value = "NewCodeName"

[geekgirlau]

It's a good idea to use the code name, however make it something relevant!

shRawData.select

rather than

Sheet1.Select

[TonyJollans]

[VBA]
ThisWorkbook.VBProject.VBComponents("Sheet3").Properties("_CodeName").Value = "NewCodeName"
[/VBA]

Alright, it can be done I had forgotten that. Thanks.

I'm not about to explain how that affects usage, and in which procedures. I'll let Piranha come to that in due course.

[Desert Piranha]

Hi Piranha,

Perhaps this'll help you? > http://www.vbaexpress.com/forum/arti...ticle&artid=35

HTH,
John

Hi, DRJ, MWE, xld,TonyJollans, geekgirkau, johnske,
Thank you all for you input. I got it now. You all are great.

johnske - Great link - thx