Changing the value of a protected cell

[Shawn]

Sub go()
Dim goal As Range
Set goal = Sheets("sheet1").Range("A1")

Range("A1") = goal + 1
End Sub

I get an error because this is on a protected sheet and protected cell. How
can I get result without having to unprotect the sheet?


--
Thanks
Shawn

[Paul T.]

You must either unlock the cell prior to protecting it, or unprotect
the sheet, change value, and then protect again. There is no other way
around it, or there would be no point to protecting it. If it is a
cell that I dont mind users editing, I unlock it, otherwise I do the
unprotect, change, protect method.

[NickHK]

Shawn,
Look at the arguments for the Protect method.
You have an option of UIOnly (or something like that). That means that the
user cannot change the sheet, but code can.

NickHK

"Shawn" <Shawn@discussions.microsoft.com> 级糶秎ン穝籇:1C595279-B2C2-4785-8D0A-E3031515CF09@microsoft.com...
>
> Sub go()
> Dim goal As Range
> Set goal = Sheets("sheet1").Range("A1")
>
> Range("A1") = goal + 1
> End Sub
>
> I get an error because this is on a protected sheet and protected cell.
> How
> can I get result without having to unprotect the sheet?
>
>
> --
> Thanks
> Shawn

[Tom Ogilvy]

Assuming you don't want to unlock the cell before you protect the sheet, then

Sub go()
Dim goal As Range
Set goal = Sheets("sheet1").Range("A1")
ActiveSheet.Unprotect Password:="ABC"
Range("A1") = goal + 1
Activesheet.Protect Password:="ABC"
End Sub

--
Regards,
Tom Ogilvy



"Shawn" wrote:

>
> Sub go()
> Dim goal As Range
> Set goal = Sheets("sheet1").Range("A1")
>
> Range("A1") = goal + 1
> End Sub
>
> I get an error because this is on a protected sheet and protected cell. How
> can I get result without having to unprotect the sheet?
>
>
> --
> Thanks
> Shawn

[Dave Peterson]

UserInterfaceOnly = UIOnly

(just a clarification)

NickHK wrote:
>
> Shawn,
> Look at the arguments for the Protect method.
> You have an option of UIOnly (or something like that). That means that the
> user cannot change the sheet, but code can.
>
> NickHK
>
> "Shawn" <Shawn@discussions.microsoft.com> 级糶秎ン穝籇:1C595279-B2C2-4785-8D0A-E3031515CF09@microsoft.com...
> >
> > Sub go()
> > Dim goal As Range
> > Set goal = Sheets("sheet1").Range("A1")
> >
> > Range("A1") = goal + 1
> > End Sub
> >
> > I get an error because this is on a protected sheet and protected cell.
> > How
> > can I get result without having to unprotect the sheet?
> >
> >
> > --
> > Thanks
> > Shawn

--

Dave Peterson

[NickHK]

Dave,
Thanks.
I'm currently on a Chinese system without Office installed, so it was
guesswork.

NickHK

"Dave Peterson" <petersod@verizonXSPAM.net>
???????:44E5E617.5A7AD8E1@verizonXSPAM.net...
> UserInterfaceOnly = UIOnly
>
> (just a clarification)
>
> NickHK wrote:
>>
>> Shawn,
>> Look at the arguments for the Protect method.
>> You have an option of UIOnly (or something like that). That means that
>> the
>> user cannot change the sheet, but code can.
>>
>> NickHK
>>
>> "Shawn" <Shawn@discussions.microsoft.com>
>> 级糶秎ン穝籇:1C595279-B2C2-4785-8D0A-E3031515CF09@microsoft.com...
>> >
>> > Sub go()
>> > Dim goal As Range
>> > Set goal = Sheets("sheet1").Range("A1")
>> >
>> > Range("A1") = goal + 1
>> > End Sub
>> >
>> > I get an error because this is on a protected sheet and protected cell.
>> > How
>> > can I get result without having to unprotect the sheet?
>> >
>> >
>> > --
>> > Thanks
>> > Shawn
>
> --
>
> Dave Peterson

[Dave Peterson]

Wow!

You live in interesting times.

(not meant as a Chinese curse!)

NickHK wrote:
>
> Dave,
> Thanks.
> I'm currently on a Chinese system without Office installed, so it was
> guesswork.
>
> NickHK
>
> "Dave Peterson" <petersod@verizonXSPAM.net>
> ???????:44E5E617.5A7AD8E1@verizonXSPAM.net...
> > UserInterfaceOnly = UIOnly
> >
> > (just a clarification)
> >
> > NickHK wrote:
> >>
> >> Shawn,
> >> Look at the arguments for the Protect method.
> >> You have an option of UIOnly (or something like that). That means that
> >> the
> >> user cannot change the sheet, but code can.
> >>
> >> NickHK
> >>
> >> "Shawn" <Shawn@discussions.microsoft.com>
> >> 级糶秎ン穝籇:1C595279-B2C2-4785-8D0A-E3031515CF09@microsoft.com...
> >> >
> >> > Sub go()
> >> > Dim goal As Range
> >> > Set goal = Sheets("sheet1").Range("A1")
> >> >
> >> > Range("A1") = goal + 1
> >> > End Sub
> >> >
> >> > I get an error because this is on a protected sheet and protected cell.
> >> > How
> >> > can I get result without having to unprotect the sheet?
> >> >
> >> >
> >> > --
> >> > Thanks
> >> > Shawn
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[NickHK]

Dave,
Interesting times for sure, but also Hong Kong, so not surprising really.

NickHK

"Dave Peterson" <petersod@verizonXSPAM.net>
???????:44E5E8F3.16DD8708@verizonXSPAM.net...
> Wow!
>
> You live in interesting times.
>
> (not meant as a Chinese curse!)
>
> NickHK wrote:
>>
>> Dave,
>> Thanks.
>> I'm currently on a Chinese system without Office installed, so it was
>> guesswork.
>>
>> NickHK
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net>
>> ???????:44E5E617.5A7AD8E1@verizonXSPAM.net...
>> > UserInterfaceOnly = UIOnly
>> >
>> > (just a clarification)
>> >
>> > NickHK wrote:
>> >>
>> >> Shawn,
>> >> Look at the arguments for the Protect method.
>> >> You have an option of UIOnly (or something like that). That means that
>> >> the
>> >> user cannot change the sheet, but code can.
>> >>
>> >> NickHK
>> >>
>> >> "Shawn" <Shawn@discussions.microsoft.com>
>> >> 级糶秎ン穝籇:1C595279-B2C2-4785-8D0A-E3031515CF09@microsoft.com...
>> >> >
>> >> > Sub go()
>> >> > Dim goal As Range
>> >> > Set goal = Sheets("sheet1").Range("A1")
>> >> >
>> >> > Range("A1") = goal + 1
>> >> > End Sub
>> >> >
>> >> > I get an error because this is on a protected sheet and protected
>> >> > cell.
>> >> > How
>> >> > can I get result without having to unprotect the sheet?
>> >> >
>> >> >
>> >> > --
>> >> > Thanks
>> >> > Shawn
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Shawn]

I have decided to use sort of a back door method. I am going to make that
cell a formula equal to a value in another sheet that is unprotected.
--
Thanks
Shawn


"Shawn" wrote:

>
> Sub go()
> Dim goal As Range
> Set goal = Sheets("sheet1").Range("A1")
>
> Range("A1") = goal + 1
> End Sub
>
> I get an error because this is on a protected sheet and protected cell. How
> can I get result without having to unprotect the sheet?
>
>
> --
> Thanks
> Shawn