VBA: If changed cell is in a certain column execute subroutine

Question

I am trying to create a subroutine that runs when any cell in column 13 is changed. I've read a number of stack overflow questions already, but have not found my answer. here are some I've read:

Not activated by a change

Uses intersect. Doesn't address my issue

Might work but I'm not very good with events

I tried to make it work using the intersect fuction

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Intersect (Target, Activesheet.Columns(13)) Is Nothing Then
    MsgBox "Help Help"
EndIf

End Sub

This works when I change values but if left alone for a while it will come up with "Run-time error '1004': Method 'intersect of object '_Global' failed". Any ideas welcome. if there is a simpler way to acheive this, I'd love to know. Thank you for your time.

user3598756 · Accepted Answer · 2016-04-21 16:16:35Z

1

to handle ALL worksheet, place this in ThisWorkbook code pane

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Intersect(Target, Sh.Columns(13)) Is Nothing Then
    MsgBox "Help Help"
End If

End Sub

or

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Column = 13 Then
    MsgBox "Help Help"
End If

End Sub

Otherwise place the following in the code pane of the Sheet(s) you want to "handle" only

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 13 Then
    MsgBox "Help Help"
End If

End Sub

answered Apr 21, 2016 at 16:16

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA: If changed cell is in a certain column execute subroutine

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related