Drop down list - Display Yes/No but returns "tick"/"cross" symbols

[dmacmillan]

Hello All,

Seeking assistance with drop down lists with symbols.

I require a drop down list that displays Yes/No but returns a "tick" or "cross".

Is there Custom text formatting equivalent of [=1]"Yes";[=0]"No" using Wingdings to present symbols. For example, [=P]"Yes";[=O]"No" .... I cannot get this to work.

Many thanks,
David

 

[Yongle]

Assuming that your dropdown boxes are in cells in column B (amend code to match your data)

Place in sheet code window (right click on sheet tab \ view code \ paste code into that window)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
        Application.EnableEvents = False
        Select Case LCase(Target)
        Case "yes":     Target = Evaluate("Unichar(10004)")
        Case "no":      Target = Evaluate("Unichar(10006)")
        End Select
        Application.EnableEvents = True
    End If
End Sub

 

[dmacmillan]

Assuming that your dropdown boxes are in cells in column B (amend code to match your data)
View attachment 22981
Place in sheet code window (right click on sheet tab \ view code \ paste code into that window)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
        Application.EnableEvents = False
        Select Case LCase(Target)
        Case "yes":     Target = Evaluate("Unichar(10004)")
        Case "no":      Target = Evaluate("Unichar(10006)")
        End Select
        Application.EnableEvents = True
    End If
End Sub

Thank you Yongle!

I had hoped to avoid VBA - because of the audience/users - but suspected unavoidable.

Kind regards,
David

 

[Yongle]

Another suggestion which avoids VBA
1 and 0 in dropdown (insted of yes & no)
and use conditional formatting with icons as below

 

[dmacmillan]

Another suggestion which avoids VBA
1 and 0 in dropdown (insted of yes & no)
and use conditional formatting with icons as below

View attachment 22984
View attachment 22985

Thanks again Jongle!

The wee issue for this solution is the "0,1" interface in the drop-down list.

Oh, a query on the VBA solution ... must the Cases be written in lower case (i.e. "yes" not "Yes")?

Kind regards,
David

 

[RoryA]

You can do it with CF. First set the cell's regular number format to:
;;;"P"
which will handle the Yes values. Then add a conditional format rule to set the number format to:

;;;"O"
when its value is No. Then change the font, and job done.​

 

[Yongle]

Oh, a query on the VBA solution ... must the Cases be written in lower case (i.e. "yes" not "Yes")?

Quite the opposite - the code is written to handle everything (YES yes Yes yeS ....)

 

[dmacmillan]

Quite the opposite - the code is written to handle everything (YES yes Yes yeS ....)

Hello Yongle,
Thank you.
My query related to the VBA code - I changed "yes" to "Yes" and it fell over.
Thoughts?
Kind regards,
David

 

[Yongle]

Why did you change it?
- it was written to accept every form of yes

To restrict to "Yes" and "No"...

Select Case Target
Case "Yes": Target = Evaluate("Unichar(10004)")
Case "No": Target = Evaluate("Unichar(10006)")
End Select

I am guessing that the code failed because you gave VBA a headache!
- LCase(Target) cannot be"Yes"

 

[dmacmillan]

Why did you change it?
- it was written to accept every form of yes

To restrict to "Yes" and "No"...

Select Case Target
Case "Yes": Target = Evaluate("Unichar(10004)")
Case "No": Target = Evaluate("Unichar(10006)")
End Select

I am guessing that the code failed because you gave VBA a headache!
- LCase(Target) cannot be"Yes"

Marvellous, thank you Yongle.

Education is a journey; I wouldn't be across this issue had I not 'tweaked' the code.

With thanks,
David