Ok, I am trying to do the right thing here. What I wanted to do in the
begining is to prepare a document on excel, lock out users in certain cells
so as not to change my formulas. The teachers need to be able to use the
color palett for the rest of the month when a student leaves. So, when I
protect the sheet, because some teachers are not that computer savey, the
color palett shuts down on me. When I added the statement that you gave me it
allowed me to use the palett again making the buttons irrelevent. So my
question is, do I need this button or a macro. How can I unprotect certain
cells, protect the worksheet and still use the color palett?

Once again thanks for any help you can give me.

Bob

"Barb Reinhardt" wrote:

Bob,

YOu never did unprotect your sheet with the code. You protect it twice.
AND ... if it's protected when you do what you're doing, why even bother.
Try commenting out the protect lines and see what happens.

I

"bk" wrote:

Well that most certainly did something, please review the code, to make sure
I put your add on in in the right place.

The funny thing is that when I push the button it disables all the color and
the tools, but when I click on a new cell it changes all back until I click
on the button again. Dont get me wrong, it works now, but I cant figure out
what is going on in the head of this excell program, or why it is doing this.
You would think that I could just click on the button and the selected cells
would highlight yellow. LOL

Private Sub CommandButton1_Click()
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFormattingCells:=True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFormattingCells:=True

End Sub

Private Sub CommandButton2_Click()
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFormattingCells:=True
Selection.Interior.ColorIndex = xlNone
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFormattingCells:=True

End Sub



Thanks for your help. From me and all the teachers that will be using this
program, we bow to your greatness.

Bob

"Barb Reinhardt" wrote:

I just realized what you were asking. If you don't have FORMAT CELLS
selected in your protect code, you won't see the color palette.

Add AllowFormattingCells = TRUE to the end of your protect command.

"bk" wrote:

Can I unprotect a group of cells then protect the workbook and still use the
color palett. Right now when I protect the workbook and want to fill in the
unprotected portion with a color the color palett is blank. That is why I
wrote this macro but it still askes me for a password before the button
allows me to color in the selected cells. Here is the Macro for two buttons,
one to highlight and one to clear.

Private Sub CommandButton1_Click()
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True

End Sub

Private Sub CommandButton2_Click()
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True
Selection.Interior.ColorIndex = xlNone
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True

End Sub

I appreciate your help, but you should know I am learning and am not sure
where I am going wrong with this.

Thanks for any help you may give me.

Bob



"Barb Reinhardt" wrote:

Try adding this
dim r as range

for each r in selection
if r.locked = false then
With r.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
end if
next r


"bk" wrote:

I have assigned a button with a macro for the color yellow.......

Please review the code.

Sub allow_highlight()
'
' allow_highlight Macro
' Macro recorded 1/8/2007 by Classroom
'

'
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub

The probem is I have locked and unlocked cells, I want only to color the
locked cells. When I protect the sheet and use the button it askes me for the
password. I dont want this to happen, I just want the button to color the
cells that are unprotected.

Help!!!!!!!!!!!!!!!!!!!!!!!!