How to do Pull-Down menu with colored graphics icon?

pchen

New Member
Joined
Jun 18, 2009
Messages
6
Hi All,

I'm trying to do a pull down menu with three selections - green,yellow, and red dots? I set up the data validation and referred it to a list. That is under Data Validation -> Settings -> Allow: List; Source: a range of cells where I put 3 colored dots(graphics). Now in the pull down menu when I select the first choice, it just gives me a black dot. The second choice gives me the same black dot. And so does the 3rd choice gives me a black dot.

So, how do I carry the 3 colored dots over? Or it's not possible using the Data Validation approach? If not what is the best and easiest way to go about doing this.

Thanks a lot! I'm new to this forum and this seems like a great resource.

:)

PC
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,565
As far as I know, the font color of the Validation dropdown list can't be varied.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,565
I'll try it later after work, but one work-around might be to use -1,0,1 as your listed values with custom formatting to make it appear as colored dots. That will get the cells to look as you want, but the validation dropdown...a Change event toggling some shapes (that looks like a dropdown) might be needed.

Perhaps the best way to go would be to use numbers and letters instead of colored dots. That way, users with red-green color blindness could use the sheet.
 
Last edited:

pchen

New Member
Joined
Jun 18, 2009
Messages
6
I'll try it later after work, but one work-around might be to use -1,0,1 as your listed values with custom formatting to make it appear as colored dots. That will get the cells to look as you want, but the validation dropdown...a Change event toggling some shapes (that looks like a dropdown) might be needed.

Perhaps the best way to go would be to use numbers and letters instead of colored dots. That way, users with red-green color blindness could use the sheet.
Interesting. Can you please kindly post a sample Excel spreadsheet to show that. It needs to be dots. I know silly. But it is.

:)

PC
 

pchen

New Member
Joined
Jun 18, 2009
Messages
6
Actually I replaced the dots with 1,0,-1 as you suggested. But I'm still trying to figure out how how could i format 1,0,-1 as colored dots? thanks.
 

pchen

New Member
Joined
Jun 18, 2009
Messages
6
I tried this in the Custom format box [Red][=-1];[Yellow][=0];[Green][=1] but it gave me an error message that Excel cannot use the number format you typed. Also, even if it did it doesn't ahve the colored dots i want...
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,565
Excel will accept this number format. (quotes required, the dot is CHAR(149) )
[Red]"•";[Yellow]"•";[Green]"•"
 

pchen

New Member
Joined
Jun 18, 2009
Messages
6
Excel will accept this number format. (quotes required, the dot is CHAR(149) )
[Red]"•";[Yellow]"•";[Green]"•"
Thanks! It works fine! But I had to use 1,0,-1 in the list pull-down selection. When I tried to put dots as the choice in the pull down selection it doesn't work. Is there a way to make the pull down menu list dots and have the color changes correctly? It's not that important. But aesthetically it will be nice. I'll also try to play around some more tomorrow. If you know a way then great.

Thanks a lot!

PC
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,565
The only thing I can think of to get dots in the "pull down menu" would involve shapes and the SelectionChange event.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,964
Messages
5,471,739
Members
406,780
Latest member
Todd Gentry

This Week's Hot Topics

Top