Share this content

Auto updating of excel sheet ?

Auto updating of excel sheet ?

I have an excel sheet which contains work carried out that also requires reviewing. Once approved initials are placed say in cell A20 in a box and will always appear at the same place on all sheets.

Is there any way to ensure that if the sheet is altered at all then the initials in cell A20 wil be erased thereby indicating the sheet had to be re-reviewed ? (the cell would need to be protected to prevent reviewers initials being entered by anyone else)


Please login or register to join the discussion.

By ACDWebb
23rd Mar 2010 13:31

There is with VBA

You will need to use something like

Private Sub Worksheet_Change(ByVal Target As Range)
 'inset code to change cell A20 / whatever here
End Sub

or perhaps

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
or even

Private Sub Worksheet_Calculate()

it all rather depends what is going to trigger the change to Cell A20

I presume from the wording of the query this is some form of template completed for each client - hence "and will always appear at the same place on all sheets."

If the sheet is largely protected with unprotected boxes to input review points then you could probably use Worksheet_Change  with

Select Case Target.Address

Case "B1"

Case "B2"

End Select

to test changes to your input cells. Your problem then will be comparing what is in the cell now with what was there before to determine if there has been a change

Thanks (0)
By stu
23rd Mar 2010 14:07

Thanks. Will give it a try. The sheet is

The sheet is not a fixed template and contents will vary. Header will be the same however for each client and review initials will always be in same cell. Idea is that once reviewed and signed any subsequent changes to sheet take out the initials so if reprinted it is clear that this version has not been reviewed. Thanks for the response. 


Thanks (0)
Share this content