I have a sheet named "Risk Register" and another named "Outside Residual Risk Threshold". I need an event macro on the
worksheet "View code" section, so that any time a value changes in column AF on "Risk Register", the filter on rows 8:39 in
"Outside Residual Risk Threshold" is refreshed. Both sheets are protected.
The code I tried in the "view code" or
"Outside Residual Risk Threshold" was as below...
Private Sub Worksheet_Calculate()
On Error Resume Next
If Me.FilterMode = True Then
.EnableEvents = False
.ScreenUpdating = False
Me.Protect Password:="risk*123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True, UserInterfaceOnly:=True
'Change range and filter criteria to suit
Me.Range("A8:N39").AutoFilter Field:=5, Criteria1:=""
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.EnableEvents = True
.ScreenUpdating = True
On Error Goto 0
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I haven't set this up correctly, since everytime you alter any cell in any worksheet, including other files open in
Excel at the same time, this event kicks off. Also, it always ends on the "Outside Residual Risk Threshold" sheet, when I
need it to end on the sheet you started on before the event ran (generally "Risk Register", but not always).
you able to tell me how best to write in a target range (changes in Col AF) that would trigger the event to kick off, so that
it doesn't occur at any other time? And also how to stop it ending on the "Outside Residual Risk Threshold" sheet when it's
finished it's run?
Thanks in advance