New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

Hi, I have been using the following code for some time with no issue. Suddenly I am getting the error code mentioned in the post title. I can figure out why this would happen or what may have been changed to cause this error to start occurring, can anyone give me a clue where to look?? The following code is called from a userform with 15 sets of combo boxes that the user selects a date, shift time and shift end time from.

Private Sub CommandButton1_Click()
Dim DateColumn As Integer
Dim TimeRowA As Integer
Dim TimeRowB As Integer
Dim NameFind As String
Dim count As Long
Dim StaffB As String
Dim TimeB As Double
Dim x As Integer

Unload Me



For x = 1 To 15
If Me.Controls("CMBShiftDate" & x) > "" Then

    With Sheets("Sheet1").Range("ListDates")
        DateColumn = .Find(what:=Me.Controls("CMBShiftDate" & x), After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False).Row
    End With

With Sheets("Sheet1").Range("Timelist")

TimeRowA = .Find(what:=Me.Controls("CMBTimeIn" & x), After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Row

End With

With Sheets("Sheet1").Range("list2")

TimeRowB = .Find(what:=Me.Controls("CMBTimeOut" & x), After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Row
End With

    'Make sure they are all blank
    If Application.CountBlank(Range(Cells(TimeRowA, DateColumn), Cells(TimeRowB, DateColumn))) = _
        Range(Cells(TimeRowA, DateColumn), Cells(TimeRowB, DateColumn)).Cells.count Then
    'What to do if they are blank
        Range(Cells(TimeRowA, DateColumn), Cells(TimeRowB, DateColumn)).Select
        Selection.Value = CMBStaff
    Else
    'What to do if they are not all blank
     Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String

StaffB = Cells(TimeRowA, DateColumn).End(xlDown).Offset(0, 0)
TimeB = Cells(Cells(TimeRowA, DateColumn).End(xlDown).Offset(0, 0).Row, 2)
        
strPrompt = StaffB & " is clocked on at " & Format(TimeB, "h:mm AM/PM") & " on " & Me.Controls("CMBShiftDate" & x) & _
". Please check " & CMBStaff & " and " & StaffB & "'s timecards and make the appropriate corrections. " & _
"Should " & StaffB & "'s shift be changed? Click YES to over-write  " & StaffB & "'s shift. Or, click NO to end " _
& CMBStaff & "'s shift at " & Format(TimeB, "h:mm AM/PM") & ". This will retain " & StaffB & _
"'s previously claimed hours."
        
strTitle = "Correction Needed"
        
iRet = MsgBox(strPrompt, vbYesNoCancel, strTitle)

If iRet = vbNo Then
        Range(Cells(TimeRowA, DateColumn), Cells(TimeRowA, DateColumn).End(xlDown).Offset(-1, 0)).Select
        Selection.Value = CMBStaff
End If

If iRet = vbYes Then
        Range(Cells(TimeRowA, DateColumn), Cells(TimeRowB, DateColumn)).Select
        Selection.Value = CMBStaff
End If
        
End If

End If

Next x

End Sub

The debug highlights this line

        DateColumn = .Find(what:=Me.Controls("CMBShiftDate" & x), After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False).Row

which is just supposed to return a row number, in the debugger when I mouseover "DateColumn" it says that the value is zero. I'm not sure why this is.

Thanks for the reply Norie, The data should all still be the same

    With Sheets("Sheet1").Range("ListDates")
        DateColumn = .Find(what:=Me.Controls("CMBShiftDate" & x), After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False).Row
    End With

sheet1 still has a range called ListDates and the combobox names haven't changed. I tried changing the Range reference to

With Sheets("Sheet1").Range("AK3:AD34")

To see if it would make a difference. I double checked that nothing has changed in that range. Its crazy but the first text after changing the an address instead of the named range, and the code worked. Then I tested it again and it failed. Any ideas? Is this a crazy date format issue?

Like this...

With ShiftEntry
   CMBShiftDate1.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate2.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate3.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate4.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate5.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate6.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate7.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate8.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate9.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate10.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate11.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate12.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate13.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate14.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")
   CMBShiftDate15.RowSource = Format("ListDates", "DDDD MM/DD/YYYY")

End With

Book1.xlsx Here ya go

Originally Posted by Norie

You should be able to get the Row like this.

DateColumn = Me.Controls("CMBShiftDate" & x).ListIndex +3

Hi Norie, That seems to work, I will mark as solved. What does ListIndex return? How does that work I mean?

btw thank you so much for helping me Norie Much appreciated