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

[pchen]

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

 

[mikerickson]

As far as I know, the font color of the Validation dropdown list can't be varied.

 

[pchen]

As far as I know, the font color of the Validation dropdown list can't be varied.

OIC. So, what would be the workaround? Maybe instead of doing DV pull-down menu what should I be doing?

 

[mikerickson]

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.

 

[pchen]

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]

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]

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]

Excel will accept this number format. (quotes required, the dot is CHAR(149) )
[Red]"•";[Yellow]"•";[Green]"•"

 

[pchen]

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]

The only thing I can think of to get dots in the "pull down menu" would involve shapes and the SelectionChange event.