I have a form which fills cells I5:J24 with some default values. It
stores those values in cells N5:O24, which are hidden. When the user
changes a value from the default, the cells contents are bold. The
following script changes the cell contents back to the default value
when the user double clicks the cell:


Code:
--------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error GoTo errHandler

If Union(Range("$I$5:$J$24"), Target).Address = Range("$I$5:$J$24").Address Then
Application.ScreenUpdating = False
ActiveCell.Offset(0, 5).Range("A1").Copy
ActiveCell.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
--------------------


When I protect the sheet and workbook, I get the following error:
Function is not available on a protected worksheet

The strange thing is, when I comment out the entire code, I still get
the error.

Any thoughts or suggestions?


--
grime
------------------------------------------------------------------------
grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
View this thread: http://www.excelforum.com/showthread...hreadid=488750

I don't get the same error message but I'll assume this is version specific.
I don't think disabling screen updating in this case is necessary and your
code didn't disable events, so reenabling isn't necessary. Suggested is the
following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Union(Range("I5:J24"), Target).Address = Range("I5:J24").Address Then
Me.Unprotect
Target.Value = Target(1, 6).Value
Target.Font.Bold = False
Target(2, 1).Select
Me.Protect
End If
End Sub

Regards,
Greg


"grime" wrote:


I have a form which fills cells I5:J24 with some default values. It
stores those values in cells N5:O24, which are hidden. When the user
changes a value from the default, the cells contents are bold. The
following script changes the cell contents back to the default value
when the user double clicks the cell:


Code:
--------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error GoTo errHandler

If Union(Range("$I$5:$J$24"), Target).Address = Range("$I$5:$J$24").Address Then
Application.ScreenUpdating = False
ActiveCell.Offset(0, 5).Range("A1").Copy
ActiveCell.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
--------------------


When I protect the sheet and workbook, I get the following error:
Function is not available on a protected worksheet

The strange thing is, when I comment out the entire code, I still get
the error.

Any thoughts or suggestions?


--
grime
------------------------------------------------------------------------
grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
View this thread: http://www.excelforum.com/showthread...hreadid=488750

I forgot to answer the below question:

The strange thing is, when I comment out the entire code, I still get
the error.

I beleive this is because the normal function of double-clicking a cell is
to enter Edit Mode. This is not allowed for locked cells when the sheet is
protected. Therefore, this should cause an error message whether you had
created code or not. However, I wouldn't expect you would get the same
message. Rather, somthing to the effect that you can't change protected
cells. I get:

"The cell or chart you are trying to change is protected and therefore
read-only..."

Regards,
Greg