Define global worksheet name for a userform

**zbor** · 05-22-2012, 04:06 AM

I use worksheet name on several places, and I want to have it only on one place...

I've tried:

Please Login or Register  to view this content.

But it give me

Compile error:
Invalid outside procedure

on Set part of the code...

What am I missing...
How can I define Worksheet name only on one place for whole Userform?

**jraj1106** · 05-22-2012, 04:13 AM

Hi

Try like this:

Please Login or Register  to view this content.

**watersev** · 05-22-2012, 04:20 AM

hi zbor, if that concerns a userform, you can use

Please Login or Register  to view this content.

in userform initializing code that fires first and your public variable will hold sheet object for the use by other private subroutines within userform code level

**zbor** · 05-22-2012, 04:27 AM

thx watersev.. I've tried that first time but somehow failed...

jraj1106, that's something I could use .. It's working and I can call that macro whenver I need that (I've also defined some other things within a Sub)

**snb** · 05-22-2012, 05:15 AM

In the userform module:

Please Login or Register  to view this content.

But I think it's hardly worth while to use a variable that doesn't vary.
I'd prefer to refer to the sheet in the code using With ... End With

Please Login or Register  to view this content.

**zbor** · 05-22-2012, 07:15 AM

Mine case was that Worksheets("TEST") appeared in several private Subs of the same userform so I wanted to define it globaly (for that userform).

Will defining sheet just in userform_Initialize affect only that Sub or all (like for example Private Sub CommandButton3_Click() )?

**snb** · 05-22-2012, 09:45 AM

Will defining sheet just in userform_Initialize affect only that Sub or all (like for example Private Sub CommandButton3_Click() )?

It will take effect for the whole userform and all procedures/functions it contains.

Make a distinction between the scope of the Variable (in this case 'Private') which will suffice for all the code in the 'private' userform, and the point where a value will be attributed to that variable. The scope of the variable is crucial for it's scope, not the point of attributing.