Lock cell selection cancels UserInterFaceOnly
 

I am using Office 2003 on Windows XP.

I use the following code which allows me to make programmatic changes to a
sheet even though protection is on:

Sheet1.Protect Password:="***", UserInterfaceOnly:=True

I also want to prevent the user from selecting locked cells, so I added the
following line to my function:

Sheet1.EnableSelection = xlUnlockedCells

But now, I can't make programmatic changes to the sheet without turning
protection off. My code fails on:

dValue = Sheet1.Range("M5").Formular1c1

It seems if I prevent the user from selecting "M5" this shouldn't stop my
code from getting this info, but it does. Why? How can I work around this?

Thanks much for your assistance.

Lock cell selection cancels UserInterFaceOnly
 

This line:

dValue = Sheet1.Range("M5").Formular1c1

isn't changing the sheet. It's grabbing the formula (in R1C1 notation) from a
cell.

Maybe you declared dValue incorrectly (depending on what was in that cell???).

Dim dValue As String
Sheet1.EnableSelection = xlUnlockedCells
Sheet1.Protect Password:="***", UserInterfaceOnly:=True
dValue = Sheet1.Range("M5").FormulaR1C1

But this worked ok for me, too:

Option Explicit
Sub testme()
Dim dValue As String
dValue = "=r1c1"
Sheet1.EnableSelection = xlUnlockedCells
Sheet1.Protect Password:="***", UserInterfaceOnly:=True
Sheet1.Range("M5").FormulaR1C1 = dValue
End Sub

quartz wrote:

I am using Office 2003 on Windows XP.

I use the following code which allows me to make programmatic changes to a
sheet even though protection is on:

Sheet1.Protect Password:="***", UserInterfaceOnly:=True

I also want to prevent the user from selecting locked cells, so I added the
following line to my function:

Sheet1.EnableSelection = xlUnlockedCells

But now, I can't make programmatic changes to the sheet without turning
protection off. My code fails on:

dValue = Sheet1.Range("M5").Formular1c1

It seems if I prevent the user from selecting "M5" this shouldn't stop my
code from getting this info, but it does. Why? How can I work around this?

Thanks much for your assistance.

--

Dave Peterson