Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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:

    Code:
    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?


    Code:
     
    
    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):

    Code:
    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 10: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
    19,293

    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 16: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,786

  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.

    Code:
    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, 23:14
  2. Populate Userform Textbox From Another Userform
    By westic in forum EXCEL HELP
    Replies: 13
    Last Post: November 23rd, 2007, 19:54
  3. Populate Textbox On Userform With Named Formula
    By jolivanes in forum EXCEL HELP
    Replies: 4
    Last Post: September 25th, 2006, 13:15
  4. Userform button to populate userform textbox
    By syaworski in forum EXCEL HELP
    Replies: 1
    Last Post: June 20th, 2006, 02:07
  5. Replies: 4
    Last Post: May 12th, 2006, 15: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