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]

You have not told the code what the object called Target is. You need to set that before using it.

 

[Fluff]

I called the code, because when placed on the worksheet, it prevented other code from running.

In what way did it prevent other code from running?

I was thinking that if the code was called it would allow the other code to run.

Maybe, maybe not. Depends on the answer to the above question.

 

[FrancisM]

You have not told the code what the object called Target is. You need to set that before using it.

Really dumb question. The target range is B4:B17. So what is the proper syntax? I understand that Target.Address = "???" Identifies the location of the target. However I am struggling with the syntax.

 

[FrancisM]

In what way did it prevent other code from running?

Maybe, maybe not. Depends on the answer to the above question.

The other code did not run, & there was no error message. All the various pieces of code ran separately, but when placed on the same work, simply did not run & did not provide any error message.

 

[Fluff]

What is the other code & how are you calling this code?

 

[FrancisM]

What is the other code & how are you calling this code?

Here is the code for the worksheet. I have added +++++ for the code that prevents other code from running.

Option Explicit

Public Sub AllowMacros()
'This allows a macro to run & keep the sheet protected.
Me.Protect UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Prevents code from running error message if the value is deleted.
'If Target.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub    +++++++++ prevents modification or deletion of cells code from running


'This code formats the phone numbers in a certain format.
    Dim vCopy As String
     Dim Num As Long
     Dim C As Range
    
     If Not Intersect(Target, Union(Range("E4:E10"), Range("E13:E17"))) Is Nothing Then
         For Each C In Target
             vCopy = C.Value
             For Num = VBA.Len(vCopy) To 1 Step -1
               If Not VBA.Mid(vCopy, Num, 1) Like "#" Then
                 Mid(vCopy, Num, 1) = Chr(32)
               End If
             Next
             vCopy = Replace(vCopy, " ", "")
             Application.EnableEvents = False
             Target.NumberFormat = "(000) 000-0000"
             Target.Value = CDec(vCopy)
             Application.EnableEvents = True
         Next C
     End If

    'This section of code clears the second listing of a duplicate, & allows the first.
    Dim r As Long
    If Not Intersect(Range("B5:B17,H4:H16"), Target) Is Nothing Then
        Application.EnableEvents = False
        For r = 5 To 17
            If Range("H" & r - 1).Value = "No" And Range("B" & r).Value <> "" Then
                Range("B" & r).ClearContents
            End If
        Next r
        Application.EnableEvents = True
    End If
    
 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
        If LCase(Target.Value) = LCase("No") Then
    
            MsgBox "If there is pink in a previously filled cell, after checking No." & vbCrLf & _
            "There is a Scheduling conflict." & vbCrLf & _
            "Choose another time or reschedule the first veteran" & vbCrLf & _
            "Remember! New Career Link Visits require 1 hour.", vbCritical, "Vocational Services - Career Link" & ActiveSheet.Name
        End If
        Dim SSNcell As Range
    'Test whether content should be an abbreviated SSN
    'This restricts the area of application of the event handler
    If Not Intersect(Target, Range("SSN")) Is Nothing Then
        'Make sure the program does not trigger a further event
        Application.EnableEvents = False
        'Loop over intersection
        For Each SSNcell In Intersect(Target, Range("SSN"))
            SSNcell.Value = VBA.Right(SSNcell.Value, 4)
        Next
        'Reset
        Application.EnableEvents = True
      End If
    End If
    
    'This code activates a message box when No is entered in the column regarding New Visit.
    If Not Intersect(Target, Range("H4:H9,H13:H16")) Is Nothing Then
        If Target = "No" Then
                                    Application.Speech.Speak " New appointments require a 1 hour slot. Please fill in the colored slot.  ", SpeakAsync:=True
                                Application.Wait (Now + TimeValue("00:00:01"))
              MsgBox "Veteran is qualified to meet with Career Link," & vbCrLf & "If they are:" & vbCrLf & "Ex-Offender  -  any jail time" & vbCrLf & "Homeless - if in the dom, or a shelter" & vbCrLf & "Low income - self-explanatory" & vbCrLf & "  " & vbCrLf & "These entires must be made upon Check In.", vbInformation, "Vocational Services - Career Link   " & ActiveSheet.Name

        End If
    End If
    
   'This code prevents unauthorized word entry & instructs the user what is the appropriate action
    'in these ranges: B13:C17, E4:E10, E13:E17.
    If Target.Cells.Count = 1 Then
        If Not Intersect(Union(Range("B4:C10"), Range("B13:C17"), Range("E13:E17"), Range("E4:E10")), Target) Is Nothing Then
            Application.EnableEvents = False
            If LCase(Target.Value) = "canceled" Or LCase(Target.Value) = "canx" Or LCase(Target.Value) = "can" Or LCase(Target.Value) = "cancel" Or LCase(Target.Value) = "schedule" Or LCase(Target.Value) = "scheduled" Or LCase(Target.Value) = "Rescheduled" Or LCase(Target.Value) = "move" Or LCase(Target.Value) = "moved" Then
                Application.Undo
                'Informs user that they can not enter this text.
                       Application.Speech.Speak "This Action  is  not authorized. Click   the Move Canceled  button ", SpeakAsync:=True
                MsgBox "Click the Move Canceled........... button", vbInformation, "Vocational Services - OVR   " & ActiveSheet.Name
                  Application.Wait (Now + TimeValue("00:00:01"))
                Application.EnableEvents = False
            End If
        End If
    End If
    
    Application.EnableEvents = True

   'This code prevents modification or deletion of cells in the range below.
        If Intersect(Target, Range("A2:T3,A21:C23,B11:T12,A18:B18,B32:M50,C18:F18,D21:F21,D23:F24,E20:F20,F22,D4:D17,D21,D23:F24,E20:F20:F24,D21:F21,F18,F20:F22,G18:F18,G18:J18,K18:M19,B63:M81,E87:P90,N4:N10,N13:N17,AI2:AJ3,AI4:AI10,AI13:AI17,AW1")) Is Nothing Then Exit Sub
        On Error GoTo ExitPoint
        Application.EnableEvents = False
        If Not IsDate(Target(1)) Then
        Application.Undo
        'Informs user that the cell that they clicked is locked.
      
            Application.Speech.Speak "You can't delete  or   modify cell contents  in this range. It is  locked.", SpeakAsync:=True
            Application.Wait (Now + TimeValue("00:00:01"))
        MsgBox " You can't delete or modify cell contents in this range " _
        , vbCritical, "Vocational Services - Career Link   " & ActiveSheet.Name
    End If
ExitPoint:    'Application.EnableEvents = True
    'End If
    'End If
  End Sub

Private Sub Worksheet_Activate()
If 1 = 1 Then
Do_Meeting 'Code that turns off varous sheet events & provides a xlNormalView
Else
 End If

End Sub

Private Sub Worksheet_Deactivate()
 'Un-hides Ribbon. (Normal Ribbon Access)
    Application.ScreenUpdating = False
      Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
       Application.DisplayFormulaBar = True
        Application.DisplayStatusBar = True
         Application.ScreenUpdating = False
            ActiveWindow.View = xlNormalView
End Sub

 

[steve the fish]

Really dumb question? Was that calling me dumb? Thanks for that. I'll retire from this thread.

 

[FrancisM]

Really dumb question? Was that calling me dumb? Thanks for that. I'll retire from this thread.

NO! I was calling my question dumb. Not you.

 

[Fluff]

The line of code you have commented out with the +++++ has nothing to do with the code you originally posted.
There is no reason that I can see stopping you from replacing this Call Do_Carryover with the actual code.