Tried:

Private Sub Worksheet_Activate()
ActiveSheet.Protect Password:= _
"pass", UserInterfaceOnly:=True
End Sub

It does protect the worksheet but when I i double click on the cel
that should toggle between "Yes" and "No" I get:

"Runtime error 1004
The cell or chart you are trying to change is protected.... " When
hit debug it goes to the line in the macro that changes the value o
the cell to either "Yes" or "No".

I also tried the following version in the macro that is triggered b
double clicking on the target cell (By the way the macro work
perfectly with protection off):

Public Sub ChooseYesNo(Trng As Range)

Dim YNrng As Range
Dim YNrngb As Range
Set YNrng = Range("FormCompleted")
Set YNrngb = Range("WklyHrsSigned")

ActiveSheet.Protect Password:= _
"pass", UserInterfaceOnly:=True

If Not Intersect(Trng, YNrng) Is Nothing Then
If YNrng = UCase("NO") Or YNrngb = UCase("Yes") Then

' following is the line that the macro gets stuck on

YNrng.Value = UCase("yes")

Range("FormCompletedLabel").Select
Else
YNrng.Value = UCase("no")
End If
Else
If Not Intersect(Trng, YNrngb) Is Nothing Then
If YNrngb = UCase("NO") Then
YNrngb.Value = UCase("yes")
YNrng.Value = UCase("yes")
Else
YNrngb.Value = UCase("no")
End If
End If
End If

Set YNrng = Nothing
Set YNrngb = Nothing

End Sub

This gives me the same error message as above.
Anybody have a suggestion?
Thanks
Richar

--
rgarber5
-----------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...fo&userid=1135
View this thread: http://www.excelforum.com/showthread.php?threadid=26348