Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > assign button for color
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bk bk is offline
external usenet poster
  
Posts: 28
Default assign button for color
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!!!!!!!!!!!!!!!!!!!!!!!!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default assign button for color
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!!!!!!!!!!!!!!!!!!!!!!!!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bk bk is offline
external usenet poster
  
Posts: 28
Default assign button for color
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!!!!!!!!!!!!!!!!!!!!!!!!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default assign button for color
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)


"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!!!!!!!!!!!!!!!!!!!!!!!!
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default assign button for color
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!!!!!!!!!!!!!!!!!!!!!!!!
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bk bk is offline
external usenet poster
  
Posts: 28
Default assign button for color
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!!!!!!!!!!!!!!!!!!!!!!!!
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default assign button for color
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!!!!!!!!!!!!!!!!!!!!!!!!
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I assign a symbol to a button (like the $ button) NickW Excel Discussion (Misc queries) 2 September 6th 05 02:45 PM
how to Assign Hyperlink to Button in Spreadsheet Warren Deeker Excel Discussion (Misc queries) 1 June 9th 05 02:05 PM
How do I Assign Macro to a button? BellExcel Charts and Charting in Excel 4 May 20th 05 07:21 PM
Assign auto_open to a button MJO Excel Discussion (Misc queries) 1 April 11th 05 04:54 PM
assign a macro to a control button ewan72 Excel Discussion (Misc queries) 1 December 3rd 04 02:04 PM


All times are GMT +1. The time now is 12:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement