Code posted, ignore the countif code, I am experimenting with that part, basically it tells me if someone has already taken the date and shift that they are entering. What I need is to find a date and shift in the columns and then enter their name in the third column.
Many thanks, really do appreciate the help.
Simon
Private Sub cmdOK_Click()
Application.Cursor = xlWait
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Book1.xls"
Range("A1").Select
If WorksheetFunction.CountIf(Range("[Book1.xls]Sheet1!shift"), cboShift.Value) > 0 Then GoTo Simon Else GoTo Simon3
Simon:
If WorksheetFunction.CountIf(Range("[Book1.xls]Sheet1!Date"), DTPicker1.Value) > 0 Then GoTo Simon1 Else GoTo Simon3
Simon1:
MsgBox DTPicker1.Value & cboShift.Value & " already used"
GoTo Finish
Simon3:
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboName.Value
ActiveCell.Offset(0, 1) = cboShift.Value
ActiveCell.Offset(0, 2) = DTPicker1.Value
'Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String
Finish:
Range("A1").Select
Application.Cursor = xlDefault
ActiveWorkbook.Close True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
With cboShift
.AddItem "Early"
.AddItem "Late"
.AddItem "Night"
End With
cboName.Value = ""
With cboName
.AddItem "Simon"
.AddItem "Wendy"
.AddItem "Micheal"
.AddItem "Pete"
.AddItem "Keith"
End With
cboName.Value = ""
End Sub