Darren
09-23-2007, 03:52 PM
Hi Captains
I have searched the knowledge base and not found an answer
I have a userform called Overheads it has 15 TextBoxes that can be completed. I would like to save this information that is in the TextBoxes by the use of a CommandButton14_click.
Is it possible to save the OverHeads Useform as is or will the data have to be extracted first from each TextBox ? If so then we would need to save the label attached to that TextBox (Please see attached image.) or we would just have numbers on a page.
Can it be saved to a file called C:\Overhead and if this file does not exist on the C:\ to then create it and save?
It can be saved as note pad document or HTML thats fine
This is the code page for OverHeads Userform
Private Sub CommandButton1_Click()
Hide
BasicTerms.Show
End Sub
Private Sub CommandButton10_Click()
OtherExpenses.Show
End Sub
Private Sub CommandButton11_Click()
Hide
HelpFile3.Show
End Sub
Private Sub CommandButton14_Click()
End Sub
Private Sub CommandButton2_Click()
Hide
HelpFile2.Show
End Sub
Private Sub CommandButton3_Click()
Hide
SalesTaxCalc.Show
End Sub
Private Sub CommandButton4_Click()
Hide
PromoCalc.Show
End Sub
Private Sub CommandButton5_Click()
Me.PrintForm
End Sub
Private Sub CommandButton6_Click()
Hide
Summary.Show
End Sub
Private Sub CommandButton7_Click()
Hide
DailyControl.Show
End Sub
Private Sub CommandButton8_Click()
Hide
NewVenture.Show
End Sub
Private Sub CommandButton9_Click()
Hide
StockTake.Show
End Sub
Private Sub UserForm_Activate()
TextBox14.Text = BasicTerms.lblTurnoverExcl.Caption
TextBox13.Text = BasicTerms.lblRoyaltcost.Caption
TextBox13_AfterUpdate
TextBox1.SetFocus
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")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "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")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "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
TextBox10 = OtherExpenses.TextBox27
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox10_Change()
TextBox10.Text = Format(TextBox10.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%")
If SumPercent.Text <> "" Then
End If
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")
If TheBasicFormulas.EGrossProfit.Text <> "" Then
TextBox16.Text = TheBasicFormulas.EGrossProfit.Text
End If
End Sub
Private Sub TextBox17_AfterUpdate()
TextBox17.Text = Format(TextBox17.Text, "#,##0.00")
If TheBasicFormulas.EGrossProfit.Text <> "" Then
TextBox17.Text = TheBasicFormulas.DNettProfit.Text
End If
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
Thanking you all and God Bless
Darren
South Africa:hi:
I have searched the knowledge base and not found an answer
I have a userform called Overheads it has 15 TextBoxes that can be completed. I would like to save this information that is in the TextBoxes by the use of a CommandButton14_click.
Is it possible to save the OverHeads Useform as is or will the data have to be extracted first from each TextBox ? If so then we would need to save the label attached to that TextBox (Please see attached image.) or we would just have numbers on a page.
Can it be saved to a file called C:\Overhead and if this file does not exist on the C:\ to then create it and save?
It can be saved as note pad document or HTML thats fine
This is the code page for OverHeads Userform
Private Sub CommandButton1_Click()
Hide
BasicTerms.Show
End Sub
Private Sub CommandButton10_Click()
OtherExpenses.Show
End Sub
Private Sub CommandButton11_Click()
Hide
HelpFile3.Show
End Sub
Private Sub CommandButton14_Click()
End Sub
Private Sub CommandButton2_Click()
Hide
HelpFile2.Show
End Sub
Private Sub CommandButton3_Click()
Hide
SalesTaxCalc.Show
End Sub
Private Sub CommandButton4_Click()
Hide
PromoCalc.Show
End Sub
Private Sub CommandButton5_Click()
Me.PrintForm
End Sub
Private Sub CommandButton6_Click()
Hide
Summary.Show
End Sub
Private Sub CommandButton7_Click()
Hide
DailyControl.Show
End Sub
Private Sub CommandButton8_Click()
Hide
NewVenture.Show
End Sub
Private Sub CommandButton9_Click()
Hide
StockTake.Show
End Sub
Private Sub UserForm_Activate()
TextBox14.Text = BasicTerms.lblTurnoverExcl.Caption
TextBox13.Text = BasicTerms.lblRoyaltcost.Caption
TextBox13_AfterUpdate
TextBox1.SetFocus
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")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "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")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "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
TextBox10 = OtherExpenses.TextBox27
TextBox15.Text = Format(SumOverheads, "#,##0.00")
SumPercent.Text = Format(SumPercent1 / CDbl(TextBox14.Text), "0.00%")
End Sub
Private Sub TextBox10_Change()
TextBox10.Text = Format(TextBox10.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%")
If SumPercent.Text <> "" Then
End If
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")
If TheBasicFormulas.EGrossProfit.Text <> "" Then
TextBox16.Text = TheBasicFormulas.EGrossProfit.Text
End If
End Sub
Private Sub TextBox17_AfterUpdate()
TextBox17.Text = Format(TextBox17.Text, "#,##0.00")
If TheBasicFormulas.EGrossProfit.Text <> "" Then
TextBox17.Text = TheBasicFormulas.DNettProfit.Text
End If
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
Thanking you all and God Bless
Darren
South Africa:hi: