Help...Using dropdown list

[modicon2]

Greetings,
I need to select an item from a dropdown list but display a different
name/number.
For example:
My dropdown list contains the following items:
Project in town
Project out of town
Project on the road
Projeect anywhere

I want to select the dropdown list and see the items listed above but
when I select an item, I want just a number to showup in the cell.

exm1: If I selected "Project in town" from dropdown, then a "1" would
be displayed in the cell.

exm2: If I selected "Project out of town" from dropdown, then a "2"
would be displayed in the cell.

I can make a list and set the dropdown to display that list by using
Data..Validation..allow:list...then select data range.

Thanks!

 

[Otto Moehrbach]

When you setup the Data Validation, uncheck the "Show error alert after
invalid data is entered" box in the "Error Alert tab".
Then the following macro will do what you want. Note that this is a
worksheet macro and must be placed in the sheet module of the sheet that has
the drop-down cell. I assumed the drop-down cell is E2. To access the
sheet module, right-click on the sheet tab, select View Code. Paste this
macro into that module. "X" out of the module to return to the worksheet.
HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("E2")) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value
Case "Project in town": Target.Value = 1
Case "Project out of town": Target.Value = 2
Case "Project on the road": Target.Value = 3
Case "Projeect anywhere": Target.Value = 4
End Select
Application.EnableEvents = True
End If
End Sub

 

[scottfoxall]

OK, Follow this step by step

First

VIEW > TOOLBARS > CONTROL TOOLBOX

A small toolbar is activated. Click the triangle to go into design mode.

Drag and drop a combobox (Drop down) onto the spread sheet.

Then select the Combo and right click > properties.

Then look for listFillRange and type the data range in for the drop down,
example A1:A15

Then

Double click the combobox

You should see

Private Sub ComboBox1_Change()


End Sub


Paste this code inbetween the above comments so it looks like:

Private Sub ComboBox1_Change()
Dim Project,Count,Search

Project = ComboBox1.Value
Count = 1

While Search = "ON"
If Project = Range("A1").Offset(Count, 0).Value Then
Range("B5").Value = Count
Search = "OFF"
Else
If Project = "" Then
Search = "OFF"
End If
Count = Count + 1
End If
Wend

End Sub

The above code assumes you list range is starts at A1

Edit this to your actual start of list

It also puts the number in to cell B5

Again, edit this to your required destination

Regards

 

[scottfoxall]

Sorry

Missed this script under Count=1

Search="ON"

And Count=1 should be Count=0

So it looks like this, and it works!

'--------------------------------------------------------------------
Private Sub ComboBox1_Change()

Project = ComboBox1.Value
Count = 0
Search = "ON"

While Search = "ON"
If Project = Range("A1").Offset(Count, 0).Value Then
Range("B5").Value = Count
Search = "OFF"
Else
If Project = "" Then
Search = "OFF"
End If
Count = Count + 1
End If
Wend

End Sub
'------------------------------------------------------------

 

[paul]

even easier
forms toolbar
combo box
click on it,click where you want it on the sheet and drag it to the size you
want
right click
format control
input list range and number of lines you want to show
choose a cell somewhere for a cell link
when you select item 1 on the drop down the cell link will show 1
and so on