Pasting to Protected Cells in Xl2000

[Guest]

I am trying to prevent users from making changes to certain cells in XL2000.
I have the cells 'Locked' and both the Sheet and the Workbook are Protected
with a password. The locked cells prevent users from typing information into
them, however, users can still copy another cell and then paste it into the
locked and protected cells. Is there any way to prevent this?
Thanks for your help.
-VMill

 

[Ken Johnson]

I am trying to prevent users from making changes to certain cells in XL2000.
I have the cells 'Locked' and both the Sheet and the Workbook are Protected
with a password. The locked cells prevent users from typing information into
them, however, users can still copy another cell and then paste it into the
locked and protected cells. Is there any way to prevent this?
Thanks for your help.
-VMill

Hi,
one way is to prevent users from selecting locked cells. This is a
worksheet property that can be set for a session, however, the setting
does not persist between sessions. To overcome this lack of persistence
you can use the Workbook_Open Event to set it when the workbook is
opened...

Private Sub Workbook_Open()
Dim Sht As Worksheet
For Each Sht In Me.Worksheets
Sht.EnableSelection = xlUnlockedCells
Next
End Sub

To get the code in place...

1. Copy it

2. Right click any worksheet tab, then select "View Code" from the
popup. This takes you to the VB Editor

3. In the VB Editor double click on the "ThisWorkbook" icon in the
"Project Explorer" to open the Workbook's code module.

4. Paste the code into the code module that appears

5. For the code to work the workbook's Security level will need to be
Medium. Also, everytime the workbook is opened the user must click on
the "Enable Macros" button on the "Security Warning" dialog.
To change security to medium, go Tools|Macro|Security...select
Medium|OK|Close workbook|ReOpen workbook|click "Enable Macros" on
"Security Warning" dialog.

Ken Johnson

 

[Guest]

Thanks for the suggestion, however, I really need a solution that does not
rely on a user enabling macros.
-VMill

 

[Ken Johnson]

Thanks for the suggestion, however, I really need a solution that does not
rely on a user enabling macros.
-VMill

Hi -VMill,

If you add an extra worksheet and name it "Must Enable Macros" you can
use the following codes to hide the worksheets except for "Must Enable
Macros" which can also instruct the user to close the workbook then
reopen and click on "Enable macros". If the user follows the
instruction the reopened workbook will have visible sheets and will be
able to work on them and will not be able to select your protected
cells because the code also reinstates the xlUnlockedCells selection
property.

Use the same password for protecting each sheet and insert that
password into the speech marks in the code where you see Password:=
""....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Worksheets("Must Enable Macros").Visible = xlSheetVisible
Dim Sht As Worksheet
For Each Sht In Me.Worksheets
On Error GoTo PROTECTED
If Sht.Name <> "Must Enable Macros" Then
Sht.Visible = xlSheetVeryHidden
End If
Next Sht
Exit Sub
PROTECTED:
Sht.Unprotect password:=""
Sht.Visible = xlSheetVeryHidden
Sht.Protect password:=""
Resume Next
End Sub

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim Sht As Worksheet
For Each Sht In Me.Worksheets
Select Case Sht.Name
Case "Must Enable Macros"
On Error GoTo PROTECTED
Sht.Visible = xlSheetVeryHidden
Case Else
Sht.Visible = xlSheetVisible
Sht.EnableSelection = xlUnlockedCells
End Select
Next Sht
Exit Sub
PROTECTED:
Worksheets(Worksheets.Count - 1).Unprotect password:=""
Sht.Visible = xlSheetVeryHidden
Worksheets(Worksheets.Count - 1).Protect password:=""
Resume Next
End Sub

Both lots of go go into the ThisWorkbook code module as before

Ken Johnson