Re: Sheet Protection Question by Minitman
Minitman
Fri May 09 14:35:13 CDT 2008
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
On Fri, 9 May 2008 08:17:03 -0700, IT Dev Guy
<ITDevGuy@discussions.microsoft.com> wrote:
>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" wrote:
>
>> 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
>>
>>
>>
>> On Thu, 8 May 2008 22:01:17 -0700, "Tim Williams" <timjwilliams at
>> gmail dot com> wrote:
>>
>> >I think you can't change the locked properrty while the sheet is protected.
>> >
>> >Tim
>> >
>> >
>> >"Minitman" <steve@minitmaidsofaustin.com> wrote in message
>> >news:qi57249nhqan3frcarsua8ofn292ffi6pn@4ax.com...
>> >> 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
>> >>
>> >>
>> >> On Thu, 8 May 2008 11:10:00 -0700, IT Dev Guy
>> >> <ITDevGuy@discussions.microsoft.com> wrote:
>> >>
>> >>>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" wrote:
>> >>>
>> >>>> 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
>> >>>>
>> >>
>> >
>>
>>