Using Workbook_SheetChange for single column

[Guest]

What is the simplest way to use the "Private Sub Workbook_SheetChange(ByVal
Sh As Object, ByVal Target As Range)" for only one column? Or maybe this
isn't the choice I want to use. I want to run a macro only if data has been
added to a cell in column "C".
Any help would be appreciated.
Thanks in advance,
Brad K

 

[Rob van Gelder]

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then
If Not Intersect(Target, Columns("C")) Is Nothing Then
Beep
End If
End If
End Sub


Be aware that each Worksheet has it's own events for handling Change
So instead of receiving the Workbook event and working out which sheet it's
for, you could just receive the Worksheet event.

 

[Guest]

If you want to trigger this event for changes in col C of ALL sheets then use
the Workbook_SheetChange event .

If you want to trigger this event for changes in col C of JUST CERTAIN
sheets then use the Worksheet_Change event .

Here's the code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count>1 then Exit Sub
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Application.EnableEvents=False 'this prevents continuous looping
'''' Call your macro - placed in a Standard module
'''' or add your code directly here
End If
Application.EnableEvents=True 'This is required, unlike ScreenUpdating
End Sub