PDA

View Full Version : Solved: TextBox and Label Calculation



Darren
08-20-2007, 05:13 AM
Hi Masters

Trying hard to do the code and winning

What i have is

TextBox14 has total turnover amount in it.

TextBox1 has Electricity Amount paid

lblElectr must show % of Turnover

lblElectr = TextBox1 / TextBox14 and show as %

TIA

Darren
South Africa

Bob Phillips
08-20-2007, 05:35 AM
lblElectr.Caption = Format(CBdl(TextBox1.Text) / CDbl(TextBox14.Text), "%")

Darren
08-20-2007, 01:51 PM
Hi Xld

Thanks for the reply, I tried the code and had to read a little About CBdl could not find but found CDbl which converts to Double.

lblElectr.Caption = Format(CBdl(TextBox1.Text) / CDbl(TextBox14.Text), "%")

after reading i changed it to
lblElectr.Caption = Format(CDbl(TextBox1.Text) / CDbl(TextBox14.Text), "0.00%")

it works well

This is the code for my userform

Private Sub TextBox2_AfterUpdate()
TextBox2 = Format(TextBox2, "#,##0.00")
lblRent.Caption = Format(CDbl(TextBox2.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox3_AfterUpdate()
TextBox3 = Format(TextBox3, "#,##0.00")
lblStaffSal.Caption = Format(CDbl(TextBox3.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox4_AfterUpdate()
TextBox4 = Format(TextBox4, "#,##0.00")
lblManageSal.Caption = Format(CDbl(TextBox4.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox5_AfterUpdate()
TextBox5 = Format(TextBox5, "#,##0.00")
lblInsurance.Caption = Format(CDbl(TextBox5.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub UserForm_Initialize()
Dim Gross
Gross = Range("I10")
lblBasicInFormulas = Format(Gross, "#,##0.00")
TextBox14 = Format(Gross, "#,##0.00")
ITurnover = Format(Gross, "#,##0.00")
End Sub
Private Sub TextBox1_AfterUpdate()
TextBox1 = Format(TextBox1, "#,##0.00")
lblElectr.Caption = Format(CDbl(TextBox1.Text) / CDbl(TextBox14.Text), "0.00%")

End Sub
Private Sub TextBox6_AfterUpdate()
TextBox6 = Format(TextBox6, "#,##0.00")
lblAdvertis.Caption = Format(CDbl(TextBox6.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox7_AfterUpdate()
TextBox7 = Format(TextBox7, "#,##0.00")
lblCleaning.Caption = Format(CDbl(TextBox7.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox8_AfterUpdate()
TextBox8 = Format(TextBox8, "#,##0.00")
lblSecurity.Caption = Format(CDbl(TextBox8.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox9_AfterUpdate()
TextBox9 = Format(TextBox9, "#,##0.00")
lblTelephone.Caption = Format(CDbl(TextBox9.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox10_AfterUpdate()
TextBox10 = Format(TextBox10, "#,##0.00")
lblOther.Caption = Format(CDbl(TextBox10.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox11_AfterUpdate()
TextBox11 = Format(TextBox11, "#,##0.00")
lblAccounting.Caption = Format(CDbl(TextBox11.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox12_AfterUpdate()
TextBox12 = Format(TextBox12, "#,##0.00")
lblOthers.Caption = Format(CDbl(TextBox12.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox13_AfterUpdate()
TextBox13 = Format(TextBox13, "#,##0.00")
lblRoyalties.Caption = Format(CDbl(TextBox13.Text) / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox15_AfterUpdate()
TextBox15 = Format(TextBox15, "#,##0.00")
TextBox15 = Format(TextBox1, TextBox2, TextBox3, TextBox4, "#,##0.00")
End Sub
Private Sub TextBox16_AfterUpdate()
TextBox16 = Format(TextBox16, "#,##0.00")
End Sub
Private Sub TextBox17_AfterUpdate()
TextBox17 = Format(TextBox17, "#,##0.00")
End Sub

I would like to sum textbox15 with all the numbers FROM TextBox1 to TextBox14 and the total of this in TextBox15. So basically to sum them all

is there a quicker way other than using TextBox1 + Textbox2 + Textbox3 and so on

Thanking you for your help

Darren
South Africa

Bob Phillips
08-20-2007, 02:02 PM
Yeah that was a typo.

Other bit



For i = 1 to 14
tmp=tmp + Controls("TextBox" & i).Text
Next i
TextBox15.Text = tmp


You may need to cast the textbox values (CDbl)

Darren
08-20-2007, 02:38 PM
Hi Xld

Not sure on the new instruction. as above. do I place this code in TextBox15 after change event. or in textbox15 after_update

Sorry maybe you misunderstood me .... need to sum all textBoxes 1 to 14

the total of all these text boxes to appear in Textbox15
attached snapshot

TIA
Darren

Bob Phillips
08-20-2007, 02:44 PM
There would seem to be no point in putting it in TextBox15 Change or AfterUpdate events, as you shouldn't be entering directly into TextBox15 if you want that to store the other cells.

You need some event to do it, maybe a Total button or something, which would then run that code.

mdmackillop
08-20-2007, 11:46 PM
Will Textbox14 always have data? If so, how about placing it as a Change or AfterUpdate for that control.

Bob Phillips
08-21-2007, 12:17 AM
I would create a small routine that does the adding of all the boxes, checking for blanks as CDbl fails on them, and call that routine from the Change or AfterUpdate of all of boxes 1 to 14.

Darren
08-21-2007, 05:16 AM
Hi Masters

Thanks for the replys

As Malcolm says

"Provide sample data and layout if you want a quicker solution." - MD

In our previous calculators it would update the total automatically. Is it because it is all text boxes ?

I tried adding a Button Control as suggested by XLD and it worked but instead of adding the text boxes together to give one total sum it listed them side by side for emample when clicking total the result would be 1164,00,2187.00,4533.00, and so on.

I provided a complete spreadsheet with working formulas and the complete userform. Not so keen on the "Press to Total button" it would be so much better to sort of auto update TextBox15 as the Textboxes 1 to 13 are filled in (sorry not 1 to 14)

I trust you can assist

Darren
South Africa

Bob Phillips
08-21-2007, 06:17 AM
Private Sub UserForm_Initialize()
Dim Gross
Gross = Range("I10")
lblBasicInFormulas = Format(Gross, "#,##0.00")
TextBox14 = Format(Gross, "#,##0.00")
ITurnover = Format(Gross, "#,##0.00")

End Sub
Private Sub TextBox1_AfterUpdate()
TextBox1 = Format(TextBox1, "#,##0.00")
lblElectr.Caption = Format(CDbl(TextBox1.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub

Private Sub TextBox2_AfterUpdate()
TextBox2 = Format(TextBox2, "#,##0.00")
lblRent.Caption = Format(CDbl(TextBox2.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox3_AfterUpdate()
TextBox3 = Format(TextBox3, "#,##0.00")
lblStaffSal.Caption = Format(CDbl(TextBox3.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox4_AfterUpdate()
TextBox4 = Format(TextBox4, "#,##0.00")
lblManageSal.Caption = Format(CDbl(TextBox4.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox5_AfterUpdate()
TextBox5 = Format(TextBox5, "#,##0.00")
lblInsurance.Caption = Format(CDbl(TextBox5.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox6_AfterUpdate()
TextBox6 = Format(TextBox6, "#,##0.00")
lblAdvertis.Caption = Format(CDbl(TextBox6.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox7_AfterUpdate()
TextBox7 = Format(TextBox7, "#,##0.00")
lblCleaning.Caption = Format(CDbl(TextBox7.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox8_AfterUpdate()
TextBox8 = Format(TextBox8, "#,##0.00")
lblSecurity.Caption = Format(CDbl(TextBox8.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox9_AfterUpdate()
TextBox9 = Format(TextBox9, "#,##0.00")
lblTelephone.Caption = Format(CDbl(TextBox9.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox10_AfterUpdate()
TextBox10 = Format(TextBox10, "#,##0.00")
lblOther.Caption = Format(CDbl(TextBox10.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox11_AfterUpdate()
TextBox11 = Format(TextBox11, "#,##0.00")
lblAccounting.Caption = Format(CDbl(TextBox11.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox12_AfterUpdate()
TextBox12 = Format(TextBox12, "#,##0.00")
lblOthers.Caption = Format(CDbl(TextBox12.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox13_AfterUpdate()
TextBox13 = Format(TextBox13, "#,##0.00")
lblRoyalties.Caption = Format(CDbl(TextBox13.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox14_AfterUpdate()
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub

Private Sub TextBox15_AfterUpdate()
TextBox15.Text = Format(TextBox15.Text, "#,##0.00")
End Sub
Private Sub TextBox16_AfterUpdate()
TextBox16.Text = Format(TextBox16.Text, "#,##0.00")
End Sub
Private Sub TextBox17_AfterUpdate()
TextBox17.Text = Format(TextBox17.Text, "#,##0.00")
End Sub


Private Function SumOverheads() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 14
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumOverheads = tmp
End Function

Darren
08-21-2007, 08:56 AM
Hi XLD

Thank you once again will try and advise you shortly

Darren
South Africa

Darren
08-21-2007, 12:20 PM
Hi Xld

thanks for your help and testing to achive the same goal and understanding. The code works well.

I will need to read a little about backspace to enter new value i get a run time error type mismatch. You fixed that in one of the earlier calculators so wil investigate.

I will need to sum the percentage and i will try the same procedure for each lbltext.caption is there much difference between textbox and lablel ?

Thanks for your time Bob
Darren
South Africa

mdmackillop
08-21-2007, 01:03 PM
You/the user can enter values in a textbox (unless locked). Labels will show results. You can format a label to look like a textbox for presentation purposes though.

Bob Phillips
08-21-2007, 01:23 PM
The problem seems to lie with trying to populate the listboxes when the source (a textbox) is blank due to backspacing, and then exiting.

s I said earlier, CDbl on a blank value errors out, so this



Private Sub TextBox4_AfterUpdate()
TextBox4 = Format(TextBox4, "#,##0.00")
lblManageSal.Caption = Format(CDbl(TextBox4.Text) / CDbl(TextBox14.Text), "0.00%")
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub


will errro if TextBox 4 or 14 is blank.

Test it



Private Sub TextBox4_AfterUpdate()
TextBox4 = Format(TextBox4, "#,##0.00")
If TextBox4.Text <> "" And TextBox14.Text <> "" Then
lblManageSal.Caption = Format(CDbl(TextBox4.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub


and similar elswhere

Darren
08-21-2007, 01:36 PM
Hi Captains

Late in Africa will aplly the instructions, fresh in the morning and advise you, thank you from the heart for the help...

Darren
South Africa

Bob Phillips
08-21-2007, 03:32 PM
Think nothing of it, I am just chalking up my helicopter credits <bg>

geekgirlau
08-21-2007, 05:39 PM
:2p: Can I suggest that you think about the names for your text boxes and labels? It is much simpler to follow if the name describes the data that is displayed. You need to think about trying to decipher this text a year (or 5) down the track, or passing it on to someone else to support.

In this instance, you can still use a numbering sequence that enables you to easily add the results - for example, "txtRand1", "txtRand2" ... "txtTotalRand".

Bob Phillips
08-22-2007, 12:02 AM
Whilst I agree with the principle, and I think Malcolm made the same point to Darren earlier, txtRand1, txtRand2, etc. seems little better that TextBox1, 2,3 ... <BG>

geekgirlau
08-22-2007, 12:22 AM
It's WAAAAYYY better if you also have 522 other text boxes on your form unrelated to Rands :devil2: ... it's a good idea to get used to consistently using some sort of naming convention.

rory
08-22-2007, 02:06 AM
Darren,
If you use Val rather than CDbl it will treat blank text as 0 (you will still need to check for divide by zero errors though!)
Also, the total of your percentages should always be 100% since the percentages are calculated by dividing each textbox by the sum of all textboxes.

Darren
08-22-2007, 02:23 AM
Hi Masters

I have taken all the post above and have noted them. I will apply them in my next calculator.

Xld i have applied the ammended code to all the textBoxes and it works like a charm no more "type error" Mismatch problems.

I will now attempt to Sum the Purple coloum which are lblElectr , lblRent and so on to give the total percentage of turnover based on what i have learned here. I will apply a new line of code to each Textbox to sum the lblBoxes.caption. will let you know how it goes i need to try!

Thank you all

Darren
South Africa

Darren
08-23-2007, 12:12 PM
Hi Masters

Not winning in totaling the purple coloum to a percentage. I can get it to display the same as TextBox15 but it displays as a Number and not a percentage my current code to folllow

Private Sub UserForm_Initialize()
Dim Gross
Gross = Range("I10")
lblBasicInFormulas = Format(Gross, "#,##0.00")
TextBox14 = Format(Gross, "#,##0.00")
ITurnover = Format(Gross, "#,##0.00")

End Sub
Private Sub TextBox1_AfterUpdate()
TextBox1 = Format(TextBox1, "#,##0.00")
If TextBox1.Text <> "" And TextBox14.Text <> "" Then
ElectrLabel1.Caption = Format(CDbl(TextBox1.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
With SumPercent.Text = Format(SumPercent1, "0.00%")
End With
End Sub

Private Sub TextBox2_AfterUpdate()
TextBox2 = Format(TextBox2, "#,##0.00")
If TextBox2.Text <> "" And TextBox14.Text <> "" Then
Rentlabel2.Caption = Format(CDbl(TextBox2.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
With SumPercent.Text = Format(SumPercent1, "0.00%")
End With
End Sub
Private Sub TextBox3_AfterUpdate()
TextBox3 = Format(TextBox3, "#,##0.00")
If TextBox3.Text <> "" And TextBox14.Text <> "" Then
lblStaffSal3.Caption = Format(CDbl(TextBox3.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox4_AfterUpdate()
TextBox4 = Format(TextBox4, "#,##0.00")
If TextBox4.Text <> "" And TextBox14.Text <> "" Then
lblManageSal.Caption = Format(CDbl(TextBox4.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")

End Sub
Private Sub TextBox5_AfterUpdate()
TextBox5 = Format(TextBox5, "#,##0.00")
If TextBox5.Text <> "" And TextBox14.Text <> "" Then
lblInsurance.Caption = Format(CDbl(TextBox5.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox6_AfterUpdate()
TextBox6 = Format(TextBox6, "#,##0.00")
If TextBox6.Text <> "" And TextBox14.Text <> "" Then
lblAdvertis.Caption = Format(CDbl(TextBox6.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox7_AfterUpdate()
TextBox7 = Format(TextBox7, "#,##0.00")
If TextBox7.Text <> "" And TextBox14.Text <> "" Then
lblCleaning.Caption = Format(CDbl(TextBox7.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox8_AfterUpdate()
TextBox8 = Format(TextBox8, "#,##0.00")
If TextBox8.Text <> "" And TextBox14.Text <> "" Then
lblSecurity.Caption = Format(CDbl(TextBox8.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox9_AfterUpdate()
TextBox9 = Format(TextBox9, "#,##0.00")
If TextBox9.Text <> "" And TextBox14.Text <> "" Then
lblTelephone.Caption = Format(CDbl(TextBox9.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox10_AfterUpdate()
TextBox10 = Format(TextBox10, "#,##0.00")
If TextBox10.Text <> "" And TextBox14.Text <> "" Then
lblOther.Caption = Format(CDbl(TextBox10.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox11_AfterUpdate()
TextBox11 = Format(TextBox11, "#,##0.00")
If TextBox11.Text <> "" And TextBox14.Text <> "" Then
lblAccounting.Caption = Format(CDbl(TextBox11.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox12_AfterUpdate()
TextBox12 = Format(TextBox12, "#,##0.00")
If TextBox12.Text <> "" And TextBox14.Text <> "" Then
lblothers.Caption = Format(CDbl(TextBox12.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox13_AfterUpdate()
TextBox13 = Format(TextBox13, "#,##0.00")
If TextBox13.Text <> "" And TextBox14.Text <> "" Then
lblRoyalties.Caption = Format(CDbl(TextBox13.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub SumPercent_AfterUpdate()
SumPercent.Text = Format(SumPercent, "0.00%")
End Sub

Private Sub TextBox15_AfterUpdate()
TextBox15.Text = Format(TextBox15.Text, "#,##0.00")
End Sub
Private Sub TextBox16_AfterUpdate()
TextBox16.Text = Format(TextBox16.Text, "#,##0.00")
End Sub
Private Sub TextBox17_AfterUpdate()
TextBox17.Text = Format(TextBox17.Text, "#,##0.00")
End Sub
Private Function SumOverheads() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 13
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumOverheads = tmp
End Function
Private Function SumPercent1() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 13
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumPercent = tmp
End Function

Hope you can help me

mdmackillop
08-23-2007, 12:54 PM
A typo in the last line of the last function :rant:
SumPercent1 = tmp

Bob Phillips
08-23-2007, 01:09 PM
Private Sub UserForm_Initialize()
Dim Gross
Gross = Range("I10")
lblBasicInFormulas = Format(Gross, "#,##0.00")
TextBox14 = Format(Gross, "#,##0.00")
ITurnover = Format(Gross, "#,##0.00")

End Sub
Private Sub TextBox1_AfterUpdate()
TextBox1 = Format(TextBox1, "#,##0.00")
If TextBox1.Text <> "" And TextBox14.Text <> "" Then
ElectrLabel1.Caption = Format(CDbl(TextBox1.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub

Private Sub TextBox2_AfterUpdate()
TextBox2 = Format(TextBox2, "#,##0.00")
If TextBox2.Text <> "" And TextBox14.Text <> "" Then
Rentlabel2.Caption = Format(CDbl(TextBox2.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox3_AfterUpdate()
TextBox3 = Format(TextBox3, "#,##0.00")
If TextBox3.Text <> "" And TextBox14.Text <> "" Then
lblStaffSal3.Caption = Format(CDbl(TextBox3.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox4_AfterUpdate()
TextBox4 = Format(TextBox4, "#,##0.00")
If TextBox4.Text <> "" And TextBox14.Text <> "" Then
lblManageSal.Caption = Format(CDbl(TextBox4.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox5_AfterUpdate()
TextBox5 = Format(TextBox5, "#,##0.00")
If TextBox5.Text <> "" And TextBox14.Text <> "" Then
lblInsurance.Caption = Format(CDbl(TextBox5.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox6_AfterUpdate()
TextBox6 = Format(TextBox6, "#,##0.00")
If TextBox6.Text <> "" And TextBox14.Text <> "" Then
lblAdvertis.Caption = Format(CDbl(TextBox6.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox7_AfterUpdate()
TextBox7 = Format(TextBox7, "#,##0.00")
If TextBox7.Text <> "" And TextBox14.Text <> "" Then
lblCleaning.Caption = Format(CDbl(TextBox7.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox8_AfterUpdate()
TextBox8 = Format(TextBox8, "#,##0.00")
If TextBox8.Text <> "" And TextBox14.Text <> "" Then
lblSecurity.Caption = Format(CDbl(TextBox8.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox9_AfterUpdate()
TextBox9 = Format(TextBox9, "#,##0.00")
If TextBox9.Text <> "" And TextBox14.Text <> "" Then
lblTelephone.Caption = Format(CDbl(TextBox9.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox10_AfterUpdate()
TextBox10 = Format(TextBox10, "#,##0.00")
If TextBox10.Text <> "" And TextBox14.Text <> "" Then
lblOther.Caption = Format(CDbl(TextBox10.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox11_AfterUpdate()
TextBox11 = Format(TextBox11, "#,##0.00")
If TextBox11.Text <> "" And TextBox14.Text <> "" Then
lblAccounting.Caption = Format(CDbl(TextBox11.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox12_AfterUpdate()
TextBox12 = Format(TextBox12, "#,##0.00")
If TextBox12.Text <> "" And TextBox14.Text <> "" Then
lblothers.Caption = Format(CDbl(TextBox12.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox13_AfterUpdate()
TextBox13 = Format(TextBox13, "#,##0.00")
If TextBox13.Text <> "" And TextBox14.Text <> "" Then
lblRoyalties.Caption = Format(CDbl(TextBox13.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub SumPercent_AfterUpdate()
SumPercent.Text = Format(SumPercent, "0.00%")
End Sub

Private Sub TextBox15_AfterUpdate()
TextBox15.Text = Format(TextBox15.Text, "#,##0.00")
End Sub
Private Sub TextBox16_AfterUpdate()
TextBox16.Text = Format(TextBox16.Text, "#,##0.00")
End Sub
Private Sub TextBox17_AfterUpdate()
TextBox17.Text = Format(TextBox17.Text, "#,##0.00")
End Sub
Private Function SumOverheads() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 13
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumOverheads = tmp
End Function
Private Function SumPercent1() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 13
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumPercent1 = tmp
End Function

Darren
08-23-2007, 03:26 PM
Hi Masters
Both malcolm and xld hmmmm I tried both suggestions.

I applied a 1 as per Malc's suggestion and the result is nothing appears in the SumPercent Box.

If i apply the code Bob posted i get the same result hmmm Bob i see you removed With statement from code i posted. Still learning so thanks for that but the result is still not correct.

All the Purple boxes are labels and not TextBoxes will this make a difference to creating a sum of them ?

could the problem lie with "With me.Contols("TextBox" & i) ??
not sure guys i tried "label" but no joy.

thanking you for your help

Darren
South Africa

Bob Phillips
08-23-2007, 03:41 PM
Well I am sure I don't know why you get that, it works fine for me. If I put 100 in box1, I get .5% in the first and final purple, if I then put 200 in box 2 I get 1% in the second purple and 1.5% in the final, and so on.

Darren
08-23-2007, 03:57 PM
Hi Bob

Not sure as well i copied and pasted your code as given and this is my screen shot of the result. Next to Total Overheads i get a blank Purple box this box should total the percentage as it is collected by inputs from the user?

If i leave out the 1

Private Function SumPercent1() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 13
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumPercent1 = tmp
End Function


in this code it list the amount but not as a percentage of the amount

darren

Darren
08-23-2007, 04:03 PM
Hi Bob

Code as you sent it to me earlier

Private Sub UserForm_Initialize()
Dim Gross
Gross = Range("I10")
lblBasicInFormulas = Format(Gross, "#,##0.00")
TextBox14 = Format(Gross, "#,##0.00")
ITurnover = Format(Gross, "#,##0.00")

End Sub
Private Sub TextBox1_AfterUpdate()
TextBox1 = Format(TextBox1, "#,##0.00")
If TextBox1.Text <> "" And TextBox14.Text <> "" Then
ElectrLabel1.Caption = Format(CDbl(TextBox1.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
With SumPercent.Text = Format(SumPercent1, "0.00%")
End With
End Sub

Private Sub TextBox2_AfterUpdate()
TextBox2 = Format(TextBox2, "#,##0.00")
If TextBox2.Text <> "" And TextBox14.Text <> "" Then
Rentlabel2.Caption = Format(CDbl(TextBox2.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
With SumPercent.Text = Format(SumPercent1, "0.00%")
End With
End Sub
Private Sub TextBox3_AfterUpdate()
TextBox3 = Format(TextBox3, "#,##0.00")
If TextBox3.Text <> "" And TextBox14.Text <> "" Then
lblStaffSal3.Caption = Format(CDbl(TextBox3.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox4_AfterUpdate()
TextBox4 = Format(TextBox4, "#,##0.00")
If TextBox4.Text <> "" And TextBox14.Text <> "" Then
lblManageSal.Caption = Format(CDbl(TextBox4.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")

End Sub
Private Sub TextBox5_AfterUpdate()
TextBox5 = Format(TextBox5, "#,##0.00")
If TextBox5.Text <> "" And TextBox14.Text <> "" Then
lblInsurance.Caption = Format(CDbl(TextBox5.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox6_AfterUpdate()
TextBox6 = Format(TextBox6, "#,##0.00")
If TextBox6.Text <> "" And TextBox14.Text <> "" Then
lblAdvertis.Caption = Format(CDbl(TextBox6.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox7_AfterUpdate()
TextBox7 = Format(TextBox7, "#,##0.00")
If TextBox7.Text <> "" And TextBox14.Text <> "" Then
lblCleaning.Caption = Format(CDbl(TextBox7.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox8_AfterUpdate()
TextBox8 = Format(TextBox8, "#,##0.00")
If TextBox8.Text <> "" And TextBox14.Text <> "" Then
lblSecurity.Caption = Format(CDbl(TextBox8.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox9_AfterUpdate()
TextBox9 = Format(TextBox9, "#,##0.00")
If TextBox9.Text <> "" And TextBox14.Text <> "" Then
lblTelephone.Caption = Format(CDbl(TextBox9.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox10_AfterUpdate()
TextBox10 = Format(TextBox10, "#,##0.00")
If TextBox10.Text <> "" And TextBox14.Text <> "" Then
lblOther.Caption = Format(CDbl(TextBox10.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox11_AfterUpdate()
TextBox11 = Format(TextBox11, "#,##0.00")
If TextBox11.Text <> "" And TextBox14.Text <> "" Then
lblAccounting.Caption = Format(CDbl(TextBox11.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox12_AfterUpdate()
TextBox12 = Format(TextBox12, "#,##0.00")
If TextBox12.Text <> "" And TextBox14.Text <> "" Then
lblothers.Caption = Format(CDbl(TextBox12.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub TextBox13_AfterUpdate()
TextBox13 = Format(TextBox13, "#,##0.00")
If TextBox13.Text <> "" And TextBox14.Text <> "" Then
lblRoyalties.Caption = Format(CDbl(TextBox13.Text) / CDbl(TextBox14.Text), "0.00%")
End If
TextBox15.Text = Format(SumOverheads, "#,##0.00")
End Sub
Private Sub SumPercent_AfterUpdate()
SumPercent.Text = Format(SumPercent, "0.00%")
End Sub

Private Sub TextBox15_AfterUpdate()
TextBox15.Text = Format(TextBox15.Text, "#,##0.00")
End Sub
Private Sub TextBox16_AfterUpdate()
TextBox16.Text = Format(TextBox16.Text, "#,##0.00")
End Sub
Private Sub TextBox17_AfterUpdate()
TextBox17.Text = Format(TextBox17.Text, "#,##0.00")
End Sub
Private Function SumOverheads() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 13
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumOverheads = tmp
End Function
Private Function SumPercent1() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 13
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumPercent1 = tmp
End Function

This is the code i am using'

Darren

Darren
08-23-2007, 04:07 PM
Bob

Suggestion send you file that works to me so i can compare apples with apples ??

Darren

Darren
08-23-2007, 04:14 PM
Sorry Bob

My mistake copy and paste error Captain

Darren:friends: :beerchug: :bow: :bow: :bow: :bow: :bow: :bow: :bow:

Darren
08-23-2007, 04:22 PM
Hi Bob

for others

Lesson when copying and pasting code to your project ..... if you delete the total code to paste the correct soloution delete your code and save the file then paste the new code received....

Darren
now it works like a dream thanks xld

Bob Phillips
08-24-2007, 12:30 AM
I am so glad about that, as I couldn't think what the problem was.