Hi all.
I have a spreadsheet for weekly time sheets. It has a sheet for each day of the week that the user fill his time and other job descriptions in on a daily basis. This is then converted with formulas to a summary sheet. Now they wants a additional monthly summary sheet for all the overtime worked that the manager must sign off on.
I created this new Over Time Summary Sheet and it is in a table format ("TblOTSummary") with a total row at the bottom, and the manager's motivation and signature line is below the table.
I have created a userform for the input of each column in the Over Time Summary table with a macro button to call this userform in each of the daily sheets. I had the code working to add a new row to the table and inserting all the text boxes to it's respective column in the table before I added the total row and the manager's motivation and signature lines.
Also when I wanted to run the code from one of the daily sheets, there was some kind of error as well. (I tried so many things since then, that I cant remember the error message.)
Now I don't get that error message again, but the table row are added but the information is not entered into the last blank row of the table.
Some help with my code will be highly appreciated.
I have a spreadsheet for weekly time sheets. It has a sheet for each day of the week that the user fill his time and other job descriptions in on a daily basis. This is then converted with formulas to a summary sheet. Now they wants a additional monthly summary sheet for all the overtime worked that the manager must sign off on.
I created this new Over Time Summary Sheet and it is in a table format ("TblOTSummary") with a total row at the bottom, and the manager's motivation and signature line is below the table.
I have created a userform for the input of each column in the Over Time Summary table with a macro button to call this userform in each of the daily sheets. I had the code working to add a new row to the table and inserting all the text boxes to it's respective column in the table before I added the total row and the manager's motivation and signature lines.
Also when I wanted to run the code from one of the daily sheets, there was some kind of error as well. (I tried so many things since then, that I cant remember the error message.)
Now I don't get that error message again, but the table row are added but the information is not entered into the last blank row of the table.
Some help with my code will be highly appreciated.
Code:
Private Sub cmdSend_Click()
Dim r As Long
If Me.tbDate = VBA.Constants.vbNullString Then
MsgBox "Please enter a Date."
Me.tbDate.SetFocus
Exit Sub
End If
With shOTSummary
r = Range("a" & .Rows.Count).End(xlUp).Row + 1
.Range("a" & r).Value = Format(Me.tbDate, "yyyy/mm/dd")
.Range("b" & r).Value = Me.tbCust
.Range("c" & r).Value = Me.tbWSite
If Me.tbJNo = VBA.Constants.vbNullString Then
Range("d" & r).Value = ""
Else
.Range("d" & r).Value = Me.tbSite & Me.lJNo & Format(Me.tbJNo, "00000")
End If
.Range("e" & r).Value = Me.tbFNo
.Range("f" & r).Value = Format(Me.tbTimeS, "HH:mm")
.Range("g" & r).Value = Format(Me.tbTimeE, "HH:mm")
.Range("h" & r).Value = Me.tbCmnts
.Range("i" & r).Value = Format(Me.tbTrav, "0.0")
.Range("j" & r).Value = Format(Me.tbOT1, "0.0")
.Range("k" & r).Value = Format(Me.tbOT2, "0.0")
.Range("l" & r).Value = Format(Me.tbPH, "0.0")
Selection.ListObject.ListRows.Add AlwaysInsert:=False
End With
' Unload Me
End Sub