Selecting a target column in vba

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
182
Hi guys, I'm having a little trouble with a bit of code.
I don't really know much vba, and I usually manipulate existing code to do what I need.
This bit of code has me stumped and I can't find anything similar.

At the moment I'm trying to record changes to my worksheet when someone modifies the cells in certain ranges.
The code then tracks the changes in offset cells. What I need is to have these changes recorded in columns, AW, AX, & AY.

Any help on this would be greatly appreciated.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C7:H229,K7:M229,P7:R229,U7:W229,Z7:AB229")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
    .Offset(, 46).Value = Date
    .Offset(, 47).Value = Time
    .Offset(, 48).Value = Environ("username")
End With
Application.EnableEvents = True
End Sub
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C7:H229,K7:M229,P7:R229,U7:W229,Z7:AB229")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    With Target
        Range("AW" & .Row).Value = Date
        Range("AX" & .Row).Value = Time
        Range("AY" & .Row).Value = Environ("username")
    End With
    Application.EnableEvents = True
End Sub
 

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
182

ADVERTISEMENT

Sorry to pester you Domenic, but the macro seems to have stopped working all of a sudden.
I'm using it on 2 worksheets and its stopped on both.

I've looked online and found references to enable the events but I'm not sure.
My code says that they're enabled.

It might be worth noting that I have toggle buttons on the same worksheet, but I'm not sure if they have caused an interference.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Did you try running the following line of code in the Immediate Window (Alt+F11 > Ctl+G)...

Code:
Application.EnableEvents = True

If so, did you try quitting, and then re-starting Excel?
 

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
182
Brilliant. That's it working now.
Thanks again Domenic.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,988
Members
414,418
Latest member
mightyMagnus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top