Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
Ahhhhh! I see! Thank you very much. I'm sure that will comein quit
handy. - Piku -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
But how do you use that designator in a certain workbook? i.e
"Workbooks(1).Sheet1" doesn't work. Nor doe "Workbooks.Sheest(Sheet1)" or "Workbooks.Sheets.Sheet1" Thanks in advance for clearing this up. - Piku -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
No, you don't need to set a worksheet object as you did in your example.
When you want to refer to anything on the sheet, such as a range, you just use something like Sheet1.Range("A1") Personally, I always change my codename properties (you change the name property in the properties window to have a meaningful name, such as shData, or shSummary. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pikus " wrote in message ... But how do you use that designator in a certain workbook? i.e. "Workbooks(1).Sheet1" doesn't work. Nor does "Workbooks.Sheest(Sheet1)" or "Workbooks.Sheets.Sheet1" Thanks in advance for clearing this up. - Pikus --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Name Confusion
Bob,
I'm still not clear on how you specify which workbook Sheet1 is in, as per pikus's previous post. So, if my code is in Book1, how do I specify Sheet1 in Book2? Doug "Bob Phillips" wrote in message ... No, you don't need to set a worksheet object as you did in your example. When you want to refer to anything on the sheet, such as a range, you just use something like Sheet1.Range("A1") Personally, I always change my codename properties (you change the name property in the properties window to have a meaningful name, such as shData, or shSummary. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pikus " wrote in message ... But how do you use that designator in a certain workbook? i.e. "Workbooks(1).Sheet1" doesn't work. Nor does "Workbooks.Sheest(Sheet1)" or "Workbooks.Sheets.Sheet1" Thanks in advance for clearing this up. - Pikus --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort confusion | Excel Discussion (Misc queries) | |||
If Formula confusion | Excel Discussion (Misc queries) | |||
MDI Confusion | Excel Discussion (Misc queries) | |||
COUNTA Confusion.... | Excel Discussion (Misc queries) | |||
Confusion..... | Excel Discussion (Misc queries) |