Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a Variable from one sub to another sub
I have two command buttons in a userform; cmbCalculate & cmbAddToQuote.
cmbCalculate when clicked calculates a price. cmbAddToQuote adds the price to the worksheet. In order to calculate a price the user must enter all valid information into the userform, if not a message box prompts them what to do and the sub exits. My problem is what if the user clicks the cmbAddToQuote button before a price is generated. I want to calculate the price and exit the entire sub if not all information is entered. But this code does not work why? Public Sub cmbCalculate_Click() Dim ExitEntireSub as Boolean If textbox1 = "" Then MsgBox "You must enter Information in TextBox1." ExitEntireSub = True Exit Sub End If End Sub Private Sub cmbAddToQuote_Click() Call cmbCalculate_Click If ExitEntireSub = True Then Exit Sub 'code to add information onto worksheet End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a Variable from one sub to another sub
Put:
Dim ExitEntireSub as Boolean in a really Public place (a standard module) and dim it as public: Public ExitEntireSub as Boolean -- Gary''s Student - gsnu200773 "RyanH" wrote: I have two command buttons in a userform; cmbCalculate & cmbAddToQuote. cmbCalculate when clicked calculates a price. cmbAddToQuote adds the price to the worksheet. In order to calculate a price the user must enter all valid information into the userform, if not a message box prompts them what to do and the sub exits. My problem is what if the user clicks the cmbAddToQuote button before a price is generated. I want to calculate the price and exit the entire sub if not all information is entered. But this code does not work why? Public Sub cmbCalculate_Click() Dim ExitEntireSub as Boolean If textbox1 = "" Then MsgBox "You must enter Information in TextBox1." ExitEntireSub = True Exit Sub End If End Sub Private Sub cmbAddToQuote_Click() Call cmbCalculate_Click If ExitEntireSub = True Then Exit Sub 'code to add information onto worksheet End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a Variable from one sub to another sub
Is "textbox1" the name of an actual text box on your userform?
If so, then textbox1.value would return whatever is typed there by the user. HTH, JP On Mar 14, 2:54*pm, RyanH wrote: I have two command buttons in a userform; cmbCalculate & cmbAddToQuote. * cmbCalculate when clicked calculates a price. *cmbAddToQuote adds the price to the worksheet. *In order to calculate a price the user must enter all valid information into the userform, if not a message box prompts them what to do and the sub exits. My problem is what if the user clicks the cmbAddToQuote button before a price is generated. *I want to calculate the price and exit the entire sub if not all information is entered. *But this code does not work why? Public Sub cmbCalculate_Click() Dim ExitEntireSub as Boolean If textbox1 = "" Then * * *MsgBox "You must enter Information in TextBox1." * * *ExitEntireSub = True * * *Exit Sub End If End Sub Private Sub cmbAddToQuote_Click() * * *Call cmbCalculate_Click * * *If ExitEntireSub = True Then Exit Sub * * *'code to add information onto worksheet End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a Variable from one sub to another sub
Hi Ryan;
You could make the second button be Visible = False initially and then set it to Visible = True at the end of the first routine or you could do the same thing using the Enable reserve word. Also, here is an example of how to pass from one sub to another Sub SubOne() varMyValueToTransfer = 22 MySecondSub varMyValueToTransfer End Sub Sub MySecondSub(varMyValueToTransfer) MsgBox "" & varMyValueToTransfer End Sub I hope this helps "RyanH" wrote: I have two command buttons in a userform; cmbCalculate & cmbAddToQuote. cmbCalculate when clicked calculates a price. cmbAddToQuote adds the price to the worksheet. In order to calculate a price the user must enter all valid information into the userform, if not a message box prompts them what to do and the sub exits. My problem is what if the user clicks the cmbAddToQuote button before a price is generated. I want to calculate the price and exit the entire sub if not all information is entered. But this code does not work why? Public Sub cmbCalculate_Click() Dim ExitEntireSub as Boolean If textbox1 = "" Then MsgBox "You must enter Information in TextBox1." ExitEntireSub = True Exit Sub End If End Sub Private Sub cmbAddToQuote_Click() Call cmbCalculate_Click If ExitEntireSub = True Then Exit Sub 'code to add information onto worksheet End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a Variable from one sub to another sub
How about an alternative?
Keep the cmbAddToQuote button disabled until the cmbCalculate button has been clicked. In fact, keep the cmbcalculate button disabled until all the input areas have data. I created a small userform with 2 textboxes, a combobox, a listbox, a label, and 3 commandbuttons (do calc, updatequote, and cancel). This is the code under the userform: Option Explicit Dim BlkProc As Boolean Private Sub CommandButton1_Click() 'calculate button 'do the calculation 'if everything is ok then enable the addtoquote button 'and disable the calc button Me.CommandButton1.Enabled = False Me.CommandButton2.Enabled = True End Sub Private Sub CommandButton2_Click() 'add to quote button 'if everything worked ok, then 'disable both buttons Me.CommandButton1.Enabled = False Me.CommandButton2.Enabled = False 'clear the input?? BlkProc = True Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.ComboBox1.ListIndex = -1 Me.ListBox1.ListIndex = -1 Me.Label1.Caption = "Enter your values" BlkProc = False End Sub Private Sub CommandButton3_Click() 'cancel button Unload Me End Sub Private Sub ComboBox1_Change() If BlkProc = True Then Exit Sub Call CheckAllInput End Sub Private Sub ListBox1_Change() If BlkProc = True Then Exit Sub Call CheckAllInput End Sub Private Sub TextBox1_Change() If BlkProc = True Then Exit Sub Call CheckAllInput End Sub Private Sub TextBox2_Change() If BlkProc = True Then Exit Sub Call CheckAllInput End Sub Private Sub UserForm_Initialize() With Me.CommandButton1 .Enabled = False .Caption = "Do Calc" End With With Me.CommandButton2 .Enabled = False .Caption = "Add to Quote" End With With Me.CommandButton3 .Enabled = True .Caption = "Cancel" End With With Me.ListBox1 .MultiSelect = fmMultiSelectSingle .AddItem "test1" .AddItem "test2" .ListIndex = -1 End With With Me.TextBox1 .Value = "" End With With Me.TextBox2 .Value = "" End With With Me.ComboBox1 .AddItem "cb1" .AddItem "cb2" .AddItem "cb3" .ListIndex = -1 .Style = fmStyleDropDownList End With With Me.Label1 .Caption = "Enter your values" End With Me.TextBox1.SetFocus End Sub Private Sub CheckAllInput() Dim OkBtn As Boolean 'make sure the addtoquote button is disabled after any change 'user must hit calculate and not make a change Me.CommandButton2.Enabled = False OkBtn = True If IsNumeric(Me.TextBox1.Value) = False Then OkBtn = False ElseIf Trim(Me.TextBox2.Value) = "" Then OkBtn = False ElseIf Me.ComboBox1.ListIndex < 0 Then OkBtn = False ElseIf Me.ListBox1.ListIndex < 0 Then OkBtn = False End If If OkBtn = True Then Me.Label1.Caption = "" Else Me.Label1.Caption = "Enter your values" End If Me.CommandButton1.Enabled = OkBtn End Sub RyanH wrote: I have two command buttons in a userform; cmbCalculate & cmbAddToQuote. cmbCalculate when clicked calculates a price. cmbAddToQuote adds the price to the worksheet. In order to calculate a price the user must enter all valid information into the userform, if not a message box prompts them what to do and the sub exits. My problem is what if the user clicks the cmbAddToQuote button before a price is generated. I want to calculate the price and exit the entire sub if not all information is entered. But this code does not work why? Public Sub cmbCalculate_Click() Dim ExitEntireSub as Boolean If textbox1 = "" Then MsgBox "You must enter Information in TextBox1." ExitEntireSub = True Exit Sub End If End Sub Private Sub cmbAddToQuote_Click() Call cmbCalculate_Click If ExitEntireSub = True Then Exit Sub 'code to add information onto worksheet End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass variable from one workbook to another | Excel Discussion (Misc queries) | |||
Pass Variable to another Sub | Excel Programming | |||
pass a variable to a public sub | Excel Programming | |||
How to pass a variable into an SQL statement | Excel Discussion (Misc queries) | |||
Pass a variable into a range? | Excel Programming |