Data Validation with Symbols

[green biro]

I would like to present a dropdown list for a cell consisting of the four
suit symbols (hearts, clubs, diamonds, spades) and the letters "NT". Can't
get data validation to do this as its font can't be changed.

Does anybody know how to do this any other way (perhaps with with VBA)?

Thanks for any advice or direction.

 

[Norman Harker]

Hi green biro!

Have you tried inserting the symbols in a range and referring to that
range in Data Validation List?

Works OK for me.

 

[Dave Peterson]

What symbols/characters and fonts did you use to get this to work?

 

[Debra Dalgleish]

The dropdown list can't be changed from Tahoma font, which doesn't have
those symbols. You could use the words in the dropdown, and use a macro
to display the symbol in the cell, after a word has been selected.

 

[Norman Harker]

Hi Dave!

I put the symbols in a range H1:H5
I used Insert > Symbol
Font is Lucida Sans Unicode
Subset is Miscellaneous Dingbats [the Spades Hearts Clubs Diamonds are
down the bottom]

I then used Data > Validation List declaring the range as H1:H5

My default font is Arial (10)

Sounds like I'm luckier at getting this to work than I am at Bridge.

 

[Dave Peterson]

That worked for me, too.

Thanks for the detailed instructuctions.

Hi Dave!

I put the symbols in a range H1:H5
I used Insert > Symbol
Font is Lucida Sans Unicode
Subset is Miscellaneous Dingbats [the Spades Hearts Clubs Diamonds are
down the bottom]

I then used Data > Validation List declaring the range as H1:H5

My default font is Arial (10)

Sounds like I'm luckier at getting this to work than I am at Bridge.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

 

[Dave Peterson]

I should have added that it worked for me in xl2002.

Insert|symbol was added in that version.

I went back to:
windows start button|run
charmap
and showed "lucinda sans unicode"

and couldn't find those symbols.

That worked for me, too.

Thanks for the detailed instructuctions.

Hi Dave!

I put the symbols in a range H1:H5
I used Insert > Symbol
Font is Lucida Sans Unicode
Subset is Miscellaneous Dingbats [the Spades Hearts Clubs Diamonds are
down the bottom]

I then used Data > Validation List declaring the range as H1:H5

My default font is Arial (10)

Sounds like I'm luckier at getting this to work than I am at Bridge.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

 

[Norman Harker]

Hi Dave!

That's a relief! I thought I was getting spots in my bloodshot eyes.

 

[green biro]

Thanks for reply.

Others replying seem to have cracked it but maybe that's because they've got
Excel 2002 - I've only got Excel 2000.

I'm assuming that the dropdown method is the only way of doing it in Excel
2000. Is there any good documentation on using listboxes in cells. I
couldn't make it look nice at all. It seems to acquiure unnecessary scroll
bars. Also, without protection, I can only modify its design. With
protection, I can't even change its value
And would I ever be able to mix symbols and text?

Any help greatly appreciated.

 

[Debra Dalgleish]

In this example, I used cell D2 for the dropdown list. You can change it
to the address of the cell on your worksheet:

Select cell D2
Format it with the Symbol Font
Choose Data>Validation
From the Allow dropdown, choose List
In the Source box, type: Hearts,Clubs,Diamonds,Spades,NT
Click OK

Right-click on the sheet tab, and choose ViewCode
Where the cursor is flashing, paste in the following code:

'=================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$D$2" Then
Application.EnableEvents = False
Select Case LCase(Target.Value)
Case "hearts"
Target.Value = Chr(169)
Case "clubs"
Target.Value = Chr(167)
Case "diamonds"
Target.Value = Chr(168)
Case "spades"
Target.Value = Chr(170)
Case "nt"
Target.Value = "NT"
Case Else
Target.Value = ""
End Select
End If
Application.EnableEvents = True
End Sub
'==================================

Press Alt+Q to return to Excel
Select a suit from the dropdown list, and the symbol will be displayed.

 

[Dave Peterson]

Another option.

Put a combobox from the control toolbox toolbar on your worksheet.

You can rightclick on the combobox and select properties. Change the font to
Symbol. And assign the listfill range to a nice group of 4 cells.

I put =char(167) through =char(170) in A1:A4. I even formatted that range as
Symbol (but not required).

Put the linked cell where you like (right under the combobox?).

 

[green biro]

Thank you Debra.
A very neat solution.

GB

In this example, I used cell D2 for the dropdown list. You can change it
to the address of the cell on your worksheet:

Select cell D2
Format it with the Symbol Font
Choose Data>Validation
From the Allow dropdown, choose List
In the Source box, type: Hearts,Clubs,Diamonds,Spades,NT
Click OK

Right-click on the sheet tab, and choose ViewCode
Where the cursor is flashing, paste in the following code:

'=================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$D$2" Then
Application.EnableEvents = False
Select Case LCase(Target.Value)
Case "hearts"
Target.Value = Chr(169)
Case "clubs"
Target.Value = Chr(167)
Case "diamonds"
Target.Value = Chr(168)
Case "spades"
Target.Value = Chr(170)
Case "nt"
Target.Value = "NT"
Case Else
Target.Value = ""
End Select
End If
Application.EnableEvents = True
End Sub
'==================================

Press Alt+Q to return to Excel
Select a suit from the dropdown list, and the symbol will be displayed.

 

[green biro]

Thanks for your reply and interest.
I went for Debra's solution as it seemed a bit slicker than introducing
controls.
Also, I'm a bit stumped on getting a listbox to actually work. I would like
to know (more for future reference) how I get it to be 'active' (see my
previous comments re sheet protection).

Thanks again.

GB

 

[Dave Peterson]

I put a listbox from the control toolbox toolbar on a worksheet. I used a
linked cell on the same sheet.
Then I protected the worksheet:
If that linked cell were locked, xl yelled.
If that linked cell were not locked, it worked fine.

Same thing with the listbox from the Forms toolbar.

 

[green biro]

Well I'm really missing a trick somewhere...


I create my list (though it's not attached to a cell in the same way as the
dropdown for data validation)

I update the linked cell property (though I'm not sure what that does)

I update the fill range property as a group of populated cells on the same
worksheet.

Now the list acquires both horizontal and vertical scroll bars. The
horizontal scroll bar only goes away if I increase the width to 80.

When I float the cursor over the list box, I just get the cross-arrows to
amend it but can't actually 'use' it (i.e. change / select values)

If I then protect the worksheet then there's absolutely nothing I can do
with it.....


Does anybody know of any nice web pages that offer an introduction on usage
of controls in worksheets?

 

[Dave Peterson]

The linkedCell is used to return the chosen value from the control (listbox).

Make sure your worksheet is unprotected (just for testing).
Make sure you are not in DesignMode (click on that icon on the control toolbar)
to toggle it.

now select an item in the Listbox.

Watch what happened to the linkedcell. It should inherit your selected item.

If you have that cell locked and the worksheet protected, then this cell can't
change.

Unprotect the worksheet, unlock the cell, reprotect the worksheet and try it
out.

===
If you want to play around with a test listbox, try this:

Go into design mode
rightclick|properties
Look at the columnwidths property and the width property.

If the columnwidths is larger than the width, I get that horizontal scroll bar.

===
I don't have a site for using these controls on a worksheet. But these controls
are also used on Userforms. You may want to browse through a book or two next
time you're at the bookstore.

For excel books, Debra Dalgleish has a big list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. I think that John Green
(and others) is nice, too.

 

[green biro]

Thank you very much.

I see now that my biggest oversight was not turning off design mode - I knew
it had to be something simple.

Thanks for the steer and all the other information.

GB

 

[Dave Peterson]

Glad you're closer!

Thank you very much.

I see now that my biggest oversight was not turning off design mode - I knew
it had to be something simple.

Thanks for the steer and all the other information.

GB