Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Pass a Variable from one sub to another sub

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RyanH - 14 Mar 2008 19:54 GMT
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
Gary''s Student - 14 Mar 2008 20:08 GMT
Put:

Dim ExitEntireSub as Boolean

in a really Public place (a standard module) and dim it as public:

Public ExitEntireSub as Boolean

Signature

Gary''s Student - gsnu200773

> I have two command buttons in a userform; cmbCalculate & cmbAddToQuote.  
> cmbCalculate when clicked calculates a price.  cmbAddToQuote adds the price
[quoted text clipped - 27 lines]
>
> End Sub
JP - 14 Mar 2008 20:11 GMT
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

> I have two command buttons in a userform; cmbCalculate & cmbAddToQuote.  
> cmbCalculate when clicked calculates a price.  cmbAddToQuote adds the price
[quoted text clipped - 27 lines]
>
> End Sub
DownThePaint - 14 Mar 2008 20:13 GMT
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

> I have two command buttons in a userform; cmbCalculate & cmbAddToQuote.  
> cmbCalculate when clicked calculates a price.  cmbAddToQuote adds the price
[quoted text clipped - 27 lines]
>
> End Sub
Dave Peterson - 14 Mar 2008 21:20 GMT
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

> I have two command buttons in a userform; cmbCalculate & cmbAddToQuote.
> cmbCalculate when clicked calculates a price.  cmbAddToQuote adds the price
[quoted text clipped - 27 lines]
>
> End Sub

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.