assign button for color

[bk]

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!!!!!!!!!!!!!!!!!!!!!!!!

 

[Barb Reinhardt]

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]

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]

Don't worry. I was learning this stuff just a couple of years ago and came
here with a LOT of questions. I'm still learning the protection stuff and
as far as I'm concerned, dealing with a protected worksheet is a PITA as far
as code is concerned.

I don't see any unprotect commands in your code. What exactly do you want
to do? It appears that you want to clear or add an interior color to a
selection. I presume the workbook is protected when you begin. Do you
have the cells that you want to edit "unlocked".
(format->Cells->Protection -> Uncheck LOCKED)

 

[Barb Reinhardt]

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]

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]

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]

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]

Q: do I need this button or a macro.
A: You have a macro tied to the button. The only difference is how you
choose to "run" it. You can press a button, you can access it with "Tools ->
Macro", or you could add another pull down to your list (that takes more VBA)

Q: How can I unprotect certain cells, protect the worksheet and still use
the color palett?
A: I think when you say you "unprotect certain cells", you are unlocking
them so that when the worksheet is protected, the user can still edit them.
If you protect the worksheet so that it allows formatting of cells, you can
still use the color palette on those unlocked cells.

HTH
Barb Reinhardt

 

[bk]

Oh My God. You have done it. Happy Easter,

I am sorry I did mean unlock the cells. I took a class in excel, and the
teacher told me to make this , "button" because he could not explain to me
how the color palett was not visible when I protected the worksheet. Here all
the time it was a simple check of a box when protecting the sheet.

I cannot tell you how much this means to me to have you and others online to
help those who are just starting. I have worked on this macro for over a
month and finally found this place to ask questions. Now I can move on and
learn more, and if anybody ever has this problem I will tell this story, with
the happy ending.

Thanks for everything, and I really mean that.

Bob