Writing Macro to Run after changes are made

[Dave]

Is there a way to write a macro that would put up a reminder like "Take PSV
of Total Column on CLIN Tab." anytime a total in a certain cell changes?

I have a file with 50+ tabs that all end up providing data to a final
summary tab. In column Y97 there is a grand total. Anytime we make a
financial (number) change to any of the tabs that causes the total in Y97 to
change, I would like the message above to pop up. I need to make sure I am
creating an "audit" trail anytime the grand total changes and a reminder
would be nice becuase it sucks to go back and recreate a step that was missed.

Please let me know if you need more information to help me.

I appreciate any help.

Thanks

 

[Dave Peterson]

Maybe...

Y97 on the summary worksheet contains a formula, right?

Excel has some events that it monitors and one of those events is a _calculate
event. So each time excel recalcs, you could show a message. You could even
compare that existing value in y97 to the new value and decide when to show it.

But if you're worried about the user doing something, why not have the macro do
it for them (or even a formula do it for them)?

And if you really wanted an audit, you could monitor ranges on the 50 different
worksheets (the _change event fires when a typing change takes place). And then
put some logging information (user, date/time, cell address, worksheet address,
new value) in another worksheet.

 

[dhstein]

Put the code below in your worksheet. To do that - right click on the
worksheet - select "View Code" and paste in the code. Change $Y$100 to your
address .


Private Sub Worksheet_Change(ByVal Target As Range)

'Do nothing if more than one cell is changed or content deleted

If Target.Cells.Count > 1 Or IsEmpty(Target) Then
Exit Sub
End If

' Replace $Y$100 with your cell location - keep the "$"

If Target.Address = "$Y$100" Then


'Stop any possible runtime errors and halting code

On Error Resume Next

'Turn off ALL events so the change does not put the code into a loop.

Application.EnableEvents = False



MsgBox "Take PSV of Total Column on CLIN Tab."

'Turn events back on

Application.EnableEvents = True

'Allow run time errors again

On Error GoTo 0

End If

End Sub