Error 424 Object required

[erutherford]

2 questions:

1. The code below worked fine as a "change event", but I decided to allow the user to edit the data then copy over to the "WO" sheet. However when I restructured it using a command button, the code gives a "Object Req" Error 424.

Private Sub CommandButton1_Click()

    If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
  
Dim Lastrow As Long
    Lastrow = Sheets("WO").Cells(Rows.Count, "H").End(xlUp).Row + 2
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Copy Destination:=Sheets("WO").Range("H" & Lastrow) 'Copies Priority Rating to Col H.
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Offset(, -6).Copy Destination:=Sheets("WO").Range("A" & Lastrow) 'Copies Item to Col.A
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Offset(, -5).Copy Destination:=Sheets("WO").Range("B" & Lastrow) 'Copies Issues to Col.B
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Offset(, -4).Copy Destination:=Sheets("WO").Range("C" & Lastrow) 'Copies Est. to Col.C
    
   End If
   
End Sub

Question 2.

When the above code "crashes", I can reset it only by exiting excel completely. Shouldn't it be able to reset in the code window?

 

[Jaafar Tribak]

Try replacing Target with Selection and see if it works for you.

 

[erutherford]

Well it doesn't cause the error now, but its not functioning as written.

Private Sub CommandButton1_Click()

    If Not Intersect(Selection, Range("H:H")) Is Nothing Then
    If Selection.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
  
Dim Lastrow As Long
    Lastrow = Sheets("WO").Cells(Rows.Count, "H").End(xlUp).Row + 2
    If Selection.Value = "1" Or Target.Value = "2" Or Selection.Value = "3" Then Selection.Copy Destination:=Sheets("WO").Range("H" & Lastrow) 'Copies Priority Rating to Col H.
    If Selection.Value = "1" Or Target.Value = "2" Or Selection.Value = "3" Then Selection.Offset(, -6).Copy Destination:=Sheets("WO").Range("A" & Lastrow) 'Copies Item to Col.A
    If Selection.Value = "1" Or Target.Value = "2" Or Selection.Value = "3" Then Selection.Offset(, -5).Copy Destination:=Sheets("WO").Range("B" & Lastrow) 'Copies Issues to Col.B
    If Selection.Value = "1" Or Target.Value = "2" Or Selection.Value = "3" Then Selection.Offset(, -4).Copy Destination:=Sheets("WO").Range("C" & Lastrow) 'Copies Est. to Col.C
    
   End If

End Sub

 

[gallen]

You still have a 'Target' in there. After moving it from the Change event there is no variable named Target so it cannot be used.

If Selection.Cells.Count > 1 Or IsEmpty([COLOR=#ff0000][B]Target[/B][/COLOR]) Then Exit Sub

 

[gallen]

Sorry, misread code

 

[erutherford]

Thanks for the explanation behind the use of "target". I am doing some reading on this now. It is always better to learn how to fish than have someone catch fish for you!

 

[gallen]

Your code was confusing to read with all the ifs repeating. It could be written like this:

Private Sub CommandButton1_Click()

Dim Lastrow As Long

    'Validate selection
    If Selection Is Nothing Then Exit Sub
    If Selection.Cells.Count > 1 Or IsEmpty(Selection) Or Intersect(Selection, Range("H:H")) Is Nothing Then Exit Sub
    
    With Selection
        Lastrow = Sheets("WO").Cells(Rows.Count, "H").End(xlUp).Row + 2
        Select Case .Value
            Case "1", "2", "3"
                .Copy Destination:=Sheets("WO").Range("H" & Lastrow) 'Copies Priority Rating to Col H.
                .Offset(, -6).Copy Destination:=Sheets("WO").Range("A" & Lastrow) 'Copies Item to Col.A
                .Offset(, -5).Copy Destination:=Sheets("WO").Range("B" & Lastrow) 'Copies Issues to Col.B
                .Offset(, -4).Copy Destination:=Sheets("WO").Range("C" & Lastrow) 'Copies Est. to Col.C
        End Select
   End With

End Sub

 

[gallen]

Thanks for the explanation behind the use of "target". I am doing some reading on this now. It is always better to learn how to fish than have someone catch fish for you!

No Problem. Always happy to help those who are willing to learn. It's always better to teach a man to fish than to catch the fish for him

 

[erutherford]

The "case" code is much cleaner for sure. However the above code gives a Run time error "1004" Object defined error.
any ideas?

Private Sub CommandButton4_Click()

Dim Lastrow As Long

    'Validate selection
    If Selection Is Nothing Then Exit Sub
    If Selection.Cells.Count > 1 Or IsEmpty(Selection) Or Intersect(Selection, Range("E:E")) Is Nothing Then Exit Sub '<<<<<<error occurs="" here<="" strong=""></error>error occurs here
    
    With Selection
        Lastrow = Sheets("WO").Cells(Rows.Count, "H").End(xlUp).Row + 2
        Select Case .Value
            Case "1", "2", "3"
                .Copy Destination:=Sheets("WO").Range("H" & Lastrow) 'Copies Priority Rating to Col H.
                .Offset(, -6).Copy Destination:=Sheets("WO").Range("A" & Lastrow) 'Copies Item to Col.A
                .Offset(, -5).Copy Destination:=Sheets("WO").Range("B" & Lastrow) 'Copies Issues to Col.B
                .Offset(, -4).Copy Destination:=Sheets("WO").Range("C" & Lastrow) 'Copies Est. to Col.C
        End Select
   End With

MsgBox "complete"


End Sub

 

[gallen]

Apologies, been away. Did you sort this?

Best way to find out what is wrong is to break that line down so:

If IsEmpty(Selection) Then Exit Sub
If Selection.Cells.Count > 1 Then Exit Sub
If Intersect(Selection, Range("E:E")) Is Nothing Then Exit Sub