Attempted edit of protected cell jumps user to another cell
 

I have a workbook with several protected sheets in which users enter data
into unprotected cells. Much of the data displayed on the input sheets, such
as dates, headers, and descriptions, come from a single master data sheet
that contains all the "global" data for the workbook (so if I have to change
a date or description, I only have to change it once instead of on every
input sheet).

My problem: If a user clicks on a protected cell whose contents are a
formula pointing to the master data sheet, they get an error message that
they cannot alter the contents of that cell. When they click "OK" on the
error message, they instantly "jump" to the master data sheet where the
protected cell's contents are coming from. I'd rather they not see the
master sheet....how can I prevent this from happening?

Attempted edit of protected cell jumps user to another cell
 

When you protect a sheet, a list of options appears. One of the defaults is
to allow the user to select locked cells. If you deselect that option, then
the user cannot click that cell, though they can see the result of the
formulas. So, unless there is a reason why they should be allowed select
those cells, I suggest using this method.

"megangomez" wrote:

I have a workbook with several protected sheets in which users enter data
into unprotected cells. Much of the data displayed on the input sheets, such
as dates, headers, and descriptions, come from a single master data sheet
that contains all the "global" data for the workbook (so if I have to change
a date or description, I only have to change it once instead of on every
input sheet).

My problem: If a user clicks on a protected cell whose contents are a
formula pointing to the master data sheet, they get an error message that
they cannot alter the contents of that cell. When they click "OK" on the
error message, they instantly "jump" to the master data sheet where the
protected cell's contents are coming from. I'd rather they not see the
master sheet....how can I prevent this from happening?

Attempted edit of protected cell jumps user to another cell
 

Either don't allow them to select locked cells or make sure you enable "edit
directly in cell" under Editing Options.


Gord Dibben MS Excel MVP

On Tue, 4 Aug 2009 16:27:01 -0700, megangomez
wrote:

I have a workbook with several protected sheets in which users enter data
into unprotected cells. Much of the data displayed on the input sheets, such
as dates, headers, and descriptions, come from a single master data sheet
that contains all the "global" data for the workbook (so if I have to change
a date or description, I only have to change it once instead of on every
input sheet).

My problem: If a user clicks on a protected cell whose contents are a
formula pointing to the master data sheet, they get an error message that
they cannot alter the contents of that cell. When they click "OK" on the
error message, they instantly "jump" to the master data sheet where the
protected cell's contents are coming from. I'd rather they not see the
master sheet....how can I prevent this from happening?