Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
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. gab1972 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
ToolsOptionsEdit. Enable "edit directly in cell"
Gord Dibben MS Excel MVP On Mon, 26 Jan 2009 09:46:22 -0800 (PST), gab1972 wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
On Jan 26, 1:26*pm, Dave Peterson wrote:
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. * gab1972 wrote: 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 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
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 Dave Peterson 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
On Jan 26, 1:56*pm, Dave Peterson wrote:
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 Dave Peterson 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
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 -- A. Ch. Eirinberg "gab1972" wrote: 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error coding for access of locked cells...
My opinion differs.
I say you want "edit directly in cell" enabled. If disabled, you jump to linked cell upon double-click. Gord On Mon, 26 Jan 2009 10:41:43 -0800 (PST), gab1972 wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locked worksheet & hyperlinks (w/ select locked cells unchecked) | Excel Discussion (Misc queries) | |||
Access Coding Question | Excel Programming | |||
I would like to block access to my VBA coding | Excel Programming | |||
Strange error when changing locked status of cells | Excel Programming | |||
Put comments on a locked spreadsheet even though cells not locked | Excel Worksheet Functions |