Set module-wide objects?
 

Is it possible to set a worksheet object the will be available to every
macro in a single module, versus having to re-set the same object in each
macro? I can declare the variable across the entire module, but the objects
seem to vanish ate each individual End Sub.

Ed

Set module-wide objects?
 

The first sub you run must set the variable to the worksheet. The code must
be in a general module, not in a worksheet or the thisworkbook module. At
the top of the module:

Public mySheet as Worksheet

Sub Macro1()
set mySheet = Worksheets(1)
End Sub

Sub Macro2()
if mysheet is nothing then macro1
msgbox mysheet.Name
End Sub

Sub Macro3()
if mysheet is nothing then macro1
msgbox mysheet.Range("A1").Value
End Sub

--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
Is it possible to set a worksheet object the will be available to every
macro in a single module, versus having to re-set the same object in each
macro? I can declare the variable across the entire module, but the
objects
seem to vanish ate each individual End Sub.

Ed

Set module-wide objects?
 

Tom: Would I be able to fire this from a Workbook_Open macro in
ThisWorkbook? Or would that destroy the object when it left that module?

Ed

"Tom Ogilvy" wrote in message
...
The first sub you run must set the variable to the worksheet. The code
must
be in a general module, not in a worksheet or the thisworkbook module. At
the top of the module:

Public mySheet as Worksheet

Sub Macro1()
set mySheet = Worksheets(1)
End Sub

Sub Macro2()
if mysheet is nothing then macro1
msgbox mysheet.Name
End Sub

Sub Macro3()
if mysheet is nothing then macro1
msgbox mysheet.Range("A1").Value
End Sub

--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
Is it possible to set a worksheet object the will be available to every
macro in a single module, versus having to re-set the same object in
each
macro? I can declare the variable across the entire module, but the
objects
seem to vanish ate each individual End Sub.

Ed

Set module-wide objects?
 

You should declare the public variable in a general module. You can set it
in the Workbook_Open Event.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
Tom: Would I be able to fire this from a Workbook_Open macro in
ThisWorkbook? Or would that destroy the object when it left that module?

Ed

"Tom Ogilvy" wrote in message
...
The first sub you run must set the variable to the worksheet. The code
must
be in a general module, not in a worksheet or the thisworkbook module.
At
the top of the module:

Public mySheet as Worksheet

Sub Macro1()
set mySheet = Worksheets(1)
End Sub

Sub Macro2()
if mysheet is nothing then macro1
msgbox mysheet.Name
End Sub

Sub Macro3()
if mysheet is nothing then macro1
msgbox mysheet.Range("A1").Value
End Sub

--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
Is it possible to set a worksheet object the will be available to
every
macro in a single module, versus having to re-set the same object in
each
macro? I can declare the variable across the entire module, but the
objects
seem to vanish ate each individual End Sub.

Ed

Set module-wide objects?
 

Thanks, Tom. I appreciate the boost.

Ed

"Tom Ogilvy" wrote in message
...
You should declare the public variable in a general module. You can set
it
in the Workbook_Open Event.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
Tom: Would I be able to fire this from a Workbook_Open macro in
ThisWorkbook? Or would that destroy the object when it left that
module?

Ed

"Tom Ogilvy" wrote in message
...
The first sub you run must set the variable to the worksheet. The
code
must
be in a general module, not in a worksheet or the thisworkbook module.
At
the top of the module:

Public mySheet as Worksheet

Sub Macro1()
set mySheet = Worksheets(1)
End Sub

Sub Macro2()
if mysheet is nothing then macro1
msgbox mysheet.Name
End Sub

Sub Macro3()
if mysheet is nothing then macro1
msgbox mysheet.Range("A1").Value
End Sub

--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
Is it possible to set a worksheet object the will be available to
every
macro in a single module, versus having to re-set the same object in
each
macro? I can declare the variable across the entire module, but the
objects
seem to vanish ate each individual End Sub.

Ed