Error coding for access of locked cells...

[gab1972]

I have a sheet where all of the cells are locked. The cells are
populated from a separate sheet. I'm beta testing my workbook and
when I *double-click* a locked cell, I get a message box that says the
cell is locked and that you must remove the password
(...blah...blah..blah...) and then it takes me to the sheet that
populates the cell. I don't want this to happen. Do I need some sort
of Worksheet Change coding that keeps the user on that sheet??

Thanks in advance!

 

[Dave Peterson]

If you're using xl2002 and above, you can protect the worksheet and not allow
the users to select any locked cells. It's one of those options in the
protection dialog.

You could also turn off the ability to edit directly in the cell.
In xl2003 menus:
Tools|Options|edit tab|uncheck "Edit directly in cell"

But this is a user by user setting.

 

[Gord Dibben]

Tools>Options>Edit. Enable "edit directly in cell"


Gord Dibben MS Excel MVP

 

[gab1972]

If you're using xl2002 and above, you can protect the worksheet and not allow
the users to select any locked cells.  It's one of those options in the
protection dialog.

You could also turn off the ability to edit directly in the cell.
In xl2003 menus:
Tools|Options|edit tab|uncheck "Edit directly in cell"

But this is a user by user setting.  

I actually have that feature (unable to select locked cells) enabled.
If I click on the cell, it doesn't highlight...but if I double-click
it, then I get that message box and it goes to the sheet and cell that
populated that locked cell.

Also, following your advice about turning that feature off (Edit
directly in cell), is this possible to disable this function with some
Workbook Open coding? I have some coding that turns off sheet tabs,
column and row headings, and all the menu bar items except for File
and Help...can I add some coding in here to do what you suggested?

Thanks.

 

[Dave Peterson]

It sounds like you have the option checked -- to allow selection of locked
cells. When I lock cells and then protect the worksheet, I can't select the
cells and I can't doubleclick to select on any of those locked cells.

I'd take a look once more.

Yes, you can turn off that setting.

Option Explicit
Sub Auto_Open()
Application.EditDirectlyInCell = False
End Sub

(You could use the workbook_open event if you want.)



gab1972 wrote:

 

[gab1972]

It sounds like you have the option checked -- to allow selection of locked
cells.  When I lock cells and then protect the worksheet, I can't select the
cells and I can't doubleclick to select on any of those locked cells.

I'd take a look once more.

Yes, you can turn off that setting.

Option Explicit
Sub Auto_Open()
    Application.EditDirectlyInCell = False
End Sub

(You could use the workbook_open event if you want.)

gab1972 wrote:

<snipped>>

I stand corrected. I had the option disabled to select locked cells,
but had the option enabled to select unlocked cells...the problem is
that there are no unlocked cells on that sheet...I turned everything
off when I protected the sheet and now everything is fine...hmmmm

 

[Howard31]

Hi gab1972,

In the code module associated with the sheet which is protected in the
BeforeDoubleClick event write Cancel = True that should solve the problem

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

This will make that double-clicking has no affect at all, however if the
user will try to edit a cell he'll get the message as you've mentioned but he
will not be taken to a different sheet

 

[Gord Dibben]

My opinion differs.

I say you want "edit directly in cell" enabled.

If disabled, you jump to linked cell upon double-click.


Gord