how to understand the three names relative to one worksheet

  • Thread starter Thread starter clara
  • Start date Start date
C

clara

Hi all,

I think there are at least three names relative to one worksheet, in Project
window, you can find something like sheet1(My First), sheet1 and My First are
names of the worksheet, from the top left dropdown on the code window, you
can find "worksheet", what is the difference and can they be interchangable?
on the dropdown you can also find "General", could you tell me how is the VBA
organize the code structure?

Clara
 
One point of confusion you may have is that your workbook is an object, there
is an array of sheets within, each with an index. The 'Name' associated is
something to make it easier to remember. You can call a sheet by either its
name or its index, in your example
sheets("My First")
that calls it by name
sheets(1)
calls it by index, sheets1 is not a name, just an index.
The dropdown just refers to what the code will use when firing events, on
the sheets it is worksheet, so all of the events associated with worksheets
are at you disposal and only work for that worksheet. Under ThisWorkbook the
selection changes to Workbook, this lets you select events that will affect
the entire workbook. General is where you place code that will not be fired
by the sheet or workbook events such as a button click event.
If you place a button on sheet 1, then go to the Sheet 1 in the project
screen, the dropdown will now include Commandbutton1 on the left and then a
list of all the events on the right such as the click event. Hope that helps.
 
Sheet1 is the code name of the sheet. You can change this in the VBE
properties. The user has no ability to access or change this and that is one
good reason why you should be using this in your code. Another reason is that
it directly references the sheet object. In the code of a sub if you type in
Sheet1. you will get the intellisence drop down showing all of the properties
(finger pointing at page) and methods (Green button) of the sheet object.

My first is the tab name of the sheet. Obvioulsy the user can change this
and if they do your code will crash. In code if you type in Sheets("My
First") you are indirectly referencing the object by it's tab name. Note if
you type the dot you will not get an intellisence drop down (there are
reasons for this including it could be a worksheet or a chart sheet). A
recorded macro uses this which is why you see so much code out there using
this method. It is not a good method to use.

So in short start using the code name in your programming as it has huge
advantages over the tab name.

In the code window you can select Worksheet as you suggest. This is a way to
access the events of the worksheet object. When you select that you can view
the events in the drop down just to the right and the VBE will insert code
stubs when you choose these events. If you add a button or such from the
control toolbox to a sheet you could also select if from the drop down on the
left and get it's associated events.
 
Sheet1 is the code name of the worksheet object. It has nothing to do with
the index of the worksheet in the worksheets collection.

Sheets(1) and Sheet1 do not necessarily have anything to do with each other.
Sheets(1) is the first item in the Worksheets collection. Sheet1 is a direct
reference to the worksheet with the code name Sheet1. Sheet1 may or may not
be the first item in the Worksheets collection.

Just to clarify your last assertion about adding a button to the sheet. That
only applys if you use a button from the control toolbox (ActiveX controls).
If you add a button from the forms toobar there is no associated event with
the control.
 
Hello Jim,

Thank you very much for your reply that help me very much! I stiil have some
questions: is there an existing module here to contain the sheet object, the
control and the General together;can we say the sheet object contains all the
controls on the sheet?

Clara
 
The XL object model is a heirarchy. The application contains 0 or more
workbooks. A Workbook contains 1 or more sheets. A worksheet contains ranges.
A range can not span more than 1 worksheet any more than a worksheet can span
more than one workbook. Workbooks and Worksheets are collections of workbook
or worksheet objects. A range is a collection of one or more cells.

Each of these objects (with the exception of the applications) is contained
in a parent object. The parent for a range is its worksheet. The parent of a
worksheet is it's workbook. In fact each of these objects has a parent
property which will return it's parent object.

ActiveX Command buttons are embedded in worksheets and so the worksheet
becomes the parent (or container) for the object.

As such there is no one single module where you can access everything. The
closest you get is the Thisworkbook module. Thisworkbook is the workbook
where the code is being run. Being that the workbook where the work is being
done you can drill down through the heirarchy to get at the objects below.
 
Back
Top