check for change in Excel VBA

Poco12 · October 26th, 2010, 01:01 PM

I have a worksheet that we use for status reporting for some 200 activities. I would like to insert the current date in one cell when the manager updates status on another cell in the same row.

I'm able to put in the date when the two cell references are hard-coded. However, I can't figure out how to use a single formula that will work as the reporters go row to row. I tried combining the column and ActiveCell.Row then using that variable in the "...Not Intersect(Target, Me.row)..." IF statement, but got an error.

Anyone have an answer for this? I could just be blowing syntax - I'm no VBA programmer.

One other thing - I've seen some examples using "row - 1" and such, but frankly, they could jump anywhere after they update the status.

mtranchi · October 26th, 2010, 01:51 PM

it sounds like you're using the worksheet change event, which is an event that is raised anytime the worksheet is changed. Here's code that will enter the date one column to the left of the cell that has been changed, but only if the cell that has been changed is in column "B".

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
    Application.EnableEvents = False
    Target.Offset(0, -1) = Date
    Application.EnableEvents = True
End If
End Sub

you have to turn off events or else it gets caught in a loop and keeps putting the date one cell to the left

Poco12 · October 26th, 2010, 05:29 PM

Your post got me started on the path to what I needed. Much simpler, too than some other things I was reading. What I ended up coding was -

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCol2
    MyCol2 = Left(ActiveCell.Address, 2)
    Application.EnableEvents = False
    If MyCol2 = "$M" Then
        Target.Offset(0, 10) = Date
        Target.Offset(0, 11) = Time
    End If
    Application.EnableEvents = True
End Sub

Works every time!

mtranchi · October 26th, 2010, 06:33 PM

it probably doesn't matter because it's such a small block of code, but for future reference there's no need to declare the MyCol2 variable as you can use Target directly. Also for future reference, when you declare variables don't forget to declare the type. If you end up making a big application with lots of code that will speed things up.

So if you did need to declare MyCol2 you would do it like so:

Code:

Dim MyCol2 as string

Anyway, here's the fastest most efficient way to do what you're doing:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then 'it's the 13th column, in other words, "M"
    Application.EnableEvents = False
    Target.Offset(0, 10) = Date
    Target.Offset(0, 11) = Time
    Application.EnableEvents = True
End If
End Sub

Take care

Poco12 · October 26th, 2010, 07:37 PM

even simpler. thank you.