macro question

[Oligo]

if my macro on sheet 2 has a formula reference with sheet 1 cell, when i
change the name of sheet 1, will the macro work and change itself
accordingly???

 

[Gary''s Student]

It depends on how your ae referring to the Sheet.

x=Sheets("Sheet1").Range("A1").Value will not change
x=Sheets(1).Range("A1").Value will change

 

[Dave Peterson]

It depends on how you refer to that worksheet.

If you use the name of the sheet that you see on the tab, then nope.

Worksheets("Accounting Info").range("a1").value = "hi there"
will break.

But each sheet has its own code name--you can see it in the VBE when you show
the project explorer (and expand the branches).

With my example name, you'd see something like:
Sheet1(Accounting Info)

The name in ()'s is what the user sees in the sheet tab.

The name in front of this is called the codename. You can change this to a nice
mnemonically significant name and use it in your code. Although this codename
can be changed, most users wouldn't know how.

Open the VBE (alt-F11)
Open the project Explorer (ctrl-r)
Expand the project
Select the object under: Microsoft Excel Objects
that represents your worksheet.

Hit F4 to show the properties window.
In the (Name) box, give it a nice name -- like:
AcountingInfo

Then you can use something like this in your code:
AccountingInfo.range("a1").value = "hi there"

You don't have to rename the codename, but things like this can get very
confusing:

Sheet1.range("a1").value = "hi there"
Sheet2.range("a1").value = "bye there"
Sheet3.range("a1").value = "welcome back"
Sheet4.range("a1").value = "gone again?"

 

[Gord Dibben]

Depends.

If you reference the codename of the sheet, the name of the sheet does not
matter.

Sheets(1) will always refer to that sheet.

Sub test()
Sheets(1).Range("A1:A10").Interior.ColorIndex = 3
End Sub

Sheets("Sheet1") is hard-coded and will fail if you change the name.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Just to add to GS and Gord's responses...

If you use worksheets(1), then you're using the leftmost sheet. You can change
the name and it'll still work. But if you change the position, then the code
may work--but it may not do what you want to the correct sheet!