I have arranged the two tables as follows. This allows you in Table1 to sort on position or name, etc. It also allows you to do an easy pivottable.
The pivot table could look something like:
Then I have modified the userform slightly. I have added a textbox called tbxPosition, to hold the position of the selected person:
In use the form will look like:
And the code to run the userform:
Code:
Option Explicit
Private Sub btnCancel_Click()
Unload Me
End Sub
Private Sub btnOK_Click()
'find name in table1 and add hours in column behind
Dim rFound As Range
Dim vSp As Variant
vSp = Split(tbxHours, ":")
Set rFound = ActiveSheet.ListObjects("Table1").DataBodyRange.Find(what:=cbxNames)
rFound.Offset(0, 2) = rFound.Offset(0, 2) + vSp(0) + vSp(1) / 60
End Sub
Private Sub cbxNames_Change()
Dim rFound As Range
If Len(cbxNames) Then
Set rFound = ActiveSheet.ListObjects("Table1").DataBodyRange.Find(what:=cbxNames)
If Not rFound Is Nothing Then
tbxPosition = rFound.Offset(0, 1)
Else
tbxPosition = ""
End If
End If
End Sub
Private Sub tbxHours_Change()
If Len(tbxHours) = 2 And InStr(1, tbxHours, ":") = 0 Then
tbxHours = tbxHours & ":"
End If
End Sub
Private Sub tbxHours_Enter()
tbxHours = ""
End Sub
Private Sub tbxHours_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(tbxHours.Value) = 0 Then
tbxHours.Value = "00:00"
ElseIf Len(tbxHours.Value) = 4 Then
tbxHours.Value = "0" & tbxHours.Value
End If
If IsDate(tbxHours.Value) And Len(tbxHours.Text) = 5 Then
Else
MsgBox "Input Hour like this Example 05:35"
tbxHours.Text = "00:00"
End If
End Sub
Private Sub UserForm_Initialize()
With cbxNames
.RowSource = ActiveSheet.ListObjects("Table4").ListColumns(1).DataBodyRange.Address
End With
tbxHours = "00:00"
End Sub
