Creating a global variable for entire workbook

[johannes2008]

Hello All

What I have are a number of macros in "Sheet 1 (Data)" and then 2 macros in a userform. I have declared the variables used in the macros for the user form in "Sheet 1 (Data)" before any macro or sub, however it seems that the values given to those variables in the user form macros are not carried through when I use the same variable in "Sheet 1 (Data)".

Any thoughts on what I am doing wrong?

Thanks for the help in advance
-Johannes

 

[GTO]

...I have declared the variables used in the macros for the user form in "Sheet 1 (Data)" before any macro or sub, however it seems that the values given to those variables in the user form macros are not carried through when I use the same variable in "Sheet 1 (Data)".

Any thoughts on what I am doing wrong?..

Whilst you can assign values to Public variable is a worksheet's module, e.g.:

Userform w/two textboxes and one commandbutton, form code:

Option Explicit
 
Private Sub CommandButton1_Click()
 
    Sheet1.sTextbox1Val = Me.TextBox1.Value
    Sheet1.sTextbox2Val = Me.TextBox2.Value
 
    Unload Me
    Sheet1.ReportVals
End Sub

Sheet1 code:

Option Explicit
 
Public sTextbox1Val As String, sTextbox2Val As String
 
Sub ReportVals()
    MsgBox sTextbox1Val & "  " & sTextbox2Val
End Sub

IMO, probably an unnecessary hassle. Try just declaring the variables as Public in a Standard Module. That way, either Object Module (the sheet's or the form's) can easily reach them.

 

[johannes2008]

Hey GTO

Thanks for the reply.

How would I do that? "declaring the variables as Public in a Standard Module" that is? What is the difference between what I am using and a standard module?

Thanks for the help
-Johannes

 

[GTO]

Greetings Johannes,

In the code window, use either the little icon in the upper-left or the Insert button on the menubar, and pick 'Module' to insert (NOT Class Module). By default, the first one will be named 'Module1'. That is a Standard Module.

Mark

 

[johannes2008]

Thanks Mark for your help

one last question (hopefully) how do you declare the variable as public?

Thanks
-Johannes

 

[Jameo]

Instead of

Dim something as Class,

Public something as Class

 

[johannes2008]

Hey All

Thanks alot for all your help. I got it sorted now.

-Johannes