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

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

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

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

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

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

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

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

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