Validation, Conditional Formatting and Font Types

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to create a situation where a user selects one of three options
from a validated drop-down list ("Improving", "Constant", "Declining"). This
bit is easy :-)

What I want the cell to display is an arrow representing the value selected
in the drop down (up arrow for increasing, right arrow for constant, down
arrow for decreasing).

I can't use a Conditional Formatting workaround in the cell, because I can't
select the Wingdings 3 font in the CF dialogue box and if I select a range of
pre-formatted cells in the validation dialogue box, the drop-down shows the
letter rather than the symbol I am trying to use (eg. the letter "k" for a
diagonal up arrow) which is meaningless to the user :-(

Can anyone make any suggestions as to how I can have a meanigful input
(either the words or the arrows, I don't care) that create just the arrows as
the output in the same cells?

This is 2003 flavour, if that makes any difference.

TIA

Dave
 
Hi Dave

I think the only way to achieve this is through code.
In your list used for DV, enter it as "k" (without the quotes) followed by a
couple of spaces and then the Text e.g.
k Improving

Then copy the code below> right click on your sheet with the DV in
place>View Code>Paste the code into the white pane that appears>press
Alt+F11 to go back to your Excel sheet.

What will appear in the dropdown list is the relevant letter, followed by
the description, but as soon as the user selects a value, the code will get
activated and will set the font for the first 2 characters to be Wingdings 3
with normal colour, but characters 3 onward will be Arial but with white on
white and will not show. If you want the words to show, change .colorindex =
2 to xlAutomatic

In my example, I chose cells B2:B20 to have the validation applied. Change
to suit your requirement.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
' change the range below from B2:B20 to suit your range
If Not Intersect(Target, Me.Range("B2:B20")) Is Nothing Then
On Error GoTo exiterror
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

With ActiveCell.Characters(Start:=1, Length:=2).Font
.Name = "Wingdings 3"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=3, Length:=30).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
End If

exiterror:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
On Error GoTo 0

End Sub
 
To create a named list of arrows, do the following on another worksheet:
Select a blank cell, press the Alt key, and on the number keypad, type
24, then press Enter
In the cell below, type Alt+26
In the next cell, type Alt+25
Select the three cells, and click in the Name box, to the left of the
formula bar.
Type a one word name, such as Arrows, then press Enter

To use the list for data validation:
Select the cells where you want the validation
Choose Data>Validation
For Allow, choose List
For the Source, type: =Arrows
Click OK
 
Both,

thanks for your responses - I'm going to use elemnts of both in various
parts of my solution.

Debra,

What is this character set (I'm onnly familiar with ASCII and it doesn't
seem to be that one) and do you know where I can find the full set listed as
I can think of lots of uses for this :-))))

Again,,
My thanks

Dave
 
Hi Deb

I'm glad I prefaced my response with "I don't think ...." <bg>
Very nice solution.
 
Back
Top