How to run a macro when a form opens?

[Donny]

I need to run a macro when I open a user form. It doesn't seem to
work like the workbook_open() line works. Also, I can't seem to
figure out how to give and take info from my worksheet and worksheet
variables to the form. ie- the variables in the worksheet macros
don't work in the userform macros. how do i delcare these and keep
there current values?


Private Sub StoreForm_Open()
Money = Sheet1.Range("E9") '<<doesn't work
StoreMoney = 0
StoreText = "$" & StoreMoney'<<doesn't work
End Sub


Thanks, Chris

 

[Mike H]

Try this

Private Sub UserForm_Activate()
Money = Sheet1.Range("E9") '<<doesn't work
StoreMoney = 0
StoreText = "$" & StoreMoney '<<doesn't work
End Sub

For me this now picks up the value of (codename) sheet1
Storetext is doing exactly what you are telling it to do and holds $0

Mike

 

[Donny]

I'm still not getting the variable pulled into the userform code.
'Money' is a variable in the Sheet1 code, and it's displayed in cell
E9. When the user form opens, it doesn't know the value of the
variable 'Money' so it treats it as 0. I see two options, the better
would be getting the userform macros to recognize the sheet1
variables. The other option is to pull a cell value from sheet1
(which i can't make work)

Dim StoreMoney as Double

Private Sub StoreForm_Activate()
Money = Sheet1.Range("E9") '<<doesn't work
StoreMoney = 0
StoreText = "$" & StoreMoney ' <does work now
End Sub

Private Sub BuyStuff_Click()
If Money >= StoreMoney Then ' <still sees Money as zero
Money = Money - StoreMoney
Sheet1.Range("E9") = Money
Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not
enough cash!")
End If
End Sub

Thanks for the help

 

[Donny]

Looking back on it, the first section is all part of the (General)
category, while all the other Subs are in their own category. Is this
part of the problem? After searching the posts, I still can't find
how to get the variables (and their current values) from the worksheet
code into the userform code to use.

'-This is part of the (General) category---------------------
Dim StoreMoney as Double

Private Sub StoreForm_Activate()
Money = Sheet1.Range("E9")  ' <<doesn't work - would rather just get
it's value from worksheet code
StoreMoney = 0
StoreText = "$" & StoreMoney ' <<doesn't work
End Sub
'---------------------------------------------------------------------------


Thanks for the help

 

[Donny]

Using UserForm_Initialize() helped, but i still can't get the
worksheet variables to work in the UserForm code.


Dim StoreMoney as Double
Dim Money as Double 'Shouldn't have to do this again

Private Sub UserForm_Initialize()
Money = Sheet1.Range("E9")  ' <<does work now - would rather just
' get it's value from
worksheet1 code
StoreMoney = 0
StoreText = "$" & StoreMoney ' <<does work now
End Sub


Thanks for the help

 

[Barb Reinhardt]

IIRC, when you dimension a variable like this

Dim myVariable as Variant

at the beginning of a module, it's only valid in the module.

If you dimension it as

Public myVariable as Variant

It can be used throughout the code.