Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: UserForm TextBox Sum Formula

  1. #1
    Join Date
    26th May 2006
    Posts
    77

    UserForm TextBox Sum Formula

    Greetings

    Being a newbie, I need your help again.

    (fyi, I have searched for the answer to this with no luck so far: )

    I have pieced together and edited a userform that has text boxes and drop downs to collect data and send it to a worksheet, and it's working very well so far...
    What I would like to do is - on the userform are two textboxes that will be filled out by the user, right below them is a textbox where I would like to immediately show the difference between the other two.

    ie: txtbox1 = 200
    txtbox2 = 125
    txtbox3 = 75

    I would like txtbox3 to show the difference right away and not allow the user to edit the box.

    I've just gotten into VBA and have tried a few ways to do this but just don't understand well enough yet.... If someone could show me some code that I could add to my userform I would appreciate it!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd June 2006
    Posts
    7

    Re: UserForm TextBox Sum Formula

    Steve,

    Assuming that you have some code to ensure that what the user has entered is numerical then set txtbox3 Locked property to True and add the following code to both txtbox1 and txtbox2 _Change code:

    VB:
    If TextBox1.Value = "" Then Exit Sub 
    If TextBox2.Value = "" Then Exit Sub 
    TextBox3.Value = CDbl(TextBox1.Value) - CDbl(TextBox2.Value) 
    
    
    Good Luck

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th May 2006
    Posts
    77

    Re: UserForm TextBox Sum Formula

    Thanks,

    I'm including my code so far, because I don't have the _change item you mentioned, maybe you could point out where it should go?


    VB:
     
     
    Private Sub cmdCancel_Click() 
        Unload Me 
    End Sub 
     
    Private Sub cmdClearForm_Click() 
        Call UserForm_Initialize 
    End Sub 
     
    Private Sub cmdOK_Click() 
        ActiveWorkbook.Sheets("Plant Production").Activate 
        Range("A1").Select 
        Do 
            If IsEmpty(ActiveCell) = False Then 
                ActiveCell.Offset(1, 0).Select 
            End If 
        Loop Until IsEmpty(ActiveCell) = True 
        ActiveCell.Value = txtDate.Value 
        ActiveCell.Offset(0, 1) = txtPlant.Value 
        ActiveCell.Offset(0, 2) = txtTonsDel.Value 
        ActiveCell.Offset(0, 3) = txtPlantProd.Value 
        ActiveCell.Offset(0, 4) = txtRunTime.Value 
        ActiveCell.Offset(0, 5) = txtACTons.Value 
        ActiveCell.Offset(0, 6) = txt5Beg.Value 
        ActiveCell.Offset(0, 7) = txt5End.Value 
        ActiveCell.Offset(0, 8) = txt5Total.Value 
        ActiveCell.Offset(0, 9) = txt2Beg.Value 
        ActiveCell.Offset(0, 10) = txt2End.Value 
        ActiveCell.Offset(0, 11) = txt2Total.Value 
         
         
        If optNewData = True Then 
            ActiveCell.Offset(0, 25).Value = "New" 
        ElseIf optCorrection = True Then 
            ActiveCell.Offset(0, 25).Value = "Corrected" 
             'Else
             '    ActiveCell.Offset(0, 25).Value = "Adv"
        End If 
        Range("A1").Select 
    End Sub 
     
    Private Sub UserForm_Initialize() 
        txtDate.Value = Date 
        txtTonsDel.Value = "" 
        txtPlantProd.Value = "" 
        txtRunTime.Value = "" 
        txtPlant.Value = "" 
        txt5Beg.Value = "" 
        txt5End.Value = "" 
        txt5Total.Value = "" 
        txtACTons.Value = "" 
        With cboWeather 
            .AddItem "Clear" 
            .AddItem "Cloudy" 
            .AddItem "Light Rain" 
            .AddItem "Heavy Rain" 
            .AddItem "Severe" 
            .AddItem "We Shouldn't Be Here!" 
             
        End With 
        cboWeather.Value = "" 
         
         
        optNewData = True 
        txtDate.SetFocus 
    End Sub 
    
    

    Thanks everyone for any help!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    3rd June 2006
    Posts
    7

    Re: UserForm TextBox Sum Formula

    seeing as you haven't used the textbox change event you need to add another two private subs to look like this (one for each of the two text boxes):

    VB:
    Private Sub TextBox1_Change() 
        If TextBox1.Value = "" Then Exit Sub 
        If TextBox2.Value = "" Then Exit Sub 
        TextBox3.Value = CDbl(TextBox1.Value) - CDbl(TextBox2.Value) 
    End Sub 
     
    Private Sub TextBox2_Change() 
        If TextBox1.Value = "" Then Exit Sub 
        If TextBox2.Value = "" Then Exit Sub 
        TextBox3.Value = CDbl(TextBox1.Value) - CDbl(TextBox2.Value) 
    End Sub 
    
    
    Note you will need to change TextBox1, TextBox2 and TextBox3 to the same names as you have used for the entry boxes on your form (eg txt2Beg, txt2End, etc).

    This code won't calculate if there is no data in one of the two text boxes and will not check to see if what is entered is numerical (which I would suggest you do if others are going to use the tool).
    Last edited by a71free4me; June 4th, 2006 at 09:19.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th May 2006
    Posts
    77

    Re: UserForm TextBox Sum Formula

    Hey, Thanks Alot!!

    I Appreciate it.

    If you don't mind, I have another Question.

    I have a second drop down in my form called "cmbMixesA" and I want it to choose from a list of mix designs that I have on "Sheet2" A1:A50 and named as a range called "Mixes". I've tried to get it to work in VBA as my "Weather" dropdown does, only I want it to reference the range on sheet2. (I just kept getting errors) The only way I can get it to work is to put "mixes" in the RowSource part of the properties.
    I'd rather have it as part of the code because eventually I want to restrict the list based on which plant is reported.

    T.I.A. if you can help

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th May 2006
    Posts
    77

    Re: UserForm TextBox Sum Formula

    O.K.

    I'm liking the way the change sub works but I would like to understand it better. What does CDbl mean?

    I'm hoping that the " - " between the two txtbox references means "minus" and that I can use other operators such as divide or multiply......

    What I'd like to do now is take two other text boxes and use divide and show the result as a percentage. It would be good feedback for the plant personel who will be completing this form.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,314

    Re: UserForm TextBox Sum Formula

    Select CDbl in your code & press F1 to read the relevant Help File
    Last edited by royUK; June 4th, 2006 at 15:32.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716

  9. #9
    Join Date
    3rd June 2006
    Posts
    7

    Re: UserForm TextBox Sum Formula

    Quote Originally Posted by Steve M
    O.K.

    I'm liking the way the change sub works but I would like to understand it better. What does CDbl mean?

    I'm hoping that the " - " between the two txtbox references means "minus" and that I can use other operators such as divide or multiply......

    What I'd like to do now is take two other text boxes and use divide and show the result as a percentage. It would be good feedback for the plant personel who will be completing this form.
    Steve M,

    Yes the " - " does mean minus and you can use multiply divide or any other mathematical operators you wish.

    CDbl converts numbers in a string (textbox input is automatically stored as a string) to numerical format specifically a data type of double (which means it can be a number as big or small as excel can handle including decimal portion).

    With this info hopefully you can take other text boxes and perform other calculations as you wish

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    5th November 2004
    Location
    Leicestershire
    Posts
    266

    Re: UserForm TextBox Sum Formula

    This may not be the best way but I have used the following code to sum the values of text boxes. This will show a running total of all 3 text boxes, even if a textbox is empty.

    VB:
    Private Sub TextBox2_Change() 
         
        ValidateNumber Me.TextBox2 
        AddTextBox 
         
    End Sub 
     
    Private Sub TextBox3_Change() 
         
        ValidateNumber Me.TextBox3 
        AddTextBox 
         
    End Sub 
     
    Private Sub TextBox4_Change() 
         
        ValidateNumber Me.TextBox4 
        AddTextBox 
         
    End Sub 
     
    Sub ValidateNumber(TextBox) 
         
        Dim sTxt As String 
         
        sTxt = TextBox.Text 
        If sTxt = "" Then Exit Sub 
         
        If IsNumeric(sTxt) Then 
            If InStr(sTxt, ".") > 0 Then 
                If Len(sTxt) - InStr(sTxt, ".") > 2 Then 
                    TextBox.Text = Mid(sTxt, 1, Len(sTxt) - 1) 
                End If 
            End If 
             
            Exit Sub 
        End If 
        TextBox.Text = Mid(sTxt, 1, Len(sTxt) - 1) 
         
    End Sub 
     
    Private Sub AddTextBox() 
         
        With Me 
            sVal1 = .TextBox2.Value 
            sVal2 = .TextBox3.Value 
            sVal3 = .TextBox4.Value 
             
            If sVal1 = "" Then sVal1 = 0 
            If sVal2 = "" Then sVal2 = 0 
            If sVal3 = "" Then sVal3 = 0 
             
            dTotal = CDbl(sVal1) + CDbl(sVal2) + CDbl(sVal3) 
            .TextBox1 = Format(dTotal, "0.00") 
        End With 
         
    End Sub 
    
    
    Kev

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: May 23rd, 2008, 22:14
  2. Populate Userform Textbox From Another Userform
    By westic in forum EXCEL HELP
    Replies: 13
    Last Post: November 23rd, 2007, 18:54
  3. Populate Textbox On Userform With Named Formula
    By jolivanes in forum EXCEL HELP
    Replies: 4
    Last Post: September 25th, 2006, 12:15
  4. Userform button to populate userform textbox
    By syaworski in forum EXCEL HELP
    Replies: 1
    Last Post: June 20th, 2006, 01:07
  5. Replies: 4
    Last Post: May 12th, 2006, 14:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno