Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get variables from worksheet code to userform code
In my worksheet, I have buttons to run macros, and I also have a
UserForm. In the worksheet code I have: Dim Money as Double Private Sub StartButton_Click() Money = 100 End Sub Then, in the UserForm code: Dim StoreMoney as Double '--I have macros that set the value of StoreMoney Private Sub BuyStuff_Click() If Money = StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub The problem is that the userform does not know that Money=100, so it just set's it at zero. How can I tell the userform code to use the variable from the worksheet code? Thanks, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get variables from worksheet code to userform code
Declare Money as Public
Public Money As Double -- __________________________________ HTH Bob "Donny" wrote in message ... In my worksheet, I have buttons to run macros, and I also have a UserForm. In the worksheet code I have: Dim Money as Double Private Sub StartButton_Click() Money = 100 End Sub Then, in the UserForm code: Dim StoreMoney as Double '--I have macros that set the value of StoreMoney Private Sub BuyStuff_Click() If Money = StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub The problem is that the userform does not know that Money=100, so it just set's it at zero. How can I tell the userform code to use the variable from the worksheet code? Thanks, Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get variables from worksheet code to userform code
Made these changes, unfortunately still won't work.
'Worksheet code-------------- Public Money As Double Sub StartButton_Click() Money = 100 End Sub 'Worksheet code-------------- 'Userform code-------------------- Public StoreMoney As Double Sub UserForm_Initialize() StoreMoney = 0 StoreText = "$" & StoreMoney 'This shows $0 in the Userform text box End Sub 'Macro that sets the value of StoreMoney to 75 Sub BuyStuff_Click() MsgBox (Money) MsgBox (StoreMoney) If Money = StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub 'Userform code-------------------- I get three MsgBoxs: Blank 75 You don't have enough money! Money variable is still not availible to the userform macro. In the Userform (general) I wrote: Public Money As Double This just gives a 0 in the first MsgBox. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get variables from worksheet code to userform code
It worked for me.
I put Public Money As Double at the top of Module2 then this code. Sub mony() Money = 100 UserForm1.Show End Sub I created a UserForm1 with a TextBox1 with this code behind the form. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) MsgBox "$" & TextBox1.Text End Sub Private Sub UserForm_Initialize() TextBox1.Text = Money End Sub The TextBox shows a text of 100 and when I exit the UserForm, which simultaneously exits the TextBox, I get a message box with $100. "Donny" wrote: Made these changes, unfortunately still won't work. 'Worksheet code-------------- Public Money As Double Sub StartButton_Click() Money = 100 End Sub 'Worksheet code-------------- 'Userform code-------------------- Public StoreMoney As Double Sub UserForm_Initialize() StoreMoney = 0 StoreText = "$" & StoreMoney 'This shows $0 in the Userform text box End Sub 'Macro that sets the value of StoreMoney to 75 Sub BuyStuff_Click() MsgBox (Money) MsgBox (StoreMoney) If Money = StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub 'Userform code-------------------- I get three MsgBoxs: Blank 75 You don't have enough money! Money variable is still not availible to the userform macro. In the Userform (general) I wrote: Public Money As Double This just gives a 0 in the first MsgBox. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get variables from worksheet code to userform code
Public declaration should be made in the standard code module, not the sheet
module. "Donny" wrote: Made these changes, unfortunately still won't work. 'Worksheet code-------------- Public Money As Double Sub StartButton_Click() Money = 100 End Sub 'Worksheet code-------------- 'Userform code-------------------- Public StoreMoney As Double Sub UserForm_Initialize() StoreMoney = 0 StoreText = "$" & StoreMoney 'This shows $0 in the Userform text box End Sub 'Macro that sets the value of StoreMoney to 75 Sub BuyStuff_Click() MsgBox (Money) MsgBox (StoreMoney) If Money = StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub 'Userform code-------------------- I get three MsgBoxs: Blank 75 You don't have enough money! Money variable is still not availible to the userform macro. In the Userform (general) I wrote: Public Money As Double This just gives a 0 in the first MsgBox. Any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get variables from worksheet code to userform code
I also tried this, and I get a blank text box and a $ msgbox. :( I must be declaring Public Money in the wrong place. I tried it in ThisWorkbook and in Sheet1, same results when running Sheet1.mony() and ThisWorkbook.mony() The only place I see "modules" is under the Personal.xls, which I don't want. What could I be missing? On Aug 22, 7:50*pm, JLGWhiz wrote: It worked for me. I put Public Money As Double at the top of Module2 then this code. Sub mony() * *Money = 100 * *UserForm1.Show End Sub I created a UserForm1 with a TextBox1 with this code behind the form. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) * * MsgBox "$" & TextBox1.Text End Sub Private Sub UserForm_Initialize() * * TextBox1.Text = Money End Sub The TextBox shows a text of 100 and when I exit the UserForm, which simultaneously exits the TextBox, I get a message box with $100. "Donny" wrote: Made these changes, unfortunately still won't work. 'Worksheet code-------------- Public Money As Double Sub StartButton_Click() Money = 100 End Sub 'Worksheet code-------------- 'Userform code-------------------- Public StoreMoney As Double Sub UserForm_Initialize() StoreMoney = 0 StoreText = "$" & StoreMoney *'This shows $0 in the Userform text box End Sub 'Macro that sets the value of StoreMoney to 75 Sub BuyStuff_Click() MsgBox (Money) MsgBox (StoreMoney) If Money = StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub 'Userform code-------------------- I get three MsgBoxs: Blank 75 You don't have enough money! Money variable is still not availible to the userform macro. In the Userform (general) I wrote: Public Money As Double This just gives a 0 in the first MsgBox. Any ideas? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get variables from worksheet code to userform code
On Aug 22, 7:54*pm, JLGWhiz wrote:
Public declaration should be made in the standard code module, not the sheet module. "Donny" wrote: Made these changes, unfortunately still won't work. 'Worksheet code-------------- Public Money As Double Sub StartButton_Click() Money = 100 End Sub 'Worksheet code-------------- 'Userform code-------------------- Public StoreMoney As Double Sub UserForm_Initialize() StoreMoney = 0 StoreText = "$" & StoreMoney *'This shows $0 in the Userform text box End Sub 'Macro that sets the value of StoreMoney to 75 Sub BuyStuff_Click() MsgBox (Money) MsgBox (StoreMoney) If Money = StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub 'Userform code-------------------- I get three MsgBoxs: Blank 75 You don't have enough money! Money variable is still not availible to the userform macro. In the Userform (general) I wrote: Public Money As Double This just gives a 0 in the first MsgBox. Any ideas? Got it - I guess i've never created a module to put variables or code in. That's next to learn. Thanks a bunch! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with userform, refercencing code to worksheet | Excel Programming | |||
how to prevent code running when in a worksheet code | Excel Programming | |||
Code in userform/worksheet vs. in Module | Excel Programming | |||
Code Conflicts With Worksheet Change Code | Excel Programming | |||
Create a newworksheet with VBA code and put VBA code in the new worksheet module | Excel Programming |