Run time error 424 - Oblect required.

[danbates]

Hi,

I am just trying to clean up a code to make it easier for me and I have moved the following code from the

Private Sub Worksheet_SelectionChange(ByVal target As Range)

    Dim RowNumSC As Long
RowNumSC = ActiveCell.Row

'COLUMN 16 = P - SHUTTLE CAR
If target.Column = 16 And Range("N" & Selection.Row).Value = Range("N5").Value And _
WorksheetFunction.CountBlank(Range(cells(RowNumSC, 4), cells(RowNumSC, 13))) = 0 Then
MsgBox "1"
Call DisplayUserForm1_CIL(target)
ElseIf target.Column = 16 And Range("N" & Selection.Row).Value <> Range("N6" & Selection.Row).Value And _
Range("O" & Selection.Row).Value <> "" And WorksheetFunction.CountBlank(Range(cells(RowNumSC, 4), cells(RowNumSC, 13))) = 0 Then
MsgBox "2"
Call DisplayUserForm1_CIL(target)
ElseIf target.Column = 16 Then
MsgBox "3"
CIL_Check
End If
End Sub

to a module.

I have deleted the target column from the code but now I am getting the run time error 424 and it highlights this part:

Call DisplayUserForm1_CIL(target)

I have looked online and tried different things but nothing has worked for me.

Any help would be appreciated.

Thanks

Dan

 

[jasonb75]

If you've moved the code to a regular module and are now running the code manually then you will need to specify a range in place of Target as that is no longer set automatically.

 

[danbates]

Hi,

Sorry, I didn't add that I was still calling the code from the selection change event but like this instead:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Column = 57 Then WrapperA
End Sub

and then the code in the standard module:

 Sub WrapperA()
 Dim RowNumW As Long
RowNumW = ActiveCell.Row
'COLUMN 57 = BE - WRAPPER A
If Range("BC" & Selection.Row).Value = Range("BC5").Value And WorksheetFunction.CountBlank(Range(cells(RowNumW, 37), cells(RowNumW, 54))) = 0 Then
MsgBox "1"
Call DisplayUserForm1_CIL(target)
ElseIf Range("BC" & Selection.Row).Value <> Range("BC6" & Selection.Row).Value And Range("BD" & Selection.Row).Value <> "" And WorksheetFunction.CountBlank(Range(cells(RowNumW, 37), cells(RowNumW, 54))) = 0 Then
MsgBox "2"
Call DisplayUserForm1_CIL(target)
MsgBox "3"

Thanks

Dan

 

[RoryA]

Yes, but you aren't passing Target to the routine you call, so it has no value.

 

[jasonb75]

It's not a method that I use, but I believe that you need

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    If target.Column = 57 Then WrapperA(Target)
End Sub

Sub WrapperA(Target As Range)

 

[danbates]

Hi,

With both of your advice, I think I've managed to sort it.

The target was within this sub:

Sub DisplayUserForm1_CIL(ByVal target As Range)

so I changed this sub to:

Sub DisplayUserForm1_CIL()

and then set the target within that sub.

Then I deleted the target of the end this line:

Call DisplayUserForm1_CIL(target)

and now it seems to be working fine.

Thank you both for your help.

Thanks

Dan