I have a spreadsheet that has a range A5:H30 being used as a list of product
numbers entered in Column A (A5:A30) and a vlookup in the remainder range
b5:h30 (locked cells) to fill in the remaining order information. Range
G5:G30 (locked) is the Rate. I protect the sheet to allow only unlocked
cells to be selected to ensure data integrity. My issue is that now I have
certain product numbers that have a variable Rate that needs to be entered
at the time the order is being entered. Is there a way to unprotect the
sheet, unlock Range G(whatever row contains the variable product number) so
that the variable rate can be entered, and then once entered, set the order
form back to the protected state. I can probably work the code but just
don't know the property to change the cell protection option. I thank you
in advance!

Amb

Hi Mike,

I am taking it you don't want to manually unprotect the worksheet.

Once unprotected it does not matter if a cell is locked or unlocked.

You can write a macro that will unpassword protect the sheet . You can then
have an alternate macro that protects. You can place these macros in your
personal macro folder so nobody else can run them.

To unprotect:

ActiveSheet.Unprotect Password:=""

To protect:

ActiveSheet.Protect Password:=""


Your password goes in the "".


"Mike Josephson" wrote:

I have a spreadsheet that has a range A5:H30 being used as a list of product
numbers entered in Column A (A5:A30) and a vlookup in the remainder range
b5:h30 (locked cells) to fill in the remaining order information. Range
G5:G30 (locked) is the Rate. I protect the sheet to allow only unlocked
cells to be selected to ensure data integrity. My issue is that now I have
certain product numbers that have a variable Rate that needs to be entered
at the time the order is being entered. Is there a way to unprotect the
sheet, unlock Range G(whatever row contains the variable product number) so
that the variable rate can be entered, and then once entered, set the order
form back to the protected state. I can probably work the code but just
don't know the property to change the cell protection option. I thank you
in advance!

Amb

Squeaky,

Actually I'm talking about just unprotecting that cell based on product
number, I don't want to allow users to manually unprotect the sheet, and
also if i unprotect the sheet i want to immediately protect the sheet again
after they enter the variable rate so that they cannot modify the other
protected cells...would I just reprotect after a change event?

thanks,
Amb

"Squeaky" wrote in message
...
Hi Mike,

I am taking it you don't want to manually unprotect the worksheet.

Once unprotected it does not matter if a cell is locked or unlocked.

You can write a macro that will unpassword protect the sheet . You can
then
have an alternate macro that protects. You can place these macros in your
personal macro folder so nobody else can run them.

To unprotect:

ActiveSheet.Unprotect Password:=""

To protect:

ActiveSheet.Protect Password:=""


Your password goes in the "".


"Mike Josephson" wrote:

I have a spreadsheet that has a range A5:H30 being used as a list of
product
numbers entered in Column A (A5:A30) and a vlookup in the remainder range
b5:h30 (locked cells) to fill in the remaining order information. Range
G5:G30 (locked) is the Rate. I protect the sheet to allow only unlocked
cells to be selected to ensure data integrity. My issue is that now I
have
certain product numbers that have a variable Rate that needs to be
entered
at the time the order is being entered. Is there a way to unprotect the
sheet, unlock Range G(whatever row contains the variable product number)
so
that the variable rate can be entered, and then once entered, set the
order
form back to the protected state. I can probably work the code but just
don't know the property to change the cell protection option. I thank
you
in advance!

Amb

Mike, Squeaky's solution is correct, if you only want to unprotect a
certain cell then you need to tell us the value that you want to
trigger the change, can the value be cleared once found?...we need a
little more informationMike Josephson;421258 Wrote:
Squeaky,

Actually I'm talking about just unprotecting that cell based on product
number, I don't want to allow users to manually unprotect the sheet,
and
also if i unprotect the sheet i want to immediately protect the sheet
again
after they enter the variable rate so that they cannot modify the other
protected cells...would I just reprotect after a change event?

thanks,
Amb

"Squeaky" wrote in message
...
Hi Mike,

I am taking it you don't want to manually unprotect the worksheet.

Once unprotected it does not matter if a cell is locked or unlocked.

You can write a macro that will unpassword protect the sheet . You
can
then
have an alternate macro that protects. You can place these macros in
your
personal macro folder so nobody else can run them.

To unprotect:

ActiveSheet.Unprotect Password:=""

To protect:

ActiveSheet.Protect Password:=""


Your password goes in the "".


"Mike Josephson" wrote:

I have a spreadsheet that has a range A5:H30 being used as a list of
product
numbers entered in Column A (A5:A30) and a vlookup in the remainder
range
b5:h30 (locked cells) to fill in the remaining order information.
Range
G5:G30 (locked) is the Rate. I protect the sheet to allow only
unlocked
cells to be selected to ensure data integrity. My issue is that now
I
have
certain product numbers that have a variable Rate that needs to be
entered
at the time the order is being entered. Is there a way to unprotect
the
sheet, unlock Range G(whatever row contains the variable product
number)
so
that the variable rate can be entered, and then once entered, set
the
order
form back to the protected state. I can probably work the code but
just
don't know the property to change the cell protection option. I
thank
you
in advance!

Amb



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116927