Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing Macro to Run after changes are made
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing Macro to Run after changes are made
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. Dave wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing Macro to Run after changes are made
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 "Dave" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro writing | Excel Worksheet Functions | |||
Writing Data with a macro??? | New Users to Excel | |||
Writing a macro | Excel Worksheet Functions | |||
Macro-writing problems | Excel Discussion (Misc queries) | |||
writing macro | New Users to Excel |