Sheet Protection Question

[Minitman]

Greetings,

I have a worksheet with linking formulas in 79 cells (named ranges),
Toolbars>Control Toolbox>CommandButtons and a Toolbars>Forms>ComboBox.

This worksheet is set up as a form to view individual records chosen
from the ComboBox. This form is setup to do one of two tasks. First
view the record. And second, edit the record.

To view: I need to only see the information in the record and not be
able to change anything - until I click the "Start Edit" button.

To edit: All of the formulas are changed into values. Then I can edit
any or all of the cells.When finished, I click the "End Edit" button,
which saves the edited version of the record into the "Customer List"
sheet and then puts the formulas back into the cells.

The only thing I am missing is how to keep from overwriting the
formulas (in the view mode) with vba. The problem I ran into with
Protect was that the CommandButtons were also protected and I need
them to always be unprotected. Is there a way to lock everything but
the CommandButtons and the ComboBox?

I'm not even sure if protection is what I need. Security is not an
issue here. Overwriting the formulas is.

Another possibility would be if when the user entered any of the cells
on that page, to automatically trigger the edit mode. The cells all
have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79).

Any help will be greatly appreciated.

-Minitman

 

[IT Dev Guy]

If you go to the Properties for the button (or combobox or individual cells
or whatever else doesn't need to be locked), you'll probably see that the
'Locked' field is checked -- that's the default. Simply uncheck it and
relock your sheet and you should be fine.

 

[Minitman]

Hey IT Dev Guy,

Thanks for the reply.

I didn't make myself clear. I am looking for a vba solution.

To simplify, The sheet has two states - Locked for viewing and
unlocked for editing. A CommandButton is used to toggle between the
states.

Here is the code I am trying to use to unlock named ranges pfCell_1 to
pfCell_100:

For i = 1 To 100
Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<<
Next i

So far all I get is this error message:

Run-time error '1004':
Unable to set the locked property of the range class

Debug highlighted the line in front of the '<<<<

Any ideas as to what is wrong or missing in this code?

Any help will be greatly appreciated.

-Minitman

 

[Tim Williams]

I think you can't change the locked properrty while the sheet is protected.

Tim

 

[Minitman]

Hey Tim,

Thanks for the reply.

Do you mean something like this:

Worksheets("Sheet1").Activate
ActiveSheet.Unprotect
For i = 1 To 100
Worksheets("Sheet1").Range("pfCell_" & i).Locked = False '<<<<
Next i

With ActiveSheet
.Protect _
.EnableSelection = xlNoSelection
End With

It still gives this same error message.

Run-time error '1004':
Unable to set the locked property of the range class

Debug highlighted the line in front of the '<<<<, the same as last
time.

That seemed like a possibility, but when I plugged in the unprotect in
front of the locking code, it made no difference.

Any other ideas as to why this is erroring out

-Minitman

 

[IT Dev Guy]

Hey, sorry about the misunderstanding before. Tim is right about needing to
unlock the sheet first. I tried pasting your code into a macro and debugging
and it got thru the lock piece fine for me. Are you positive your ranges are
all defined as you're passing them? For example, if the value of "i" when
you get the error is 5, maybe range pfCell_5 is missing...? If it stops at
the beginning - i.e., the value is 1 - try temporarily skipping the loop and
just hard-coding the Range as "pfCell_1" -- if it works that way, maybe you
need to change your range from '"pfCell_" & i' to '"pfCell_" + cstr(i)' (I
had to make many weird changes when I did my code, mainly due to users having
different versions, from Excel97 to current -- and I don't recall if that's
why I used the cstr command...)

Good luck, and let us know what happens.

 

[Minitman]

Hey IT Dev Guy,

Thanks for the reply.

I tried your suggestion with this code:

Range("pfCell_4).Locked = False

Which is the first named range in the For/Next loop.

I just realized that I forgot to mention in the earlier post that
these named ranges were also merged cells. So I had to add MergeArea
like so to get it to work:

Range("pfCell_4).MergeArea.Locked = False

It seems that vba has to be told that a merged cell is in a merge
area.

It also works in the For/Next loop with the variable.

Thanks again for the help.

-Minitman