Can't get userinterface variant to work on mac.

[Richard Garber]

I have a several cells on a worksheet form that I want the user to be able to
double click and have the cell toggle back and forth between "Yes" and "No".
I used a before double click event on the worksheet to call a macro (in
module 1) that determines if one of the target cells has been double clicked.

Tried using the protect method with the UserinterfaceOnly variant and I get
the following error message:


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

Following are 2 ways i tried to protect the sheet:

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


I also tried the following version in the macro that is triggered by double
clicking on the target cell:

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


Both these attempts do protect the sheet - but give me the error message
when I double click the target cells.

The yesno toggling macro works fine with protection off.

Anybody have a suggestion?
Thanks
Richard

 

[Tom Ogilvy]

Win 98 SE, xl97 SR2

Your code worked well for me with the sheet protected.

Are all three named ranges defined on that sheet?
FormCompleted
WklyHrsSigned
FormCompletedLabel

If you can't get it to work, then try unprotecting the sheet at the top of
your code and reprotect it at the bottom.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
If Target.Count > 1 Then Exit Sub
ChooseYesNo Target
Cancel = True
End Sub



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


I have a several cells on a worksheet form that I want the user to be able
to
double click and have the cell toggle back and forth between "Yes" and "No".
I used a before double click event on the worksheet to call a macro (in
module 1) that determines if one of the target cells has been double
clicked.

Tried using the protect method with the UserinterfaceOnly variant and I get
the following error message:


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

Following are 2 ways i tried to protect the sheet:

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


I also tried the following version in the macro that is triggered by double
clicking on the target cell:

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


Both these attempts do protect the sheet - but give me the error message
when I double click the target cells.

The yesno toggling macro works fine with protection off.

Anybody have a suggestion?
Thanks
Richard