Run-time error 424: Object Tequired

Question

That Worked!!!

You people amaze me sometimes!!!

Thanks

I have the following code that works as:

Private Sub Worksheet_Change(ByVal Target As Range)
'***********************************************************
'Dim SymChange As Range
'Dim SectChange As Range
'
'Set SymChange = Range("C3")
'Set SectToChange = Range("I2")
'
'If Target.Address = SymChange.Address Then
' If SymChange.Value <> UCase(SymChange.Value) Then
' SymChange.Value = UCase(SymChange.Value)
' End If
'End If
'
'If Target.Address = SectChange.Address Then
' Range("K2") = ""
'End If

End Sub

'*************************************************************

As this will be the same event on 14 different sheets, I am trying use a Call to run the code from a Module using the code:

Private Sub Worksheet_Change(ByVal Target As Range)

Call Sym_Or_Sect_Change

End Sub

When I do this I get the following Error message at the line:

   "If Target.Address = SymChange.Address Then"

Any suggestions?

Thanks

Answer 1

You have to pass the Target argument to Sym_Or_Sect_Change:

In the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Call Sym_Or_Sect_Change(Target)
End Sub

and in the standard module:

Sub Sym_Or_Sect_Change (ByVal Target As Range)
...
End Sub

Answer 2

Hi GibRoberts

The problem is the object variables are spelled differently in the code (highlighted in the picture below)

'

You may use this single code for the Change Event and replicate it to all the other sheets

No need to create a module and use the Call method to implement it.

Private Sub Worksheet_Change(ByVal Target As Range)

''''***********************************************************

Dim SymChange As Range

Dim SectChange As Range

Set SymChange = Range("C3")

Set SectChange = Range("I2")

If Target.Cells.CountLarge > 1 Then GoTo getOut

If Target.Value <> "" Then 

        If Target.Address = SymChange.Address Then Target.Value = UCase(SymChange.Value) 

        If Target.Address = SectChange.Address Then Range("K2") = "" 

End If 

getOut:

End Sub