class11


Hey,

I created a public variable in sheet code and assigned a value to it. Now I want to be able to call a userform from the sheet code and use the value I established in the Userform code.

Is there a way to do this




Re: public variable between sheet code and userform

Andy Pope


Hi,

You need to reference your public variable via the sheet object.
For example in sheet1 code module you have a public variable MySheetVar and on the sheet a commandbutton with the following click event code,

Code Snippet

Public MySheetVar As Long

Private Sub CommandButton1_Click()

MySheetVar = 123
UserForm1.Show

End Sub


Userform1 has a commandbutton with the following click event code to display the value

Code Snippet

Private Sub CommandButton1_Click()

MsgBox Sheet1.MySheetVar

End Sub



Alternatively place your public declaration in a standard code module rather than the sheet code.
Then you can just use the variable name.