Hi,

Im stuck with a nasty kind of issue which i dont seem to understand vots causing the problem. I have a userform that asks a user to input a date. Based on the date, the code openz another workbook, which is a rostr, to check for a corresponding code and fills in another textbox. Then based on the code in the textbox. It then does to another worksheet in the roster workbook to check the timing associated wz that code. The userform consists of r such records. All goes well the 1st time. But when i select the date in the 2nd record, it first throws a variable not defined error. Following is the code:

RqMonth (strFMnth)

 

Set wbRoster = Workbooks.Open(Filename:=rPath & lgYear & "\" & "Roster for the month of " & strMonth & " " & lgYear & ".xlsx", ReadOnly:=True, Password:="Admin123")

Set wksRoster = wbRoster.Worksheets("OPERATIONS")

 

wksRoster.Activate

 

Set rngStaffNbr = wksRoster.Range("D3", wksRoster.Range("D" & Rows.Count).End(xlUp)).Find(lgSnbr)

If Not rngStaffNbr Is Nothing Then

    strSC = Cells(rngStaffNbr.Row, lgDate + 4).Value

    If strSC = "OFF" Or strSC = "TRNG" Or strSC = "LV" Then

        MsgBox ("Duty change on a scheduled " & strSC & " day is not possible. Please review your date selection.")

        Me.Controls("cmbDCRrqst" & CStr(intCtrlNbr)).Enabled = False

        Me.Controls("txtDCRApdt" & CStr(intCtrlNbr)).SetFocus

        wbRoster.Close False

    Else

        Me.Controls("txtDCRApsc" & CStr(intCtrlNbr)).Text = Cells(rngStaffNbr.Row, lgDate + 4).Value

        Me.Controls("txtDCRApdt" & CStr(intCtrlNbr)).Enabled = False

                

        'pick shift code

        Set wksSc = wbRoster.Worksheets("Shift Codes")

        wksSc.Activate


        Set rng = wksSc.Range("C3:U9")

        Set rngend = wksSc.Cells(Rows.Count, rng.Column).End(xlUp)

 

        Set stn = rng.Find(Me.Controls("txtDCRApsc" & CStr(intCtrlNbr)).Text, LookIn:=xlValues, lookat:=xlWhole)

        If Not stn Is Nothing Then

            Me.Controls("txtDCRApst" & CStr(intCtrlNbr)).Text = stn.Offset(0, 1).Value

        End If

        

        If Me.cmbDCRCat.Text = "Replacement" Then

            Me.Controls("txtDCRdt" & CStr(intCtrlNbr)).Text = Me.Controls("txtDCRApdt" & CStr(intCtrlNbr)).Text

            Me.Controls("txtDCRdt" & CStr(intCtrlNbr)).Enabled = False

        End If

        

        'Update status

        If rngStaffNbr.Interior.Color = RGB(255, 255, 255) Then

            Me.lblAppStatus.Caption = "Normal"

        ElseIf rngStaffNbr.Interior.Color = RGB(83, 141, 213) Then

            Me.lblAppStatus.Caption = "DL Incharge"

        ElseIf rngStaffNbr.Interior.Color = RGB(0, 51, 204) Then

            Me.lblAppStatus.Caption = "DL Staff"

        ElseIf rngStaffNbr.Interior.Color = RGB(118, 147, 60) Then

            Me.lblAppStatus.Caption = "UA Incharge"

        ElseIf rngStaffNbr.Interior.Color = RGB(0, 128, 0) Then

            Me.lblAppStatus.Caption = "UA Staff"

        End If

        

    End If

End If

 

wbRoster.Close False

Set wbRoster = Nothing

Set wksSc = Nothing

Set wksRoster = Nothing

Set rngName = Nothing

Set rng = Nothing

Set rngend = Nothing

Set stn = Nothing
The error comes at 
        Set wksSc = wbRoster.Worksheets("Shift Codes")
I tried wz removing the line
        Set wksSc = nothing
But that then throughs an automation error on the same line. As mentioned, it works fine forbthe first time. Bt the 2nd record it fails

Would b grateful for any and every assistance provided