Changing Colours With Protection
 

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

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

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

Any ideas/solutions?

Thanks.


--
xander1987
------------------------------------------------------------------------
xander1987's Profile: http://www.excelforum.com/member.php...o&userid=27236
View this thread: http://www.excelforum.com/showthread...hreadid=469121

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

xander1987 wrote:

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

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

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

Any ideas/solutions?

Thanks.

--
xander1987
------------------------------------------------------------------------
xander1987's Profile: http://www.excelforum.com/member.php...o&userid=27236
View this thread: http://www.excelforum.com/showthread...hreadid=469121

--

Dave Peterson

damn that's good.

Thanks =)


--
xander1987
------------------------------------------------------------------------
xander1987's Profile: http://www.excelforum.com/member.php...o&userid=27236
View this thread: http://www.excelforum.com/showthread...hreadid=469121

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

(maybe time for an upgrade? <bg)

xander1987 wrote:

damn that's good.

Thanks =)

--
xander1987
------------------------------------------------------------------------
xander1987's Profile: http://www.excelforum.com/member.php...o&userid=27236
View this thread: http://www.excelforum.com/showthread...hreadid=469121

--

Dave Peterson