Hello all

a code i use to alter the colour of a cell when double clicked, will not run when i protect the sheet?

Can someone shed some light on what im sure is a simple fix i have missed completely?

Cheers!!!!

Private Sub Worksheet_BeforeDoubleClick
(ByVal Target As Range, Cancel As Boolean
Cancel = Tru
If Not Intersect(Target, Range("D1:D128"))
Is Nothing The
With Target.Interio
If .ColorIndex = 3 The
.ColorIndex = xlColorIndexNon
Els
.ColorIndex =
End I
End Wit
End I
End Sub

The macro is trying to change a protected cell.

In the workbook's open event reprotect with UserInterfaceonly to True. It
will let macros make changes to the protected sheet.

Sub Auto_Open()
Whatever_Sheet.Protect UserInterFaceOnly:=True, _
Password:="whatever"
End Sub


"gavin" wrote in message
...
Hello all,

a code i use to alter the colour of a cell when double clicked, will not
run when i protect the sheet??

Can someone shed some light on what im sure is a simple fix i have missed
completely??

Cheers!!!!!

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
End If
End Sub

Hi Tim,

The cells im looking to alter arent protected but the sheet is. Is this the
same as you suggested. I am sorry but could you explain a little more as to
how to implement this....im a complete novice!!!

Thanks!!!

"Tim Zych" wrote in message
...
The macro is trying to change a protected cell.

In the workbook's open event reprotect with UserInterfaceonly to True. It
will let macros make changes to the protected sheet.

Sub Auto_Open()
Whatever_Sheet.Protect UserInterFaceOnly:=True, _
Password:="whatever"
End Sub


"gavin" wrote in message
...
Hello all,

a code i use to alter the colour of a cell when double clicked, will not
run when i protect the sheet??

Can someone shed some light on what im sure is a simple fix i have
missed
completely??

Cheers!!!!!

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
End If
End Sub

Strange but true. Excel doesn't allow formatting unprotected cells on a
protected sheet.

So the approach would still apply.

In a module, put the Auto_Open code. Adjust the "Whatever_Sheet" to suit
your workbook.
Close the workbook, reopen, then try the double click again.

Userinterfaceonly = true allows macros to make changes to protected sheets.
It remains protected against changes through the UI though. (strange
terminology, "userinterfaceonly = true" belies the actual behavior). It
executes only once after the workbook is opened and then the macro can make
changes until the workbook is closed.

Another way is to unprotect every time you want to change, then reprotect
when done:

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
Target.Parent.Unprotect 'Password:="whatever"
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
Target.Parent.Protect 'Password:="whatever"
End If
End Sub

The second approach works similarly well, with some drawbacks. If the macro
crashes while unprotected the sheet is vulnerable. There might be a lot of
extra code to add if the project is big.

Userinterfaceonly has drawbacks too. Other macros outside the workbook can
make changes to the protected sheet. Most, but not all, actions can be done
against a protected sheet. There is a documented bug w/respect to
FillAcrossSheets if I recall correctly.


"gav meredith" wrote in message
...
Hi Tim,

The cells im looking to alter arent protected but the sheet is. Is this
the
same as you suggested. I am sorry but could you explain a little more as
to
how to implement this....im a complete novice!!!

Thanks!!!

"Tim Zych" wrote in message
...
The macro is trying to change a protected cell.

In the workbook's open event reprotect with UserInterfaceonly to True.
It
will let macros make changes to the protected sheet.

Sub Auto_Open()
Whatever_Sheet.Protect UserInterFaceOnly:=True, _
Password:="whatever"
End Sub


"gavin" wrote in message
...
Hello all,

a code i use to alter the colour of a cell when double clicked, will
not
run when i protect the sheet??

Can someone shed some light on what im sure is a simple fix i have
missed
completely??

Cheers!!!!!

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
End If
End Sub

Tim,

The latter option seems to have sufficed...for the moment....my fingers are
crossed.

.....and thank you for the explanation also.Its good to learn something new.
God knows ive sent enough posts!!!! :-)

Thanks for your help!! Cheers!!!

"Tim Zych" wrote in message
...
Strange but true. Excel doesn't allow formatting unprotected cells on a
protected sheet.

So the approach would still apply.

In a module, put the Auto_Open code. Adjust the "Whatever_Sheet" to suit
your workbook.
Close the workbook, reopen, then try the double click again.

Userinterfaceonly = true allows macros to make changes to protected
sheets.
It remains protected against changes through the UI though. (strange
terminology, "userinterfaceonly = true" belies the actual behavior). It
executes only once after the workbook is opened and then the macro can
make
changes until the workbook is closed.

Another way is to unprotect every time you want to change, then reprotect
when done:

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
Target.Parent.Unprotect 'Password:="whatever"
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
Target.Parent.Protect 'Password:="whatever"
End If
End Sub

The second approach works similarly well, with some drawbacks. If the
macro
crashes while unprotected the sheet is vulnerable. There might be a lot of
extra code to add if the project is big.

Userinterfaceonly has drawbacks too. Other macros outside the workbook can
make changes to the protected sheet. Most, but not all, actions can be
done
against a protected sheet. There is a documented bug w/respect to
FillAcrossSheets if I recall correctly.


"gav meredith" wrote in message
...
Hi Tim,

The cells im looking to alter arent protected but the sheet is. Is this
the
same as you suggested. I am sorry but could you explain a little more as
to
how to implement this....im a complete novice!!!

Thanks!!!

"Tim Zych" wrote in message
...
The macro is trying to change a protected cell.

In the workbook's open event reprotect with UserInterfaceonly to True.
It
will let macros make changes to the protected sheet.

Sub Auto_Open()
Whatever_Sheet.Protect UserInterFaceOnly:=True, _
Password:="whatever"
End Sub


"gavin" wrote in message
...
Hello all,

a code i use to alter the colour of a cell when double clicked, will
not
run when i protect the sheet??

Can someone shed some light on what im sure is a simple fix i have
missed
completely??

Cheers!!!!!

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
End If
End Sub