Userform VBA Calculations

[wrightyrx7]

Hi All,

I am creating a userform and I want the following calculations to show once it has the information in. And also the calculations cannot be typed over.

Textbox19 = textbox18 / 100*95 (Want 95% of textbox19)
Textbox20 = textbox19 * texbox17

The vba i have is as follows:

Private Sub cmdAdd_waiting_list_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Waiting List")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.TextBox3.Value
ws.Cells(iRow, 4).Value = Me.TextBox13.Value
ws.Cells(iRow, 5).Value = Me.TextBox11.Value
ws.Cells(iRow, 6).Value = Me.ComboBox1.Value
ws.Cells(iRow, 7).Value = Me.TextBox4.Value
ws.Cells(iRow, 8).Value = Me.TextBox5.Value
ws.Cells(iRow, 9).Value = Me.TextBox6.Value
ws.Cells(iRow, 10).Value = Me.TextBox7.Value
ws.Cells(iRow, 11).Value = Me.TextBox8.Value
ws.Cells(iRow, 12).Value = Me.TextBox9.Value
ws.Cells(iRow, 13).Value = Me.TextBox10.Value
ws.Cells(iRow, 14).Value = Me.TextBox12.Value
ws.Cells(iRow, 15).Value = Me.TextBox15.Value
ws.Cells(iRow, 16).Value = Me.TextBox16.Value
ws.Cells(iRow, 17).Value = Me.TextBox14.Value
ws.Cells(iRow, 18).Value = Me.TextBox17.Value
ws.Cells(iRow, 19).Value = Me.TextBox18.Value
ws.Cells(iRow, 20).Value = Me.TextBox19.Value
ws.Cells(iRow, 21).Value = Me.TextBox20.Value
ws.Cells(iRow, 22).Value = Me.TextBox25.Value
ws.Cells(iRow, 23).Value = Me.TextBox24.Value
ws.Cells(iRow, 24).Value = Me.TextBox21.Value
ws.Cells(iRow, 25).Value = Me.TextBox22.Value
ws.Cells(iRow, 26).Value = Me.TextBox23.Value


'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox15.Value = ""
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Me.TextBox21.Value = ""
Me.TextBox22.Value = ""
Me.TextBox23.Value = ""
Me.TextBox24.Value = ""
Me.TextBox25.Value = ""
Me.ComboBox1.Value = "Unknown"
Me.TextBox1.SetFocus

End Sub

Thanks in advance
Chris

 

[tweedle]

Something like this should work

Private Sub TextBox17_Change()
    TextBox18_Change
End Sub
Private Sub TextBox18_Change()
TextBox19.Enabled = False
TextBox20.Enabled = False
If IsNumeric(TextBox18.Value) Then
    TextBox19 = TextBox18.Value * 0.95
End If
If IsNumeric(TextBox17.Value) And _
    IsNumeric(TextBox19.Value) Then
    TextBox20 = TextBox19.Value * TextBox17.Value
End If
End Sub

 

[wrightyrx7]

That is PERFECT!! thank you very much.

Do you know how how to put example data into the Textbox but when the textbox is clicked/tabbed into it dissapears? Lets say the date textbox for example to have dd/mm/yyyy in it.

Thanks again
Chris

 

[tweedle]

That's a two-part answer

Initiate:

Private Sub UserForm_Initialize()
    Me.TextBox1.Text = Format(Date, "mm/dd/yyyy")
End Sub

Clear on control-select

Private Sub TextBox1_Enter()
    TextBox1 = ""
End Sub

 

[wrightyrx7]

Part 1 was fine, how do i input the clear on control-select part?

Thanks
Chris

 

[tweedle]

whatever textbox you're wanting this to act on
go to it's code and the _Enter event

then yourtextboxname = ""

 

[wrightyrx7]

Ahhh right ok thank you will try it in the morning, turned the computer off for the night now.

Thanks again tweedle
Chris

 

[wrightyrx7]

Works perfect thanks again Tweedle

 

[tweedle]

good good, thanks for the post-back

 

[tewellt]

I copied your code and modified the text boxes to reflect my field name (txt_tot_Taxes). When I try to Compile the VB I get the following error message "Compile error: Invalid use of Me keyword." I am using Excel 2013.

Private Sub cmb_add_Click() 'refers to Add button on userform
Dim erow As Integer
Dim ws As Worksheet
Set ws = Worksheets("Data")


erow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Me.txt_Date.SetFocus


ws.Cells(erow, 1).Value = Me.txt_Date.Value 'For date formatting: Cells(erow, 1).Value = Format(Date, "mm/dd/yy")
ws.Cells(erow, 2).Value = Me.txt_City.Value
ws.Cells(erow, 3).Value = Me.txt_State.Value
ws.Cells(erow, 4).Value = Me.txt_Location.Value
ws.Cells(erow, 5).Value = Me.txt_TotSpent.Value
ws.Cells(erow, 6).Value = Me.txt_nf_tax.Value
ws.Cells(erow, 7).Value = Me.txt_food_tax.Value
ws.Cells(erow, 8).Value = Me.txt_tlt_taxes.Value
ws.Cells(erow, 8).Value = Me.Opt_Gas.Value


'Clear the data once loaded unto the spreadsheet


Me.txt_Date.Value = ""
Me.txt_City.Value = ""
Me.txt_State.Value = ""
Me.txt_Location.Value = ""
Me.txt_TotSpent.Value = ""
Me.txt_nf_tax.Value = ""
Me.txt_food_tax.Value = ""
Me.txt_tlt_taxes.Value = ""
Me.txt_Date.SetFocus


End Sub