UserForm TextBox Sum Formula

[Steve M]

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!

[a71free4me]

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:

If TextBox1.Value = "" Then Exit Sub 
If TextBox2.Value = "" Then Exit Sub 
TextBox3.Value = CDbl(TextBox1.Value) - CDbl(TextBox2.Value) 

Good Luck

[Steve M]

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?

 
 
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!

[a71free4me]

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

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).

[Steve M]

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

[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.

[royUK]

Select CDbl in your code & press F1 to read the relevant Help File

[Dave Hawley]

See Also: VBA conversion functions

[a71free4me]

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

[Ktrasler]

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.

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