Protecting sheets with drop down boxes

[Dolores Gomez]

I have an excel spreadsheet that has drop down boxes and formulas. I want to
be able to protect this spreadsheet so that other user will not delete my
formulas. I password protected the spreadsheet, however, when this sheet is
protected i am not able to select from the drop down list. Is there a macro
that I can incorporate into my worksheet that would help me with this
problem. If so, can you please give me the codes needed?

Thanks.

 

[Gary Brown]

Unlock the cells that have the data validation.

 

[Dolores Gomez]

Hi Gary,

If I unlock the cells that have the validation (I also have a function in
the same cell) there is a chance that they will delete the function and I
only want them to have the option to click on the drop down box.

 

[Gary Brown]

OK, let's go back to basics. I need to understand how you got to where you
are.
- Did you first enter formulas, then create Data Validation [DATA >
VALIDATION > ALLOW > LISTS > SOURCE.... ] then protect your worksheet?
- Does the formula you talk about reference the cells used in LISTS > SOURCE ?

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

 

[Dolores Gomez]

I have a sheet that has drop down boxes and functions associated to these
drop down boxes. one of the functions on these drop down boxes is the
following:

=IF($Q25="","",IF(INDIRECT("'UNIQUE
ATTR'!"&ADDRESS(MATCH($Q25,PTLIST,FALSE)+'UNIQUE
ATTR'!$M$1,MATCH(S$22,ATTR,FALSE)+'UNIQUE ATTR'!$M$2))="X","SELECT
VALUE","")).

the only reason I want to protect this worksheet in case someone selects
from the drop down list and they decide to remove their choice (sometimes
users will go in and pick the cell and click on delete). If they hit delete
it will remove the if statement. I also tried going into the format and
select "hide formula", however, this did not prevent me from deleting the
function.

OK, let's go back to basics. I need to understand how you got to where you
are.
- Did you first enter formulas, then create Data Validation [DATA >
VALIDATION > ALLOW > LISTS > SOURCE.... ] then protect your worksheet?
- Does the formula you talk about reference the cells used in LISTS > SOURCE ?

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

Hi Gary,

If I unlock the cells that have the validation (I also have a function in
the same cell) there is a chance that they will delete the function and I
only want them to have the option to click on the drop down box.

 

[Gary Brown]

is the 'drop down' box from Data Validation or from the Control Toolbox or
the Forms command bar?
If Data Validation, how are you connecting the forumla to the drop down box?

If you want, you can send me a sample at (e-mail address removed).
Delete the no spam.
Sincerely,
Gary Brown

I have a sheet that has drop down boxes and functions associated to these
drop down boxes. one of the functions on these drop down boxes is the
following:

=IF($Q25="","",IF(INDIRECT("'UNIQUE
ATTR'!"&ADDRESS(MATCH($Q25,PTLIST,FALSE)+'UNIQUE
ATTR'!$M$1,MATCH(S$22,ATTR,FALSE)+'UNIQUE ATTR'!$M$2))="X","SELECT
VALUE","")).

the only reason I want to protect this worksheet in case someone selects
from the drop down list and they decide to remove their choice (sometimes
users will go in and pick the cell and click on delete). If they hit delete
it will remove the if statement. I also tried going into the format and
select "hide formula", however, this did not prevent me from deleting the
function.

OK, let's go back to basics. I need to understand how you got to where you
are.
- Did you first enter formulas, then create Data Validation [DATA >
VALIDATION > ALLOW > LISTS > SOURCE.... ] then protect your worksheet?
- Does the formula you talk about reference the cells used in LISTS > SOURCE ?

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

Hi Gary,

If I unlock the cells that have the validation (I also have a function in
the same cell) there is a chance that they will delete the function and I
only want them to have the option to click on the drop down box.


:

Unlock the cells that have the data validation.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



:

I have an excel spreadsheet that has drop down boxes and formulas. I want to
be able to protect this spreadsheet so that other user will not delete my
formulas. I password protected the spreadsheet, however, when this sheet is
protected i am not able to select from the drop down list. Is there a macro
that I can incorporate into my worksheet that would help me with this
problem. If so, can you please give me the codes needed?

Thanks.

 

[Gary Brown]

sent an 'answer' via email.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

I have a sheet that has drop down boxes and functions associated to these
drop down boxes. one of the functions on these drop down boxes is the
following:

=IF($Q25="","",IF(INDIRECT("'UNIQUE
ATTR'!"&ADDRESS(MATCH($Q25,PTLIST,FALSE)+'UNIQUE
ATTR'!$M$1,MATCH(S$22,ATTR,FALSE)+'UNIQUE ATTR'!$M$2))="X","SELECT
VALUE","")).

the only reason I want to protect this worksheet in case someone selects
from the drop down list and they decide to remove their choice (sometimes
users will go in and pick the cell and click on delete). If they hit delete
it will remove the if statement. I also tried going into the format and
select "hide formula", however, this did not prevent me from deleting the
function.

OK, let's go back to basics. I need to understand how you got to where you
are.
- Did you first enter formulas, then create Data Validation [DATA >
VALIDATION > ALLOW > LISTS > SOURCE.... ] then protect your worksheet?
- Does the formula you talk about reference the cells used in LISTS > SOURCE ?

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

Hi Gary,

If I unlock the cells that have the validation (I also have a function in
the same cell) there is a chance that they will delete the function and I
only want them to have the option to click on the drop down box.


:

Unlock the cells that have the data validation.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



:

I have an excel spreadsheet that has drop down boxes and formulas. I want to
be able to protect this spreadsheet so that other user will not delete my
formulas. I password protected the spreadsheet, however, when this sheet is
protected i am not able to select from the drop down list. Is there a macro
that I can incorporate into my worksheet that would help me with this
problem. If so, can you please give me the codes needed?

Thanks.