sooty8
06-11-2008, 11:25 AM
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
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