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?

 

[steve the fish]

Oh ok I apologise. The reason you cant use target in a normal macro as you do in a worksheet change macro is that target is set in the worksheet change macro. If you want to use target like that in a normal macro you need to set it first to tell the macro what Target is. Eg 'Set Target = Selection'

 

[FrancisM]

Oh ok I apologise. The reason you cant use target in a normal macro as you do in a worksheet change macro is that target is set in the worksheet change macro. If you want to use target like that in a normal macro you need to set it first to tell the macro what Target is. Eg 'Set Target = Selection'

I apologize. I should have made my question statement clearer. So you are saying that it would be something like Set target = ("B4:B17")

 

[Fluff]

If you want to keep them separate, then use

Call Do_Carryover(Target)

and change the sub header to

Sub Do_Carryover(Target as Range)

 

[dmt32]

If you are calling a procedure that is in a standard module from you worksheets code page and that procedure needs to work with Target range then as @steve the fish identified, you need tell your code what target is - you can do this by passing it as an argument to the procedure.

Add the parameter Target to your procedure

Sub Do_Carryover(ByVal Target As Range)

'rest of code

And then pass it when calling from your worksheet

Call Do_Carryover(Target)

If the range your code is working with a sheet that is not in the activesheet then you should qualify the ranges in the called code to Target.Parent.Range

Hope Helpful

Dave

 

[FrancisM]

I added the lines that you suggested. It appears to not run, But also not give an error message. Is it possible to include a small sample? If so How?

 

[Fluff]

Are you saying the Carryover sub doesn't run?

 

[FrancisM]

Are you saying the Carryover sub doesn't run?

correct

 

[dmt32]

I added the lines that you suggested. It appears to not run, But also not give an error message. Is it possible to include a small sample? If so How?

If the sheet your Do_Carryover code is meant to be referring to is not the active sheet then this could be a factor

qualify the range as shown below & see if resolves

If Intersect(Target, Target.Parent.Range("B4:B17")) Is Nothing Then Exit Sub

Dave

 

[Fluff]

Add the word stop as shown

Sub Do_Carryover()
Stop
'Reminds the user to enter the carryover name into the current FY listing.
Dim MsgBoxResult As Long

Then change something on the sheet, what happens?

 

[FrancisM]

@Fluff & @dmt32 . It was a combination of both your suggestions & a little on my part (VERY little).
Here is the worksheet code: Call Do_Carryover(Target)
Here is the module code:

Option Explicit
Sub Do_Carryover(ByVal Target As Range)
'Reminds the user to enter the carryover name into the current FY listing. ' prevents (prevents modification or deletion of cells code from running)
Dim MsgBoxResult As Long
If Intersect(Target, Target.Parent.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

Now I will test the other problem lines & see if they are still a problem. I also want to tank @steve the fish for his help, he explained that I had to tell the macro what the target is. Thank you all.