Worksheet name anomalies

[scrawnyguns]

Can someone please expain...
I have a form that, when loaded, initialises a "Worksheet" variable to
point to a certain worksheet...
In the general declarations section:
Dim DELSHEET As Worksheet

In the UserForm_Activate sub:
Set DELSHEET = Worksheets("DEL NOTE")

The "DEL NOTE" worksheet is Sheet4 also.

Throughout different subs in the form I reference the DELSHEET with no
worries. The problem is when I need to reference a textbox on the Del
Note worksheet. In VBA code when I type "DELSHEET" + "." I get the
usual list of properties/methods for the worksheet. However, when I use
the sheet's excel name: "Sheet4" + "." I get the usual list plus the
textboxes and objects that I added in that sheet with the control
toolbox (which is what I want).

How come it works for one and not the other? I thought they would be
the same?

I would prefer to use the DELSHEET variable name rather than Sheet4 as
sheets can get moved around. When I deliberately type:
DELSHEET.TextBox1.Text = "something"
I get a method/data member error... so that doesn't work.

-Scrawny.

 

[keepITcool]

You misinterpret the benefits of usgin CODEnames.
Users can rename sheets and move but they cannot rename the
CODEnames of the sheet without going into VBE.

it is good practice to reference the sheets you''re working with
via their CODEname. and dispense with the wks object variables.
or qualifiers like Thisworkbook.worksheets("My Fourth Sheet")

e.g.
sheet4.Range(x)

ofcourse for code clarity you could set the CODEname
to s't more descriptive.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


(e-mail address removed) wrote in

 

[scrawnyguns]

Awww..... OFCOURSE!!! Why didn't I think of that earlier... stupid,
stupid, stupid.
It's all so simple! Just rename the sheets in the properties window.

Thanks m8.

Cheerio!

)