Changing Colours With Protection

X

xander1987

Hi all. Just hit an annoying problem in Excel 2000.

Although the cell is unlocked for editing, I can't change its colou
using the fill tool.

I could really do with this as asking the user to guess which colou
they want out of 56 is a bit daft.

Any ideas/solutions?

Thanks
 
D

Dave Peterson

They can still copy a cell that's shaded the way they want and paste|special
formats.

Or maybe you could give them a macro that checks the selected cells, unprotects
the worksheet, lets them change the color, and then reprotects the worksheet.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim AllUnLocked As Boolean
Dim wks As Worksheet

Set wks = ActiveSheet

If wks.ProtectContents _
Or wks.ProtectDrawingObjects _
Or wks.ProtectScenarios Then
'keep going
Else
MsgBox "Sheet is unprotected. Just use format|cells."
Exit Sub
End If

Set myRng = Selection
AllUnLocked = True
For Each myCell In myRng.Cells
If myCell.Locked = True Then
AllUnLocked = False
Exit For
End If
Next myCell

If AllUnLocked = False Then
MsgBox "Please only select unlocked cells"
Exit Sub
End If

wks.Unprotect Password:="hi"
Application.Dialogs(xlDialogPatterns).Show
wks.Protect Password:="hi"

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Dave Peterson

PS. xl2002+ offers more options when you protect a sheet--including an option
to format cells.

(maybe time for an upgrade? <bg>)
 
Top