Call procedure in Standard Module from Private Sub Worksheet_Change(ByVal Target As Range)

[Ben47Excel]

Hi All,

I'm having issues trying to call code I've added to a standard module from the 'Private Sub Worksheet_Change(ByVal Target As Range)' Event.

The code was originally written within the Event, and works, but doesn't work when I copy that same code into a standard module and call it from the Event code. The issue is the Target parameter in the Event that I reference in the standard module.

The reason I want to do this is that I have about 70 worksheets with the same code in each, using the above event. Every time I'm required to make changes by users of the file, I need to copy the changes into all 70 sheets...very tedious. I thought there would be a solution where I change the code in a standard module, one time, and then call this from within the above Private Event within each of the 70 worksheets. 1 line of code x 70, rather than 500 lines x 70!

Below (1) works fine within the worksheet_change event. However if I copy the below code (1) to a standard module called e.g. ChangeCode and call that from within the worksheet_change (2) below, it returns a compile error 'variable not defined' and selects 'Target' within the standard module.

Just to say, I have a standard module routine I call from 'Private Sub Worksheet_Activate()' that works fine, but that's because there are no parameters like Target to work with.

Is the solution something like declaring a variable called Target within the standard module? I know this doesn't work either, but am I on the right lines with something like this as the solution?

Any assistance would be greatly valued before I get RSI in my wrists before copy pasting code 70+ times!

Thanks,

Ben

(1)
Dim H_SDirActRng As Range, H_SDirActRng2 As Range

Set H_SDirActRng = Cells(WorksheetFunction.Match("F1", LookupRng, 0), 3)
Set H_SDirActRng2 = Cells(WorksheetFunction.Match("F2", LookupRng, 0), 3)

If Not Intersect(Target, H_SDirActRng) Is Nothing And H_SDirActRng.Value = "Y" Then
H_SDirActRng2.Value = " - Type here... - "
Else
If Not Intersect(Target, H_SDirActRng) Is Nothing And H_SDirActRng.Value = "N" Then
H_SDirActRng2.Value = "N/A"
End If
End If

(2)

Private Sub Worksheet_Change(ByVal Target As Range)

Call ChangeCode

End Sub

 

[Jeffrey Mahoney]

Try this

Sheet module

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

Standard module

Sub ChangeCode(Target As Range)
  Dim H_SDirActRng As Range, H_SDirActRng2 As Range

  Set H_SDirActRng = Cells(WorksheetFunction.Match("F1", LookupRng, 0), 3)
  Set H_SDirActRng2 = Cells(WorksheetFunction.Match("F2", LookupRng, 0), 3)
  
  If Not Intersect(Target, H_SDirActRng) Is Nothing And H_SDirActRng.Value = "Y" Then
    H_SDirActRng2.Value = " - Type here... - "
  Else
    If Not Intersect(Target, H_SDirActRng) Is Nothing And H_SDirActRng.Value = "N" Then
      H_SDirActRng2.Value = "N/A"
    End If
  End If
  
End Sub

 

[Ben47Excel]

Hi Jeffrey,

simple and effective solution, superb! I've never used arguments/parameters in sub procedures before, that's a new one for me. I couldn't find an answer to this anywhere on the internet, so your solution is much appreciated!

Thanks so much,

Ben