Ignore Click Event

[keith0528]

Greetings All,

I have some nifty code that tracks user changes (think i got it from this forum) anyway, I'm only wanting to pick up manual changes made to the worksheet(s). I want it to ignore click events. I don't want to track any cmd button pushes. This results in data collection overload. Here is the track_changes code:

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


ActiveWorkbook.Sheets("TrackChanges_Record").Visible = xlSheetVeryHidden


If ActiveSheet.Name = "TrackChanges_Record" Then Exit Sub


Application.EnableEvents = False


On Error Resume Next


UserName = Environ("USERNAME")


NewVal = Target.Value


Application.Undo


oldVal = Target.Value


lr = Sheets("TrackChanges_Record").Range("A" & Rows.Count).End(xlUp).Row + 1


Sheets("TrackChanges_Record").Range("A" & lr) = Now
Sheets("TrackChanges_Record").Range("B" & lr) = ActiveSheet.Name
Sheets("TrackChanges_Record").Range("C" & lr) = Target.Address
Sheets("TrackChanges_Record").Range("D" & lr) = oldVal
Sheets("TrackChanges_Record").Range("E" & lr) = NewVal
Sheets("TrackChanges_Record").Range("F" & lr) = UserName


Target = NewVal


Application.EnableEvents = True
End Sub

thanks in advance

 

[Jerry Sullivan]

Hi Keith,

Clicking a button won't directly trigger the Workbook_SheetChange event. You probably have some actions in the code associated with the button, that is causing a Change to the workbook cells. Those statements are the ones that are actually triggering the Workbook_SheetChange event.

To prevent that, you can temporarily disable events like this...

Private Sub CommandButton1_Click()
   Application.EnableEvents = False
   Range("A1").Value = "NewValue"
   Application.EnableEvents = True
End Sub