I have a workbook which I have created on a single sheet which was
originally titled "Sheet1". I have since changed the name to "Data". I
then added a second sheet, which Excel has titled "Sheet1". As long as I
restrict myself to Excel, there is no confusion. But when I switch to the
Visual Basic Editor, it lists my sheets as Sheet1(Data) and Sheet2(Sheet1).

Apparently, there are two sets of names. Do I need to use one set when
programming in VBA, and the other for formulas in cells? What's going on,
and how does an experienced macro programmer deal with this situation?

George Borrmann

Hi George

If you use the code name(the first one) in your code
and protect your VBA code then people can't change this name.
They can only change the tab name

So If you use this
Sheet1.Range("a1").Value = 100
this code will always work

If you use this
Sheets("Sheet1").Range("a1").Value = 100

And you change the Tab name your don't work anymore



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"George B" wrote in message ...
I have a workbook which I have created on a single sheet which was
originally titled "Sheet1". I have since changed the name to "Data". I
then added a second sheet, which Excel has titled "Sheet1". As long as I
restrict myself to Excel, there is no confusion. But when I switch to the
Visual Basic Editor, it lists my sheets as Sheet1(Data) and Sheet2(Sheet1).

Apparently, there are two sets of names. Do I need to use one set when
programming in VBA, and the other for formulas in cells? What's going on,
and how does an experienced macro programmer deal with this situation?

George Borrmann

Create variables for the sheets to avoid the confusion.

Dim sheet1 As Worksheet
Dim data As Worksheet

Set sheet1 = Worksheets(“Sheet1”)
Set data = Worksheets(“Data”)

Data.Cells(1, 1).Value = “Hi there!”

- Pikus


---
Message posted from http://www.ExcelForum.com/

Hi

If you want to avoid errors when a user rename a sheet tab use the
code name like in my example Pikus

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"pikus " wrote in message ...
Create variables for the sheets to avoid the confusion.

Dim sheet1 As Worksheet
Dim data As Worksheet

Set sheet1 = Worksheets("Sheet1")
Set data = Worksheets("Data")

Data.Cells(1, 1).Value = "Hi there!"

- Pikus


---
Message posted from http://www.ExcelForum.com/

I understand your first post, but I don't see how that relates to whe
someone changes the names of the sheets and/or moves them around. Di
I miss something? - Piku

--
Message posted from http://www.ExcelForum.com

If your VBA code says
Set sheet1 = Worksheets("Sheet1")
and a user renames Sheet1 to say 'Summary', then your code is going to fail.

If you use the codename, this cannot happen as the user in Excel cannot
change the codename. Renaming a sheet has no effect upon the codename.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"pikus " wrote in message
...
I understand your first post, but I don't see how that relates to when
someone changes the names of the sheets and/or moves them around. Did
I miss something? - Pikus


---
Message posted from http://www.ExcelForum.com/