Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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! -- modicon2 ------------------------------------------------------------------------ modicon2's Profile: http://www.excelforum.com/member.php...fo&userid=4158 View this thread: http://www.excelforum.com/showthread...hreadid=573943 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "modicon2" wrote in message ... 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! -- modicon2 ------------------------------------------------------------------------ modicon2's Profile: http://www.excelforum.com/member.php...fo&userid=4158 View this thread: http://www.excelforum.com/showthread...hreadid=573943 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "modicon2" wrote: 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! -- modicon2 ------------------------------------------------------------------------ modicon2's Profile: http://www.excelforum.com/member.php...fo&userid=4158 View this thread: http://www.excelforum.com/showthread...hreadid=573943 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 '------------------------------------------------------------ "modicon2" wrote: 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! -- modicon2 ------------------------------------------------------------------------ modicon2's Profile: http://www.excelforum.com/member.php...fo&userid=4158 View this thread: http://www.excelforum.com/showthread...hreadid=573943 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Preventing manual entry in dropdown list ... possible? | Excel Discussion (Misc queries) | |||
How to Change List Based on Value Chosen in Another List | Excel Worksheet Functions | |||
Entering data from dropdown list | Excel Discussion (Misc queries) | |||
HELP! Nesting IF Statements/Dependent Dropdown List | Excel Worksheet Functions | |||
How to show dropdown list from another workbook without running it | Excel Discussion (Misc queries) |