PDA

View Full Version : Solved: Adding Txt Boxes in Form



Emoncada
05-07-2007, 02:14 PM
I have about 10 txtboxes in a UserForm. I have a LblTotal that I would like to have updated everytime there is a change in those txtboxes. How can I do that?

Simon Lloyd
05-07-2007, 03:08 PM
What do you want totalled and where if you want LblTotal to be the combined total of the textboxes maybe Me.LblTotal=textbox1.value + Textbox2.value....etc
or do you want to count the amount of controls?
Me.Controls.Count?

Regards,
Simon

Emoncada
05-07-2007, 04:39 PM
Well the txtboxes would just be a number. So I am trying
Me.LblTotal.Caption = TxtQua1.Value + TxtQua2.Value + TxtQua3.Value + TxtQua4.Value + TxtQua5.Value + TxtQua6.Value + TxtQua7.Value + TxtQua8.Value + TxtQua9.Value + TxtQua10.Value + TxtQua11.Value + TxtQua12.Value

But that's not working its not giving me anything.
Where should I put that?

lucas
05-07-2007, 05:01 PM
Not sure Em but I think you have to dim the textbox variables as integers...this works but I only did the first 2 textboxes for you....

Emoncada
05-07-2007, 05:53 PM
That adds them but I would like for that to happen automatically not have to click a cmd button. Can that be done?

Bob Phillips
05-07-2007, 07:58 PM
Use the textbox exit procedure.

Emoncada
05-07-2007, 08:22 PM
Would I need that in each textbox?

Bob Phillips
05-07-2007, 08:23 PM
Yep.

Emoncada
05-07-2007, 08:30 PM
Instead of it giving me a sum it's giving me 1111
If the first 4 are 1's.
How can I assign txtboxes to be integers?

Bob Phillips
05-07-2007, 08:32 PM
Add them, don't concatenate.

Emoncada
05-07-2007, 08:39 PM
please explain

Bob Phillips
05-07-2007, 09:03 PM
Use CDbl(Texbox1.Text) + CDbl(TextBox2.Text) + ...

instead of

TextBox1.Text + TextBox2.text + ...

Simon Lloyd
05-08-2007, 02:16 AM
The Textbox1 + Textbox2....is my fault Bob, i forgot about one of your answers to earlier posts where you Cast Double.

Apologies!

Bob Phillips
05-08-2007, 06:55 AM
You would think that it would be smart enough to know that + means add, and automatically Cast it for you. After all, there is a & operator to allow true concatenation.

Simon Lloyd
05-08-2007, 08:07 AM
Well when i wrote "+" that was my assumption because with what little i know, i know that "&" means this AND that. All i can imagine Bob is that it must have been a human that devised the language and had he or she made it perfect there would be no room for improvement and no Excel 2007 etc!
LOL!

Bob Phillips
05-08-2007, 08:59 AM
and then where would MS be ?

Simon Lloyd
05-08-2007, 10:23 AM
I would imagine Bill Gates would still be drawing on his UB40!

Emoncada
05-08-2007, 10:33 AM
How can I have that work without having to click anything. I have cmdbuttons that will autopopulate some of the TxtQua Boxes.

lucas
05-08-2007, 10:50 AM
Hi Em,
I would add a sub to the userform like this:
Private Sub add()
LblTotal.Caption = CDbl(TxtQua1.Text) + CDbl(TxtQua2.Text) _
+ CDbl(TxtQua3.Text) + CDbl(TxtQua4.Text) + CDbl(TxtQua5.Text)
End Sub


then in each command button add a call to the add routine like this:
Private Sub CmdNC8430_Click()
LoadTextBoxes "HP NC8430", "", "1"
LoadTextBoxes "LAPTOP BAG", "Expense", "1"
LoadTextBoxes "KEYBOARD", "Expense", "1"
LoadTextBoxes "MOUSE", "Expense", "1"
LoadTextBoxes "PORT REPLICATOR", "Expense", "1"
add
End Sub

I only did the one button for you but you can get it from there I'm sure
CmdNC8430_Click() is the one I set up for you in the attachment. You will have to add the call to "add" to each commandbutton

Emoncada
05-09-2007, 06:56 AM
Hey lucas that seems to work good only thing is it's giving me an error. It seems the reason it's giving me an error is because the other values are "". How can I fix that.

I have this
Private Sub add()
UserForm1.LblTotal.Caption = CDbl(TxtQua1.Text) + CDbl(TxtQua2.Text) + CDbl(TxtQua3.Text) + CDbl(TxtQua4.Text) + CDbl(TxtQua5.Text) + CDbl(TxtQua6.Text) '+ CDbl(TxtQua7.Text) + CDbl(TxtQua8.Text) + CDbl(TxtQua9.Text) + CDbl(TxtQua10.Text) + CDbl(TxtQua11.Text) + CDbl(TxtQua12.Text)
End Sub

So if a cmdbutton is only 4 or 5 lines TxtQua6.Text through TxtQua12.Text = "". Any way i can possibly do an if statement to test if it's "" if so then ignore

lucas
05-09-2007, 07:40 AM
Em,
could you post the workbook with the errors so we can see what your doing...?

Bob Phillips
05-09-2007, 08:21 AM
Private Sub add()
UserForm1.LblTotal.Caption = Iif(TxtQua1.Text = "", 0, CDbl(TxtQua1.Text)) + _
Iif(TxtQua2.Text = "", 0, CDbl(TxtQua2.Text)) + _
Iif(TxtQua3.Text = "", 0, CDbl(TxtQua3.Text)) + _
Iif(TxtQua4.Text = "", 0, CDbl(TxtQua4.Text)) + _
Iif(TxtQua5.Text = "", 0, CDbl(TxtQua5.Text)) + _
Iif(TxtQua6.Text = "", 0, CDbl(TxtQua6.Text)) + _
Iif(TxtQua7.Text = "", 0, CDbl(TxtQua7.Text)) + _
Iif(TxtQua8.Text = "", 0, CDbl(TxtQua8.Text)) + _
Iif(TxtQua9.Text = "", 0, CDbl(TxtQua9.Text)) + _
Iif(TxtQua10.Text = "", 0, CDbl(TxtQua10.Text)) + _
Iif(TxtQua11.Text = "", 0, CDbl(TxtQua11.Text)) + +
Iif(TxtQua12.Text = "", 0, CDbl(TxtQua12.Text))
End Sub

Emoncada
05-09-2007, 08:39 AM
xld that's giving me a runtime error '13' Type Mismatch.

Emoncada
05-09-2007, 08:43 AM
This is the Form

lucas
05-09-2007, 09:06 AM
Short on time Em but what if you put in zero's so the math can be performed....see attachment.

Bob Phillips
05-09-2007, 09:13 AM
Sorry, I keep forgetting that VBA doesn't short-circuit



Dim tmp
If TxtQua1.Text "" Then
tmp = tmp + CDbl(TxtQua1.Text)
End If
If TxtQua2.Text "" Then
tmp = tmp + CDbl(TxtQua2.Text)
End If
If TxtQua3.Text "" Then
tmp = tmp + CDbl(TxtQua3.Text)
End If
If TxtQua4.Text "" Then
tmp = tmp + CDbl(TxtQua4.Text)
End If
If TxtQua5.Text "" Then
tmp = tmp + CDbl(TxtQua5.Text)
End If
If TxtQua6.Text "" Then
tmp = tmp + CDbl(TxtQua6.Text)
End If
If TxtQua7.Text "" Then
tmp = tmp + CDbl(TxtQua7.Text)
End If
If TxtQua8.Text "" Then
tmp = tmp + CDbl(TxtQua8.Text)
End If
If TxtQua9.Text "" Then
tmp = tmp + CDbl(TxtQua9.Text)
End If
If TxtQua10.Text "" Then
tmp = tmp + CDbl(TxtQua10.Text)
End If
If TxtQua11.Text "" Then
tmp = tmp + CDbl(TxtQua11.Text)
End If
If TxtQua12.Text "" Then
tmp = tmp + CDbl(TxtQua12.Text)
End If
UserForm1.LblTotal.Caption = tmp

Emoncada
05-09-2007, 09:22 AM
xld that's giving me the same error. It seems not to like the empty cells. Anyway I can Assign those txtboxes as 0 but not have the 0 visible.

Bob Phillips
05-09-2007, 09:26 AM
It worked for me.

lucas
05-09-2007, 09:33 AM
I don't think Bob's code all came through.....I know the equal signs were missing in the post and I added ......not = to and it works for me...

you can also clear all textboxes on the form useing
Private Sub CommandButton6_Click()
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
ctrl.Text = ""
End If
Next ctrl
End Sub


see attached....and let us know.

Bob Phillips
05-09-2007, 10:07 AM
Damn, the <, > problem again

Bob Phillips
05-09-2007, 10:09 AM
Dim tmp
If TxtQua1.Text <> "" Then
tmp = tmp + CDbl(TxtQua1.Text)
End If
If TxtQua2.Text <> "" Then
tmp = tmp + CDbl(TxtQua2.Text)
End If
If TxtQua3.Text <> "" Then
tmp = tmp + CDbl(TxtQua3.Text)
End If
If TxtQua4.Text <> "" Then
tmp = tmp + CDbl(TxtQua4.Text)
End If
If TxtQua5.Text <> "" Then
tmp = tmp + CDbl(TxtQua5.Text)
End If
If TxtQua6.Text <> "" Then
tmp = tmp + CDbl(TxtQua6.Text)
End If
If TxtQua7.Text <> "" Then
tmp = tmp + CDbl(TxtQua7.Text)
End If
If TxtQua8.Text <> "" Then
tmp = tmp + CDbl(TxtQua8.Text)
End If
If TxtQua9.Text <> "" Then
tmp = tmp + CDbl(TxtQua9.Text)
End If
If TxtQua10.Text <> "" Then
tmp = tmp + CDbl(TxtQua10.Text)
End If
If TxtQua11.Text <> "" Then
tmp = tmp + CDbl(TxtQua11.Text)
End If
If TxtQua12.Text <> "" Then
tmp = tmp + CDbl(TxtQua12.Text)
End If
UserForm1.LblTotal.Caption = tmp

lucas
05-09-2007, 10:12 AM
That's better bob.....this should be working for you Em
Bob...I thought the = sign had been dropped...

Emoncada
05-09-2007, 10:19 AM
YES Great Job Guys That works for me. I greatly Appreciate it.