RE: assign button for color

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Relevant Pages

  • RE: assign button for color
    ... have the cells that you want to edit "unlocked". ... Private Sub CommandButton1_Click ... .Pattern = xlSolid ... When I protect the sheet and use the button it askes me for the ...
    (microsoft.public.excel.worksheet.functions)
  • RE: assign button for color
    ... color palett for the rest of the month when a student leaves. ... protect the sheet, because some teachers are not that computer savey, the ... Private Sub CommandButton1_Click ... .Pattern = xlSolid ...
    (microsoft.public.excel.worksheet.functions)
  • RE: CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
    ... There is no way to lock cells without protected the worksheet. ... Private Sub Worksheet_Change ... Dim rng As Range ... ' protect sheet so cells selected to be locked will lock ...
    (microsoft.public.excel.programming)
  • RE: CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
    ... There is no way to lock cells without protected the worksheet. ... Private Sub Worksheet_Change ... Dim rng As Range ... ' protect sheet so cells selected to be locked will lock ...
    (microsoft.public.excel.programming)
  • RE: assign button for color
    ... You would think that I could just click on the button and the selected cells ... Private Sub CommandButton1_Click ... .Pattern = xlSolid ... Here is the Macro for two buttons, ...
    (microsoft.public.excel.worksheet.functions)