Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't get userinterface variant to work on mac.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't get userinterface variant to work on mac.
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 "Richard Garber" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To get a sum for variant currency values | Excel Discussion (Misc queries) | |||
Variant as matrix | Excel Programming | |||
Variant Array | Excel Programming | |||
Variant to String | Excel Programming | |||
DLLs and VBA: Who free's a variant? | Excel Programming |