Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
Greetings, I have an Excel form that has a combo box linked to a list of
items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
I'm in a bit of a quandry over this one - either you want to use the
cell/dropdown list or you don't. It seems to be a case of you can't have your cake and eat it too. Unless you can be more specific regarding when it should be and should not be made accessible, I don't see a way around it: If you lock it, you can't use it. If you unlock it, you don't seem to want it to be used. There is a way of using the worksheet's _SelectionChange() event handler to detect when the cell is selected and then force focus out of that cell into another - but if you do that, then you can't ever get into it to change the list choice. You might be able to base that action upon the contents of some other cell in your form; i.e. if cell X1 has some value in it (indicating that they've started filling out the form) then use the bypass routine, but if cell X1 is empty, then permit using it? "berniean" wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
Thank you for the response. However, this is not a cell drop-down, it is a
combo box. They behave totally differently. The cell drop-down is data validation. The combo box is a control added from the Forms Toolbox. It sits on top of the spreadsheet. That's why there is a reference cell for the result of the selection. It is the reference cell that I'm trying to bypass without locking it. "JLatham" wrote: I'm in a bit of a quandry over this one - either you want to use the cell/dropdown list or you don't. It seems to be a case of you can't have your cake and eat it too. Unless you can be more specific regarding when it should be and should not be made accessible, I don't see a way around it: If you lock it, you can't use it. If you unlock it, you don't seem to want it to be used. There is a way of using the worksheet's _SelectionChange() event handler to detect when the cell is selected and then force focus out of that cell into another - but if you do that, then you can't ever get into it to change the list choice. You might be able to base that action upon the contents of some other cell in your form; i.e. if cell X1 has some value in it (indicating that they've started filling out the form) then use the bypass routine, but if cell X1 is empty, then permit using it? "berniean" wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
The easiest way around it is to hide the linked cell. You can have a formula
refer to the linked cell to show you the value. When the user tabs they will not go to the hidden cell. -- HTH... Jim Thomlinson "berniean" wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
Thanks, Jim. I've already tried that and can still tab to the hidden cell.
I do have a formula, thanks to another Excel MVP, that uses the row number to find the item and place the result in another cell. I use that value in some vlookup formulas to get other info about the item. "Jim Thomlinson" wrote: The easiest way around it is to hide the linked cell. You can have a formula refer to the linked cell to show you the value. When the user tabs they will not go to the hidden cell. -- HTH... Jim Thomlinson "berniean" wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
That is not how it works at mey end but in any case. Put the linked cell on a
seperate sheet altogether. You will need to add a named range... On a seperate sheet Select a blank Cell In the address bar where you see the cells address (next to where you enter the formula) Select the Cell address and change it to something like MyLink and hit enter. Now when you select that cell the address will show up as MyLink In the linked cell format for your combo box change the cell reference to =MyLink Now your combo box is attached to that cell... -- HTH... Jim Thomlinson "berniean" wrote: Thanks, Jim. I've already tried that and can still tab to the hidden cell. I do have a formula, thanks to another Excel MVP, that uses the row number to find the item and place the result in another cell. I use that value in some vlookup formulas to get other info about the item. "Jim Thomlinson" wrote: The easiest way around it is to hide the linked cell. You can have a formula refer to the linked cell to show you the value. When the user tabs they will not go to the hidden cell. -- HTH... Jim Thomlinson "berniean" wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
Thanks, Jim, I'll give it a go.
"Jim Thomlinson" wrote: That is not how it works at mey end but in any case. Put the linked cell on a seperate sheet altogether. You will need to add a named range... On a seperate sheet Select a blank Cell In the address bar where you see the cells address (next to where you enter the formula) Select the Cell address and change it to something like MyLink and hit enter. Now when you select that cell the address will show up as MyLink In the linked cell format for your combo box change the cell reference to =MyLink Now your combo box is attached to that cell... -- HTH... Jim Thomlinson "berniean" wrote: Thanks, Jim. I've already tried that and can still tab to the hidden cell. I do have a formula, thanks to another Excel MVP, that uses the row number to find the item and place the result in another cell. I use that value in some vlookup formulas to get other info about the item. "Jim Thomlinson" wrote: The easiest way around it is to hide the linked cell. You can have a formula refer to the linked cell to show you the value. When the user tabs they will not go to the hidden cell. -- HTH... Jim Thomlinson "berniean" wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
Why does the linked cell have to be on the same worksheet?
You could put the linked cell on its own dedicated worksheet (hidden???) and use that cell in your other formulas (or plop a formula that retrieves that value if you need to see it). berniean wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bypass unlocked cell on protected worksheet in Excel 2003?
Thanks, Dave, I'll give it a go.
"Dave Peterson" wrote: Why does the linked cell have to be on the same worksheet? You could put the linked cell on its own dedicated worksheet (hidden???) and use that cell in your other formulas (or plop a formula that retrieves that value if you need to see it). berniean wrote: Greetings, I have an Excel form that has a combo box linked to a list of items. When the user selects an item from the list, the result is the row number of the item in the reference cell. In order for this to work on a protected sheet, that cell has to be unlocked. I don't want the user to be able to tab to that cell while filling out the form. Is there a way to bypass that cell without locking it? Locking it causes an error when attempting to make a selection from the drop-down. Any help will be greatly appreciated! Thanks, Bernie -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting comment in unlocked cell of protected Excel worksheet? | Excel Worksheet Functions | |||
Htperlink in an unlocked cell but protected worksheet | Excel Worksheet Functions | |||
Need to add cell comments in unlocked cell on protected worksheet | Excel Discussion (Misc queries) | |||
How do you shade in an unlocked cell on a protected worksheet? | Excel Discussion (Misc queries) | |||
How do i enter a note in excel to an unlocked cell on a protected. | Excel Discussion (Misc queries) |