Writing a macro so that when saving a spreadsheet the data cannot be changed

[Michael I]

Hello all,
I work for a clinical research company. We are working toward
regulatory compliance with the FDA, and this includes electronic
records. At the current moment, we have system in place that exports
data to an excel spreadsheet from an old DOS based program. The
problem is that someone could theoretically change the data on the
spreadsheet, comprising the integrity and validity of the data and the
clinical trial. I need to make a system which does not allow users to
change the information on the spreadsheet.

I was thinking that a macro would work for something like this,
but I don't have much experience programming macros.

If anyone could point me in the right direction, I'd be grateful.

Thank you very much,
Mike

 

[J.E. McGimpsey]

Use PDF.

There is no way that you can really protect a workbook from
modification as long as the user has access to it. Macros can be
bypassed simply by holding down the shift key when opening the file.

Worksheet and Workbook protection are designed to keep people from
inadvertently changing data. There are free add-ins and macros (see

http://www.mcgimpsey.com/excel/removepwords.html

for one approach) available.

There are techniques you can use to make it more difficult (e.g.,
setting all but one sheet's .Visible property to xlVeryHidden and
putting "You must enable macros" on the remaining sheet, then using
the Workbook_Open event macro to unhide), but in general, they're
still for fooling really naive users, not those with, say, the
wherewithal to find these newsgroups.

 

[Jan Karel Pieterse]

Hi,

You could protect the worksheet with the data (Tools,
protection, worksheet).

But be aware, that any advanced user can circumvent that
(passworded) protection within a few seconds.

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Cole]

not sure if this is what you want but I am not expert either, try the
following, it is just lock and unlock the worksheet

Sub test()
'--------UNLOCK-------
ActiveWorkbook.Protect "111", Structure:=False, Windows:=False
ActiveSheet.Protect "111", DrawingObjects:=False, Contents:=False,
Scenarios:=False

'--------
'procedure of what you need to do
ActiveCell.Value = 1
'--------

'--------LOCK--------
ActiveWorkbook.Protect "111", Structure:=True, Windows:=True
ActiveSheet.Protect "111", DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub