Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
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" ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
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" ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
Dave,
Thanks. I'm currently on a Chinese system without Office installed, so it was guesswork. NickHK "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" ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
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" ... 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" ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
Dave,
Interesting times for sure, but also Hong Kong, so not surprising really. NickHK "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" ... 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" ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the value of a protected cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format changing in protected cell | Excel Discussion (Misc queries) | |||
Changing Font in an unlocked cell in a protected sheet. | Excel Discussion (Misc queries) | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
Error changing protected cell | Excel Programming |