Protecting data on a sheet

R

Robert Crandal

I would like to setup my spreadsheet so that a user
cannot change the data after 1 week. What would be
a good way to block users from changing their own
data after 1 week or longer???

I figure, I can processed Workbook_Open() and test
how old the workbook is. From this point, is it simply
good enough to password protect the sheet???

I am not completely confident that the "Sheet Protect"
mechanism is completely secure, I mean, can't a skillful
user open up the VBA editor and write code to change
data on the protected sheet???
 
V

Varne

Hi!

To prevent people from changing data on your worksheet add a condition to
the code that unprotect your Excel sheet.

You can prevent people from getting access to VB Editor.

M Varnendra
 
M

Mike Fogleman

Excel is not totally safe from hackers and password crackers, but for your
normal users, you do the best you can. Yes, a Workbook_Open event can be
used to check today's date against the workbook's last modified date or
created date or a date 7 days prior to today, and then run your WS.Protect
code. Even that can be circumvented if a user were to change the computer
date back in time. You do the best you can. Also, you can password protect
your VBA code so the modules are not visible without a password. In the VBA
editor window under Tools/VBAProject Properties, there is a protection tab
to lock out code from being viewed.

Mike F
 
R

Ryan H

I would put the origin date in cell A1, lets say todays date. Then ensure
all the cells on the sheet are unlocked except A1, then protect the sheet.
This will allow the user to change whatever they want except cell A1.

Then you can put this code in the Workbook_Open Exvent:
_______________________________
Option Explicit

Private Sub Workbook_Open()

' if today is 7 days older than A1
If Date > Format(Range("A1").Value + 7, "mm/dd/yyyy") Then

With Sheets("Sheet1")

' unprotect worksheet
.Unprotect "password"

' lock all cells
.Cells.Locked = True

' protect sheet
.Protect "password"
End With
End If

End Sub
_______________________________

This will lock all cells when the workbook is opened if the date the user
opens the workbook is 7 days older than A1. I used "password" as your
password, you may want to change that.

For the most part Excel protection is safe, but there is third party
software that can be used to crack passwords if you want to pay alittle money
for it.

Hope this helps! If so, let me know, click "YES" below.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top