Urgently need VB Help - Declaring "Global" Variable?

I have an active x Control in File A ... when you click on it it runs the
following "Sheet1" Excel Object Macro:

Option Explicit
Public MyMacroType As String


Public Sub StartCleanMRICkAccount_Click() Dim MyStartFile


MyStartFile = InputBox("What is the name of the file to run macro on? IE
Book1")
Windows(MyStartFile & ".xls").Activate

MyMacroType = "AddAccountNumbers"
Application.Run "CleanMRIExport"

Application.Calculation = xlCalculationAutomatic End Sub

As you see the above macro sets the value for the variable MyMacroType....
Then run the Maco "CleanMRIExport" which is located in the same file but in
Module11. The last step in the "CleanMRIExport" is to run a second Macro
called "addformula" the last step in this maco is below:

If MyMacroType = "NoAccountNumbers" Then

Application.Calculation = xlCalculationAutomatic


MsgBox "Report Completed"

Exit Sub
Else

Application.Run "AddAccountingNumbers"

End If


Every time I run all of this when I get to the above code my variable
"MyMacroType" is always empty. I have tried running it with the:

Option Explicit
Public MyMacroType As String

In just the "Sheet1" Excel Object and I have tried running it with it also
at the top of Module11 but still can keep the variable to hold the necessary
value.

PS: there are actually 2 different Active X controls macros that starts the
ride one sets the MyMacroType to "NoAccountNumbers" and the other Sets it to
"Add Accounting Numbers"

Am I trying to do the impossible or do I just need to make each Active X
control run different macro's when the first 50 steps are the exact same?

Sorry for such a long write up....
April

April,

If you have a Public variable declared in a Worksheet module,
it will only be available to code within that module, unless it is
called using the module name as the variable prefix...
Sheet11.MyMacroType

The easiest way may be to declare the variable in a regular/
general module. It will be available to all modules without the
need for the module name prefix


Regards,
Jim Cone
San Francisco, USA



"April" <[email protected]> wrote in message
news:[email protected]...
> I have an active x Control in File A ... when you click on it it runs the
> following "Sheet1" Excel Object Macro:
>
> Option Explicit
> Public MyMacroType As String
> Public Sub StartCleanMRICkAccount_Click() Dim MyStartFile
> MyStartFile = InputBox("What is the name of the file to run macro on? IE
> Book1")
> Windows(MyStartFile & ".xls").Activate
> MyMacroType = "AddAccountNumbers"
> Application.Run "CleanMRIExport"
> Application.Calculation = xlCalculationAutomatic End Sub

> As you see the above macro sets the value for the variable MyMacroType....
> Then run the Maco "CleanMRIExport" which is located in the same file but in
> Module11. The last step in the "CleanMRIExport" is to run a second Macro
> called "addformula" the last step in this maco is below:
> If MyMacroType = "NoAccountNumbers" Then
> Application.Calculation = xlCalculationAutomatic
> MsgBox "Report Completed"
> Exit Sub
> Else
> Application.Run "AddAccountingNumbers"
> End If

> Every time I run all of this when I get to the above code my variable
> "MyMacroType" is always empty. I have tried running it with the:
>
> Option Explicit
> Public MyMacroType As String
> In just the "Sheet1" Excel Object and I have tried running it with it also
> at the top of Module11 but still can keep the variable to hold the necessary
> value.
> PS: there are actually 2 different Active X controls macros that starts the
> ride one sets the MyMacroType to "NoAccountNumbers" and the other Sets it to
> "Add Accounting Numbers"
> Am I trying to do the impossible or do I just need to make each Active X
> control run different macro's when the first 50 steps are the exact same?
> Sorry for such a long write up....
> April