Code giving run-time error when called, but not when placed on worksheet.

[FrancisM]

The code listed below runs on the worksheet, but gives a Run-time error '424': Object required when called. I called the code, because when placed on the worksheet, it prevented other code from running. I was thinking that if the code was called it would allow the other code to run.

Sub Do_Carryover()

'Reminds the user to enter the carryover name into the current FY listing.
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B4:B17")) Is Nothing Then Exit Sub
       MsgBoxResult = MsgBox("Is the Veteran a a new client to Career Link? " & vbCr, _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbNo Then
     
       MsgBox " Check to verify Veteran data is entered in FY ##  Referrals! " & vbCr & _
                   "" & vbCr & _
                   " It's critical that Veteran data is captured. " & vbCr & _
                   "" & vbCr & _
                   " Please enter the name in walk in list if not on this year's consult list! " & vbCr & _
                   "Do not enter the name on the Walk In list of there is a Consult for this FY! " & vbCr & _
                   "" & vbCr & _
                   " Enter veteran as a walk in, if a carry over from the past FY year, and enter the SC percent! " & vbCr & _
                   "" & vbCr & _
                   " You have entered " & Cells(Target.Row, 2) & " in cell " & Target.Address, vbInformation, "Vocational Services - Career Link" & ActiveSheet.Name
           
                Call Referals 'Calls Referrals folder.
               
       ElseIf MsgBoxResult = vbYes Then
            If WorksheetFunction.CountA(Range("B:B")) <> 0 Then
            MsgBox " Check to verify Veteran data is entered in FY ##  Referrals! " & vbCr & _
                   "" & vbCr & _
                   " It's critical that Veteran data is captured. " & vbCr & _
                   "" & vbCr & _
                   " Please enter the name in walk in list if not on this year's consult list! " & vbCr & _
                   "Do not enter the name on the Walk In list of there is a Consult for this FY! " & vbCr & _
                   "" & vbCr & _
                   " Enter veteran as a walk in, if a carry over from the past FY year, and enter the SC percent! " & vbCr & _
                   "" & vbCr & _
                   " You have entered " & Cells(Target.Row, 2) & " in cell " & Target.Address, vbInformation, "Vocational Services - Career Link" & ActiveSheet.Name
           
                Call Referals 'Calls Referrals folder.
            Else
                Exit Sub
                End If
                End If
End Sub

Please tell me what I am doing wrong. Will calling the code allow the other code to run?

 

[dmt32]

Glad we all could help and thanks for all the mentions - its very much appreciated.

Dave

 

[Fluff]

Now I will test the other problem lines & see if they are still a problem

If you are referring to the Change Event, possibly not due to this

Call Do_Carryover
      
    'This section of code provides notification if there is a scheduling conflict.
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H5:H10, H13:H17")) Is Nothing Then

 

[FrancisM]

If you are referring to the Change Event, possibly not due to this

Call Do_Carryover
     
    'This section of code provides notification if there is a scheduling conflict.
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H5:H10, H13:H17")) Is Nothing Then

I still am having issues with the other code. However I will post the problems as a separate posts since this one solved the initial post question.

 

[Fluff]

Ok, that's fine & thanks for the feedback