Dynamic Data Protection

**uzymedphys** · 09-06-2008, 07:59 AM

Hi all

In order to protect a spread-sheet from accidental alteration of existing data - I felt it would be ideal to protect the new data that has been inputted (like a dynamic protection of data so to speak)?

So if I enter something this week, I will not be able to make changes (whether accidental or not) to previous data i.e including that entered last week.

This way, it is safer and I also have a backup of the excel sheet each time it is closed plus it is automatically saved (without user intervention).

Any ideas on dynamic protection anyone? Thanks

Usman

**Richard Buttrey** · 09-06-2008, 11:05 AM

Hi,

Why not use Worksheet Protection, and ensure the changes you make have their cells automatically locked. You could achieve this with a VBA macro attached to the WorkBook Save event.

You'll need to work out how you define data that you have entered 'this week'. Is this data all in one column with a date label heading up the column for instance?

So the general approach would be to first format all cells as Unlocked and then switch on Worksheet protection.

Now in the Workbook Save event, have a line of code which identifies say the column of data you have just entered, (or the range of several columns if applicable) and which then locks all the cells in that column/range. The next time the workbook is opened you won't be able to overtype data in that column unless you were to manually unlock them.

HTH

**uzymedphys** · 09-06-2008, 01:54 PM

Thats a good idea...

Do you have a sample code for this as it will be very useful?

Thanks

Usman

**Richard Buttrey** · 09-06-2008, 03:32 PM

See the attached.

At the moment columns D:K are all unlocked cells and Sheet1 is protected. Whenever you enter an x in D1:K1 and save the workbook, the column containing the x will have its cells locked.

This is only an example. You may want to detect which columns to protect by referring to a date, and testing that within the macro.

The macro is in the Before_Save event of Sheet1 - right click on the sheet tab and click View code to see it.

HTH

**uzymedphys** · 09-07-2008, 08:04 AM

Hi Richard

Thanks for your message.

I tried your macro - and found that once I typed the new entry, it locked the entire sheet on Save.

I have attached some example data and would be grateful if you could sample that for the cell protection.

Thanks in advance

Usman

**uzymedphys** · 09-07-2008, 01:21 PM

Dear all,

Solution follows to the above problem:
___________
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Sheets("SRD_V099994").Protect Password:="test", UserInterfaceOnly:=True
Sheets("SRD_V099994").Cells.Locked = True

If Sheets("SRD_V099994").Cells(ActiveCell.Row, ActiveCell.Column).Value = vbNullString Then Sheets("SRD_V099994").Cells(ActiveCell.Row, ActiveCell.Column).Locked = False

End Sub
______________

Hope this helps

Usman

**shg** · 09-07-2008, 01:22 PM

uzymedphys,

Please read the Forum Rules and then edit your post to wrap your code with Code Tags.

**Richard Buttrey** · 09-07-2008, 02:12 PM

Hi,

That's what the macro is designed to do - at least with Sheet1. You don't say exactly how you intend to use your MyData sheet. I did say it was an example and that you'd need to decide how you intend to recognise which cells need protecting. You need to understand the logic and then apply it to your actual data. At the moment I don't know how you are using it.

Did you work through the example I gave and the data I showed on Sheet1 and observe how after saving and re-opening the workbook you couldn't then overtype the data you'd entered in the column marked with an x?

Rgds