Transfer variable to user Form

[jlclyde]

I have a public variable that I declare at the top of a standard
module. When I run the code to set the variable it appears to be set
and has a value. Then when the form is loaded it forgets the
variable. I am tryign to setup a form to either enter new information
or edit existing information. There will be two buttons on the sheet
to either Add or Edit. If they hit the Edit button I have it set so
they can chose what to edit with an input box as range. This variable
is set to Public so i assumed it woudl carry over to the initialize
part of the user form. Any thoughts?

Thanks,
Jay

 

[JLatham]

I'm not real clear about your public variable, whether it's a value or a
Range, but you said range, so for the UserForm Initialize event, try
something like:

Private Sub UserForm_Initialize()
'assumes myPublicVariable is a Range
'and that it's been set up somewhere with
'something like:
' Set myPublicVariable=Sheets(1).Range("A1")

Me!TextBox1 = myPublicVariable.Value
End Sub

 

[Dave Peterson]

If you're sure you're initializing that variable somehow, then I'd check to see
if you have a variable that's local to that userform or procedure that is the
same name.

If you have this in a general module:

Public myVar as long
sub Auto_Open()
myVar = 1234
End sub

And you have this in a procedure behind a userform:

Private Sub CommandButton1_Click()
Dim myVar As Long
MsgBox myVar
End Sub

Then the local variable in that _click event will be the one that's used.

========
Another problem while you're in development mode is that you could have ended
any macro by clicking on Run|Reset (in the VBE menus, or hitting the Reset
button on a toolbar). This will erase the values in those public variables.

And if you use "End" to exit any procedure (not End With, End Sub, End
Function--just plain old End), you'll be resetting those public variables.