Selecting a target column in vba

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
191
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top