Trying to access Protected cell leads to linked sheet

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

When double clicking a cell that is protected where the formula in that cell
refers to a cell in another worksheet, on closing the protection message,
the worksheet that that cell refers to is opened and that cell is activated.
In fact double clicking any cell that has a formula with precedences going
back to the other worksheet does the same thing.
Is there a way to prevent this without making the sheet hidden?
Rob
 
This worked for me in xl2002:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Me.ProtectContents Then
If Target.Locked Then
Cancel = True
End If
End If
End Sub

rightclick on the worksheet tab that should have this behavior and select view
code. Then paste it in the code window.

Another way is to stop them from selecting the locked cells on a protected
worksheet. xl2002 has an option under the Tools|Protection dialog, but earlier
versions needed a macro approach:

Option Explicit
Private Sub Workbook_Open()
With Worksheets("sheet4")
.EnableSelection = xlUnlockedCells
.Protect Password:="hi"
End With
End Sub
 
Thanks again, Dave.
Not having xl2002, I do like the macro to prevent selecting those protected
cells!
Just a couple of Q though. If there are a lot of sheets, is there a simple
way to have this code do that?
With Worksheets("sheet4")
.EnableSelection = xlUnlockedCells
.Protect Password:="hi"
End With

Also, I normally just write With Sheet4
Is With Worksheets("sheet4") a better/safer way or just a variant?
I realise that ("sheet4") is where you would normally enter the name of the
sheet, but I find that when developing a workbook, I quite often change the
names of the sheets as each sheet gets its own "personality" as the number
of the sheet doesn't normally change.
Rob
 
By using the codename of the sheet, your code is much more robust than mine.

If the user (or developer) changes the name that appears in the worksheet tab,
my code will go kablewie!

But yours will continue to run.

If you use the same password for all the sheets, you could cycle through them:

dim wks as worksheet
for each wks in thisworkbook.worksheets
with wks
.activate
.EnableSelection = xlUnlockedCells
.Protect Password:="hi"
End With
next wks
 
Thanks for that bit of code, Dave.
And now that I know what kablewie sounds like I'll be listening for it next
time the workbook decides to have a haemorrhage.
Rob
 
Dave, both of the following procedures work great and thanks again!
However, I prefer to use the 2nd one but using that procedure still causes
another small problem. When you double click anywhere in that locked area,
whatever cell is active in the unprotected area becomes "live". That is,
edit mode is invoked for that cell and, if there's a formula or data in that
cell, it becomes too easy to mess with it. I tried using both procedures to
see if that would fix it but no go.
Any clues?
Rob

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Me.ProtectContents Then
If Target.Locked Then
Cancel = True
End If
End If
End Sub

Option Explicit
Private Sub Workbook_Open()
With Worksheets("sheet4")
.EnableSelection = xlUnlockedCells
..Protect Password:="hi"
End With
End Sub
 
I've turned off edit directly in cell and never have these challenges <vbg>.

Do you want to stop the double clicking completely?

If yes, you could use that _beforedoubleclick event.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
End Sub

Or you could be a dictator and turn off the "edit directly in cell".

Application.EditDirectlyInCell = False

(Maybe turned of in the worksheet_activate and turned on in
worksheet_deactivate????)

But I don't like other people playing with my settings--if you do it, remember
to leave it the way it was when you started.)

Workbook_deactivate or workbook_close might be good spots to look at.
 
Well.....never saw that edit direct to cell before, though I don't think
that's a good option as I agree with you about messing with other people's
settings.
Trouble with some of the options provided by MS is that they affect all the
workbooks.
Can't see why they can't make them particular only to the worksheet or at
least the workbook just like the Workbook options in Calculations and give
an option to make them global. (I've got xl2000.....maybe it's already
done or hopefully in xl2004??)
Thanks for the suggestions, Dave but I think I might leave as is for time
being.
Rob
 
Back
Top