Userform Save with contents of TextBoxes

[Darren]

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:

[lucas]

Hi Darren,
I'm wondering if you specifically need to save a text or html or if you would be just as well off to save the data to a preformatted excel spreadsheet? I may not fully understand what your are trying to do.

[Darren]

Hi Lucas

Thank you for your prompt reply. The image you see is the userform called Overheads i would like to save it as is if possible to view at a later time lets say next month end and compare the the expenses on electricity and so on. If we save as Overheads.frm will excel open it again and allow changes? i would not like that to happen, hmmm it must be saved as is on clicking save so to say like a snapshot.

hope this helped Lucas

Darren
South Africa

[lucas]

If we save as Overheads.frm will excel open it again and allow changes?
I think you will find that your data will be missing.

There are several ways you can do this. The quickest since you seem to want to see the form would be to hit alt and print screen and then open a graphics program and paste as a new image.

Practically as an excel function I would think you would wish to put the data in a new sheet that is a copy of a template and if necessary save that sheet as a new workbook name derived from the data such as a date. Then the copy of the template sheet can be deleted from the original workbook.

The template would already have your headings and non-variable information so.....just my thoughts. Maybe other members will have a better idea.

[lucas]

Hi Darren,
Simple knocked up example for you. Creates a new sheet each time you click the button on the form...this one only has two textboxes to show you how it might work.

Template can be formatted any way you want it to look and will be reused each time so all sheets copied from it will look just like it for easy comparison. Template sheet can be hidden

Run it a couple of times with different sheet names to compare.

[Darren]

Hi Lucas

Thanks i will give it a Bash will let you know.

Darren