Accessing Excel Sheet

[Bill]

Hello,
When I look under the Excel Objects in the VBA Project window, the
worksheets are listed are listed by sheet number with a name, e.g.,
Sheet1(Sheet1) or Sheet1(Bill) if I have named the first sheet Bill.

How can I select the sheet based on the Sheet1, not the name of the
sheet? Or can I? I want to be able to select the sheet even if the
name is changed by a user.

Thanks,

Bill

 

[Die_Another_Day]

You can always grab the sheet by the index. Sheets(1) But this can be
changed by the user as well.

Charles

 

[Guest]

The first part is the actual object and the second part the Tab name is just
a property of the object. The long and the short of it is it is a whole lot
easier to refer to the object than it is to refer to the object indirectly by
its tab name property. Try this and notice that intellisence (drop down list)
works when you type it in

Sheet1.Select

You can change the Sheet1 to something more meaningful by editing it in the
properties (View -> Properties if the window is not open). The first item is
(Name). When you change this you change the name of the object. So Sheet1 can
become something more meaningful like shtSourceDataSheet

 

[Mike Fogleman]

Sheet1.Activate
Sheet2.Activate

The name in parentheses is the text name that appears on the sheet tab.
Worksheets("Bill").Activate

Worksheets(1).Activate will address the first worksheet in the workbook
starting at the left. So, if Sheet15 was the first sheet tab, it would be
Activated.

Mike F

 

[Bill]

Thanks a lot.
If I want to know the object of a sheet, it appears I can use:

sheets("Bill").codename

Is that correct?

Bill

 

[Guest]

That will return something like "Sheet1" which is the codename of the sheet
unless you went through the properties and changed the (Name) of the sheet.
Pretty much as soon as I start any new Excel/VBA project I go through and
rename the CodeNames of all of the sheets. I alway prefix the sheet names
with "sht". So when you look through my code it is full of
shtMySheet.Range("A1").???
This makes the code very compact legible and bullet proof...