Announcement

Collapse
No announcement yet.

UserForm TextBox Sum Formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    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

    Comment


    • #3
      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!

      Comment


      • #4
        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, 09:19.

        Comment


        • #5
          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

          Comment


          • #6
            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.

            Comment


            • #7
              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, 15:32.
              Hope that Helps

              Roy

              New users should read the Forum Rules before posting

              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.

              Comment


              • #8
                Re: UserForm TextBox Sum Formula

                See Also: VBA conversion functions

                Comment


                • #9
                  Re: UserForm TextBox Sum Formula

                  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

                  Comment


                  • #10


                    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

                    Comment

                    Working...
                    X