Not sure even how to phrase this question, and not sure whether this should
be handled programmatically, or with some existing function in excel, but
here goes:

I need to create a drop down box that actually presents two items on each
"row" of the dropdown, but when the user selects one, it returns only on
value (say, first one). Example combo box:

CLASS A This project is a complex project that will last more than 1 year
CLASS B This project is a medium complex project that will last < 1 year
CLASS C This is a relatively simply project lasting less than 1 year.

In the above, I want the user to see the description portion of the drop
down, as well as the CLASS "X" result, which is what I really want to
capture. If they had selected the first item I would want to see CLASS A in
that cell (not the description).

This make any sense? Does this need to be handled with a function of sorts
or must I program this. If the latter, any suggestions on easiest approach?

Thanks!

pat

Thanks, Lloyd..that works!

"Simon Lloyd" wrote:


Hi if CLASS A This project is a complex project that will last more than
1 year
CLASS B This project is a medium complex project that will last < 1
year
CLASS C This is a relatively simply project lasting less than 1 year.
appears anywhere in the range shown in my code it will be trimmed down
to just the Class X so paste the code below into the worksheet module
(dont forget to change the range to as much of the sheet as you want)
and try pasting any of your list in to a cell you will find it gets
cut down to your requirement!

Hope it helps,
Regards,
Simon

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mycell As Range
Dim rng As Range
Set rng = Range("A2:C10")
For Each mycell In rng.Cells
If LCase(mycell.Value) Like LCase("Class A*") Then
mycell.Value = Left(mycell.Value, Len("This project is a complex
project that will last more than 1 year") - 58)
ElseIf LCase(mycell.Value) Like LCase("Class B*") Then
mycell.Value = Left(mycell.Value, Len("This project is a medium
complex project that will last less than 1 year") - 65)
ElseIf LCase(mycell.Value) Like LCase("Class C*") Then
mycell.Value = Left(mycell.Value, Len(" This is a relatively simply
project lasting less than 1 year.") - 55)
ElseIf mycell.Value = "" Then

End If
Next mycell

End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=566863

I finally had to give up on this (sorry). Every time i pasted it into a
module, for whatever reason, VBA did not like it (turned red and gave me
compiler errors). I am sure it was a formatting issue, that I simply do not
understand. I have written modules before...just wondering I this works in
excel, or not, as formatted. Ideas?

Thanks

Pat

"Simon Lloyd" wrote:


Hi if CLASS A This project is a complex project that will last more than
1 year
CLASS B This project is a medium complex project that will last < 1
year
CLASS C This is a relatively simply project lasting less than 1 year.
appears anywhere in the range shown in my code it will be trimmed down
to just the Class X so paste the code below into the worksheet module
(dont forget to change the range to as much of the sheet as you want)
and try pasting any of your list in to a cell you will find it gets
cut down to your requirement!

Hope it helps,
Regards,
Simon

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mycell As Range
Dim rng As Range
Set rng = Range("A2:C10")
For Each mycell In rng.Cells
If LCase(mycell.Value) Like LCase("Class A*") Then
mycell.Value = Left(mycell.Value, Len("This project is a complex
project that will last more than 1 year") - 58)
ElseIf LCase(mycell.Value) Like LCase("Class B*") Then
mycell.Value = Left(mycell.Value, Len("This project is a medium
complex project that will last less than 1 year") - 65)
ElseIf LCase(mycell.Value) Like LCase("Class C*") Then
mycell.Value = Left(mycell.Value, Len(" This is a relatively simply
project lasting less than 1 year.") - 55)
ElseIf mycell.Value = "" Then

End If
Next mycell

End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=566863

Hi Patk,
I copied and pasted my original code (the version in my first reply t
you) in to a worksheet module with no problems, however if you copy an
paste the code as seen in your reply to me you will see that the code i
word wrapped so the lines are split!

Try again and let me know.

Regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=56686