Protection on Worksheets

[Chris]

I have a Time Tracker that I have created in a spreadsheet with a button that
contains a macro that unprotects the worksheet, puts a timestamp in the
selected cell, and then re-protects the worksheet.

This works great up until the point the user tries to type into a locked
cell. The user is prompted with a message box that says the cell is
read-only and if they would like to edit the cell to unprotect the worksheet
(which obviously I do not want). So, I tried getting around this by setting
a password to unprotect the sheet. However, when the macro now runs, it asks
the user to enter a password to unprotect the sheet to be able to put their
timestamp into the cell.

I'm looking for a way to protect the sheet where the user can only put in a
timestamp by clicking the button and does not have the ability to unprotect
the sheet.

Does this make sense? Please offer any suggestions.

Thanks.

 

[Jim Thomlinson]

In your original macro when you protected and unprotected without a password
the recorded macro line for the protection looked something like this...

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Change it to

ActiveSheet.Protect Password:="whatever", DrawingObjects:=True,
Contents:=True, Scenarios:=True

Not that I added Password:="whatever" Supply your password and add this to
both the protect and unprotect.

 

[Chris]

The time stamp IS built into the macro. That isn't the issue here. The
issue is the protection of the spreadsheet. I only want users to be able to
select a cell (not type in it) and click the button (containing the macro) to
put the time stamp into the cell they have selected. Again, this part of it
is working fine.

Maybe the code of my macro might help:

ActiveCell.Select
ActiveSheet.Unprotect
Cells.Locked = False
ActiveCell.Value = Now()
Cells.Locked = True
ActiveSheet.Protect


Right now, any user can go in and click Unprotect Sheet from the menu and
therefore can edit any field, which I do not want. If I try to protect the
sheet with a password, my macro doesn't work correctly because it prompts the
user to enter a password at the "ActiveSheet.Unprotect" line.

Is there a way to disable menu options for users or gray them out by saving
the workbook a certain way or something? I don't want the users to be able
to unprotect the sheet. That is the main issue.

Thanks.

 

[Chris]

Jim,

Excellent help! That took care of that problem. However, how do I keep
users from getting into the Macros that I have created to look up the
password? They still have all the menu options to be able to modify stuff,
especially the macro.

Do you know how to disable these types of functions?

Thanks.

 

[Gord Dibben]

Why do you want user to type in locked cells?

You should unlock the cells that user can edit and lock the ones that take
the timestamp.

Assuming Column B has locked cells.

When you type in column A the sheet is unprotected, a timestamp is placed in
column B and the sheet is re-protected.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Me.Range("A" & n).Value <> "" Then
Me.Unprotect Password:="justme"
Me.Range("B" & n).Value = Now
End If
End If
enditall:
Me.Protect Password:="justme"
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Alt + F11 to go to VBE.

Select your workbook/project and right-click>VBAProject
Properties>Protection>Lock project for viewing.

Provide a unique password.

Save workbook.


Gord