Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
Hello
How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
You would be better off using the data content of the cell as criteria
rather than the fill color of the cell, especially if the cell is filled by conditional format. "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
You can't change the colour of a Forms button though you can change the
font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
I suppose it is a COmmandButton because I can manually change the color of
the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
Which toolbar did you get it from.
what's its name. How do you manually change the colour. Regards, Peter T "Thanks" wrote in message ... I suppose it is a COmmandButton because I can manually change the color of the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
Added from developer tool bar Inset Button (Form Control) To change color I
click Design Mode then right click the button Properties and then change Back Color in properties list. "Peter T" wrote: Which toolbar did you get it from. what's its name. How do you manually change the colour. Regards, Peter T "Thanks" wrote in message ... I suppose it is a COmmandButton because I can manually change the color of the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
No you didn't do that. What's its name as I asked before (assuming you
didn't change it after it was created). Regards, Peter T "Thanks" wrote in message ... Added from developer tool bar Inset Button (Form Control) To change color I click Design Mode then right click the button Properties and then change Back Color in properties list. "Peter T" wrote: Which toolbar did you get it from. what's its name. How do you manually change the colour. Regards, Peter T "Thanks" wrote in message ... I suppose it is a COmmandButton because I can manually change the color of the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
Here is how to do it. But you will need to get you Hex codes by opening the
properties and selecting the colors you want from the palette for the back colors. You will need to substitute the cells, etc. to suit your purposes. The code would go into the sheet code module. Right click the sheet tab and click view code. You can add more elseif if you need more options. Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A1") Then If UCase(Range("A1").Value) = "A" Then Sheets(1).CommandButton1.BackColor = &HFFFF00 ElseIf UCase(Range("A1").Value) = "B" Then Sheets(1).CommandButton1.BackColor = &HFF000 End If End If End Sub P.S. The Hex code will look different because VBA deletes the leading zeros. "Thanks" wrote: Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
Sorry it is CommandButton11
"Peter T" wrote: No you didn't do that. What's its name as I asked before (assuming you didn't change it after it was created). Regards, Peter T "Thanks" wrote in message ... Added from developer tool bar Inset Button (Form Control) To change color I click Design Mode then right click the button Properties and then change Back Color in properties list. "Peter T" wrote: Which toolbar did you get it from. what's its name. How do you manually change the colour. Regards, Peter T "Thanks" wrote in message ... I suppose it is a COmmandButton because I can manually change the color of the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
Afraid I was a bit cryptic last time, but you would not have formatted a
Forms button via Design mode / Properties as you said you did. Anyway the name strongly implies it is an ActiveX CommandButton. Changing the colour of the button is simple enough but the potential problem in Excel 2007 is to determine the apparent cell colour. If in a Table it's difficult (but not impossible), but if the colour is applied by one of the new Conditional Formats it's extremely difficult and way beyond what's viable to demo (I have yet to attempt it). The following should work if the colour was been applied via the palette or a Style. If the cell colour returns white it's far from conclusive as to what the apparent colour is, so perhaps better to reset to its default, but that's up to you. A CF colour will override even a formatted colour hence following would be inadequate. Sub test() Dim nClr As Long Dim rCel As Range Dim ole As OLEObject Set rCel = ActiveCell ' or say Set rCel = ActiveSheet.Range("A1") Set ole = ActiveSheet.OLEObjects("CommandButton11") nClr = rCel.Interior.Color ' reset to default if black or white If nClr = vbBlack Or nClr = vbWhite Then nClr = vbButtonFace End If ole.Object.BackColor = nClr End Sub Obviously change the cell-ref and button name to suit. Now the second problem! The apparent colour can change for all sorts of reasons, some of these can be worked out in an appropriate worksheet event. However if the colour has been manually applied as a format nothing is going to trigger the code to change the button colour, you'll need to run run something like the above macro. Regards, Peter T "Thanks" wrote in message ... Sorry it is CommandButton11 "Peter T" wrote: No you didn't do that. What's its name as I asked before (assuming you didn't change it after it was created). Regards, Peter T "Thanks" wrote in message ... Added from developer tool bar Inset Button (Form Control) To change color I click Design Mode then right click the button Properties and then change Back Color in properties list. "Peter T" wrote: Which toolbar did you get it from. what's its name. How do you manually change the colour. Regards, Peter T "Thanks" wrote in message ... I suppose it is a COmmandButton because I can manually change the color of the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
This is what I have so far. Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("E34") Then If UCase(Range("e34").Value) < 300 Then Sheets("DASHBOARD").CommandButton11.BackColor = &HFF& ElseIf UCase(Range("e34").Value) < 400 Then Sheets("DASHBOARD").CommandButton11.BackColor = &HFFFF& End If End If End Sub no I realize that I should probably have an AND in there for between 300 and 400. However if I just use on CASE and change E34 <300 the button color does not change. "JLGWhiz" wrote: Here is how to do it. But you will need to get you Hex codes by opening the properties and selecting the colors you want from the palette for the back colors. You will need to substitute the cells, etc. to suit your purposes. The code would go into the sheet code module. Right click the sheet tab and click view code. You can add more elseif if you need more options. Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A1") Then If UCase(Range("A1").Value) = "A" Then Sheets(1).CommandButton1.BackColor = &HFFFF00 ElseIf UCase(Range("A1").Value) = "B" Then Sheets(1).CommandButton1.BackColor = &HFF000 End If End If End Sub P.S. The Hex code will look different because VBA deletes the leading zeros. "Thanks" wrote: Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
Thanks Peter
OK Working trying both solutions to see which works best for me. This is what I have so far. Sub test() Dim nClr As Long Dim rCel As Range Dim ole As OLEObject Set rCel = ActiveCell ' or say Set rCel = ActiveSheet.Range("F35") Set ole = ActiveSheet.OLEObjects("CommandButton11") nClr = rCel.Interior.Color ' reset to default if black or white If nClr = vbBlack Or nClr = vbWhite Then nClr = vbButtonFace End If ole.Object.BackColor = nClr End Sub F35 is linked to a calculated cell on another page and is calculated there. F35 is conditionally formatted as I would like for the button color. When the value changes the CF for F35 changes but not the Button. What now? "Peter T" wrote: Afraid I was a bit cryptic last time, but you would not have formatted a Forms button via Design mode / Properties as you said you did. Anyway the name strongly implies it is an ActiveX CommandButton. Changing the colour of the button is simple enough but the potential problem in Excel 2007 is to determine the apparent cell colour. If in a Table it's difficult (but not impossible), but if the colour is applied by one of the new Conditional Formats it's extremely difficult and way beyond what's viable to demo (I have yet to attempt it). The following should work if the colour was been applied via the palette or a Style. If the cell colour returns white it's far from conclusive as to what the apparent colour is, so perhaps better to reset to its default, but that's up to you. A CF colour will override even a formatted colour hence following would be inadequate. Sub test() Dim nClr As Long Dim rCel As Range Dim ole As OLEObject Set rCel = ActiveCell ' or say Set rCel = ActiveSheet.Range("A1") Set ole = ActiveSheet.OLEObjects("CommandButton11") nClr = rCel.Interior.Color ' reset to default if black or white If nClr = vbBlack Or nClr = vbWhite Then nClr = vbButtonFace End If ole.Object.BackColor = nClr End Sub Obviously change the cell-ref and button name to suit. Now the second problem! The apparent colour can change for all sorts of reasons, some of these can be worked out in an appropriate worksheet event. However if the colour has been manually applied as a format nothing is going to trigger the code to change the button colour, you'll need to run run something like the above macro. Regards, Peter T "Thanks" wrote in message ... Sorry it is CommandButton11 "Peter T" wrote: No you didn't do that. What's its name as I asked before (assuming you didn't change it after it was created). Regards, Peter T "Thanks" wrote in message ... Added from developer tool bar Inset Button (Form Control) To change color I click Design Mode then right click the button Properties and then change Back Color in properties list. "Peter T" wrote: Which toolbar did you get it from. what's its name. How do you manually change the colour. Regards, Peter T "Thanks" wrote in message ... I suppose it is a COmmandButton because I can manually change the color of the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Color based on cell
You need to workout the 'state' of the *other* cell as to which, if any, of
the format coditions resolves to =True. If a CF condition has triggered, what CF colour is applied with that CF. I can't tell you that as I can't see your spreadsheet. If you are using Excel2003 or earlier it will be a ColorIndex between 1-56 (come back if you don't know how to work out which colorindex) nClr = activeworkbook.colors(myColorIndex) than apply nClr to the button as per the example I posted If no CF is true you need to workout the format colour of cell f35, which is simply as per the example. If you are using Excel2007, as I explained, it is difficult to calculate the relevant colours. Simplest for your needs might be to copy the various relevant colours to an image editor and return the RGB attributes. Back in VBA do nClr = RGB(r, g, b) Regards, Peter T "Thanks" wrote in message ... Thanks Peter OK Working trying both solutions to see which works best for me. This is what I have so far. Sub test() Dim nClr As Long Dim rCel As Range Dim ole As OLEObject Set rCel = ActiveCell ' or say Set rCel = ActiveSheet.Range("F35") Set ole = ActiveSheet.OLEObjects("CommandButton11") nClr = rCel.Interior.Color ' reset to default if black or white If nClr = vbBlack Or nClr = vbWhite Then nClr = vbButtonFace End If ole.Object.BackColor = nClr End Sub F35 is linked to a calculated cell on another page and is calculated there. F35 is conditionally formatted as I would like for the button color. When the value changes the CF for F35 changes but not the Button. What now? "Peter T" wrote: Afraid I was a bit cryptic last time, but you would not have formatted a Forms button via Design mode / Properties as you said you did. Anyway the name strongly implies it is an ActiveX CommandButton. Changing the colour of the button is simple enough but the potential problem in Excel 2007 is to determine the apparent cell colour. If in a Table it's difficult (but not impossible), but if the colour is applied by one of the new Conditional Formats it's extremely difficult and way beyond what's viable to demo (I have yet to attempt it). The following should work if the colour was been applied via the palette or a Style. If the cell colour returns white it's far from conclusive as to what the apparent colour is, so perhaps better to reset to its default, but that's up to you. A CF colour will override even a formatted colour hence following would be inadequate. Sub test() Dim nClr As Long Dim rCel As Range Dim ole As OLEObject Set rCel = ActiveCell ' or say Set rCel = ActiveSheet.Range("A1") Set ole = ActiveSheet.OLEObjects("CommandButton11") nClr = rCel.Interior.Color ' reset to default if black or white If nClr = vbBlack Or nClr = vbWhite Then nClr = vbButtonFace End If ole.Object.BackColor = nClr End Sub Obviously change the cell-ref and button name to suit. Now the second problem! The apparent colour can change for all sorts of reasons, some of these can be worked out in an appropriate worksheet event. However if the colour has been manually applied as a format nothing is going to trigger the code to change the button colour, you'll need to run run something like the above macro. Regards, Peter T "Thanks" wrote in message ... Sorry it is CommandButton11 "Peter T" wrote: No you didn't do that. What's its name as I asked before (assuming you didn't change it after it was created). Regards, Peter T "Thanks" wrote in message ... Added from developer tool bar Inset Button (Form Control) To change color I click Design Mode then right click the button Properties and then change Back Color in properties list. "Peter T" wrote: Which toolbar did you get it from. what's its name. How do you manually change the colour. Regards, Peter T "Thanks" wrote in message ... I suppose it is a COmmandButton because I can manually change the color of the button. So now what? Thanks for your response. "Peter T" wrote: You can't change the colour of a Forms button though you can change the font. (I assume you don't mean a CommandButton). You can make a 'Bevel' Autoshape look like a button with a little customizing and removing its lines lines. Add some text to it and assign a macro Assuming your Bevel is named ("AutoShape 1") try the following Sub test() Dim idx As Long Dim cel As Range Dim shp As Shape Set cel = Range("A1") idx = Range("A1").Interior.ColorIndex If idx < 1 Then idx = 58 Set shp = ActiveSheet.Shapes("AutoShape 1") shp.Fill.ForeColor.SchemeColor = idx + 7 End Sub Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the cell's RGB colour and change Schemecolor to .RGB Regards, Peter T PS, the bevel looks like a double rectangle "Thanks" wrote in message ... Hello How can I change the button color based on cell contents or fill color. I have button one sheet and I would like for it to mimic the color of a cell in another sheet. The cell content is either "Red", "Yellow", "Green". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to change color of tab based on color of cell | Excel Programming | |||
Cell color = Button face color. Possible? | Excel Programming | |||
Excel: Syntax to change cell color based on color of another cell | Excel Worksheet Functions | |||
change fill color of a range of cells based on color of a cell? | Excel Programming | |||
Browse Forms Controls and change TextBox color based on cell color | Excel Programming |