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

Urgently need VB Help - Declaring "Global" Variable?
 

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" wrote in message
...
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