Protection

  • Thread starter Thread starter Sally
  • Start date Start date
S

Sally

Using Excel 2000
I have a worksheet - I want my employees to be able to add
data to the spreadsheet and after they save it, they
cannot change it.

Or is this pobbible?
Once they enter data into a cell, it can't be changed
until someone enters a password.

Thank you
 
Sally, here is one way with a macro, put in worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
'Automatically Protecting After Input
'unlock all cells in the range first

Dim MyRange As Range

Set MyRange = Intersect(Range("A1:D100"), Target)
If Not MyRange Is Nothing Then
ActiveSheet.Unprotect password:="123"
MyRange.Locked = True
ActiveSheet.Protect password:="123"
End If
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
Thank you very much for your help
How does this code work?
I added the code to a VB window by pressing ALT+F11
and then copied and pasted the code.
Is that not right?
Because I'm able to make changes in cells.

Thank you
 
Sally, To put in this macro right click on the worksheet tab and view code,
in the window that opens paste this code, press Alt and Q to close this
window and go back to your workbook. If you are using excel 2000 or newer
you may have to change the macro security settings to get the macro to run.
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
Paul,

I'm sorry to bother you again. I appreciate your time.
I'm still having problems. I'm sure it's something I'm doing.

I followed your directions and I'm getting a Run-time error '1004'
"Unabale to set the locked property of the range class."
I click "debug" and it highlights the following code line
MyRange.Locked = True

HELP! - Thanks Again
 
Sally, tested it in 2000 and '97, works for me, did you copy and paste the
code? Put it in the sheet module? If you want I will send you a workbook
with the code and see if it works for you, let me know.

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
Back
Top