Solved: Leave TextBoxes Blank

[sooty8]

Hi All
What is the best way to alter the sub below so that if no value is required in a TextBox I can just ignore it ( leave it blank) -- at the moment only if I enter a 0 (zero) does everything total correctly.


Private Sub Add6_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Entrants")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
On Error Resume Next
ws.Cells(iRow, 1).Value = " " & Trim(UserForm2.Tb2.Value) + " " & Trim(UserForm2.Tb1.Value)
ws.Cells(iRow, 2).Value = Me.Tb3.Value
ws.Cells(iRow, 3).Value = Me.Tb4.Value
ws.Cells(iRow, 4).Value = Me.Tb5.Value * 5#
ws.Cells(iRow, 5).Value = Me.Tb6.Value * 0.5
ws.Cells(iRow, 6).Value = Me.Tb7.Value * 1#
ws.Cells(iRow, 7).Value = Me.Tb8.Value * 2#
ws.Cells(iRow, 8).Value = Me.Tb9.Value * 3#
ws.Cells(iRow, 9).Value = Me.Tb10.Value * 5#
ws.Cells(iRow, 10).Value = Me.Tb11.Value * 1#
ws.Cells(iRow, 11).Value = Format(Val(Trim(Tb5.Value * 5#))) + (Val(Trim(Tb6.Value * 0.5))) + (Val(Trim(Tb7.Value * 1#))) + (Val(Trim(Tb8.Value * 2#))) + (Val(Trim(Tb9.Value * 3#))) + (Val(Trim(Tb10.Value * 5#))) + (Val(Trim(Tb11.Value * 1#)))

'Clear Data
Me.Tb4.Value = ""
Me.Tb5.Value = ""
Me.Tb6.Value = ""
Me.Tb7.Value = ""
Me.Tb8.Value = ""
Me.Tb9.Value = ""
Me.Tb10.Value = ""
Me.Tb11.Value = ""
End Sub

Any help much appreciated

Regards

Sooty 8

[Simon Lloyd]

Sooty when posting code please remember to add the VBA code tags, to do this highlight your code and click the green VBA button.

[Bob Phillips]

Is this what you mean?



ws.Cells(iRow, 11).Value = Val(Trim(Tb5.Value)) * 5# + _
Val(Trim(Tb6.Value)) * 0.5 + _
Val(Trim(Tb7.Value)) * 1# + _
Val(Trim(Tb8.Value)) * 2# + _
Val(Trim(Tb9.Value)) * 3#+ _
Val(Trim(Tb10.Value)) * 5# + _
Val(Trim(Tb11.Value)) * 1#

[sooty8]

Hi Xld

Once again the perfect answer - can you explain to a simple mind why yours works spot on -- and mine don't

Many Thanks

Sooty8