Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro question
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??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro question
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 -- Gary''s Student - gsnu200840 "Oligo" wrote: 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??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro question
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?" Oligo wrote: 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??? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro question
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 On Sat, 21 Mar 2009 08:03:01 -0700, Oligo wrote: 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??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro question
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! Oligo wrote: 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??? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
Macro Question | Excel Worksheet Functions | |||
Macro Question | Excel Worksheet Functions | |||
Macro Question | Excel Worksheet Functions | |||
Macro Question? | Excel Discussion (Misc queries) |