Disable Protection Messages

  • Thread starter Thread starter Cathy Myers
  • Start date Start date
C

Cathy Myers

Hi,

I am on Windows 2000, Office XP, and VB 6.3. My users will
be on Excel 97. I have protected my sheets so that the
users are unable to select locked cells (using code).
However, on Excel 97, whenever a cell is double-clicked, a
message pops up telling the user that the sheet has been
protected and giving him instructions on how to unprotect
the sheet. How do I disable this message? Thanks in
advance.

Yours,
C.M.
 
I dont see a problem with this. You have to in some way tell the user that
those cells are protected plus you should protect your sheet with a password
just in case they try to unprotect the sheet.

the only way I can think of overwriting the message is to protected the
password using Data Validation and create your own message but it has a bad
drawback. other way will be to play the beforedouble click and the selection
change

somthing like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Union(Target.Range("a1"), Range("a1:b10")).Address =
Range("a1:b10").Address Then



MsgBox " cell protected sorry dude"
ActiveCell.Offset(0, 1).Select

End If



End Sub
 
Hi Cesar,

Thanks for your efforts to help me.
I don't want to change the message. I want to disable it
competely. I appreciate any further advice.

Yours,
C.M.
 
Hi,

I have worked around this problem by using the following
bit of code:

Private Sub Worksheet_Activate()
ActiveSheet.Protect
Active.EnableSelection = xlUnlockedCells
End Sub

When the user double-clicks on a locked cell, an unlocked
cell is selected, and no messages are displayed.

My problem is however that I have some sheets that have no
unlocked cells. These are explanatory sheets that do not
require input. Since there are no unlocked cells to be
selected on a double-click, the message about protection
pops up. Does anyone have any ideas about a work-around?
Thanks in advance.

Yours,
C.M.
 
Cathy,

Find a "nothing" cell somewhere on the sheets and unprotect just that cell.

I also use freeze panes to avoid shifting of the view. The panes are set
many rows and columns beyond the viewing area. (Set the zoom to 25% to make
it easier to set).

steve
 
I made a small correction to your code (activesheet in place of Active):

Private Sub Worksheet_Activate()
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

And I didn't get the warning message when I clicked on any cell, I couldn't
select it.

And you might want to just stop all selections. From Xl2002 vba's help:

XlEnableSelection can be one of these XlEnableSelection constants.
xlNoSelection
xlNoRestrictions
xlUnlockedCells


But xlnoselection and xlunlocked (with no unlocked cells) behaved the same way
for me.
 
Back
Top