| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop down lists
Hi
I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
Steve,
Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
This may sound daft but there are other cells that all line up to make the
whole sheet look nice. The other option is can you select a value from the list but have a value from another list put in the cell e.g Col A Col B ------ ------- Deliver DL Customer Collect CC Hand Deliver HD Etc I would have Col A in the drop down but want to put the corresponding value in Col B. Hope this makes sense. Many thanks -- Steve R "Duncan" wrote: Steve, Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
Steve,
Do the validation for 2 letter codes and then put this code in, (The code would be long and messy and look something like this,) Private Sub Worksheet_Change(ByVal Target As Range) Set Rng = Range("C4") If Target = Rng Then If Target.Value = "HD" Then Target.Value = "Hand Deliver" End If If Target.Value = "DL" Then Target.Value = "Deliver" End If End If End Sub And you would have to add all of the ifs in for each name. There will be someone out there with a more efficient way because I am only a beginner, but this is how I would do it as not sure how to achieve it without code. Let us know how you get on with this. Duncan Steve wrote: This may sound daft but there are other cells that all line up to make the whole sheet look nice. The other option is can you select a value from the list but have a value from another list put in the cell e.g Col A Col B ------ ------- Deliver DL Customer Collect CC Hand Deliver HD Etc I would have Col A in the drop down but want to put the corresponding value in Col B. Hope this makes sense. Many thanks -- Steve R "Duncan" wrote: Steve, Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
Steve,
I forgot to mention, Although the data validation will disallow invalid entries (anything other than HD or DL or etc) that the user attempts to type in, it will still allow the code to change the cell value, I dont know exactly why but VBA must bypass the validation which is good in this instance. Duncan Duncan wrote: Steve, Do the validation for 2 letter codes and then put this code in, (The code would be long and messy and look something like this,) Private Sub Worksheet_Change(ByVal Target As Range) Set Rng = Range("C4") If Target = Rng Then If Target.Value = "HD" Then Target.Value = "Hand Deliver" End If If Target.Value = "DL" Then Target.Value = "Deliver" End If End If End Sub And you would have to add all of the ifs in for each name. There will be someone out there with a more efficient way because I am only a beginner, but this is how I would do it as not sure how to achieve it without code. Let us know how you get on with this. Duncan Steve wrote: This may sound daft but there are other cells that all line up to make the whole sheet look nice. The other option is can you select a value from the list but have a value from another list put in the cell e.g Col A Col B ------ ------- Deliver DL Customer Collect CC Hand Deliver HD Etc I would have Col A in the drop down but want to put the corresponding value in Col B. Hope this makes sense. Many thanks -- Steve R "Duncan" wrote: Steve, Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
Thanks Duncan
I will have a go at this. Appreciate your time -- Steve R "Duncan" wrote: Steve, Do the validation for 2 letter codes and then put this code in, (The code would be long and messy and look something like this,) Private Sub Worksheet_Change(ByVal Target As Range) Set Rng = Range("C4") If Target = Rng Then If Target.Value = "HD" Then Target.Value = "Hand Deliver" End If If Target.Value = "DL" Then Target.Value = "Deliver" End If End If End Sub And you would have to add all of the ifs in for each name. There will be someone out there with a more efficient way because I am only a beginner, but this is how I would do it as not sure how to achieve it without code. Let us know how you get on with this. Duncan Steve wrote: This may sound daft but there are other cells that all line up to make the whole sheet look nice. The other option is can you select a value from the list but have a value from another list put in the cell e.g Col A Col B ------ ------- Deliver DL Customer Collect CC Hand Deliver HD Etc I would have Col A in the drop down but want to put the corresponding value in Col B. Hope this makes sense. Many thanks -- Steve R "Duncan" wrote: Steve, Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
Duncan
Your a genious. Many thanks -- Steve R "Duncan" wrote: Steve, Do the validation for 2 letter codes and then put this code in, (The code would be long and messy and look something like this,) Private Sub Worksheet_Change(ByVal Target As Range) Set Rng = Range("C4") If Target = Rng Then If Target.Value = "HD" Then Target.Value = "Hand Deliver" End If If Target.Value = "DL" Then Target.Value = "Deliver" End If End If End Sub And you would have to add all of the ifs in for each name. There will be someone out there with a more efficient way because I am only a beginner, but this is how I would do it as not sure how to achieve it without code. Let us know how you get on with this. Duncan Steve wrote: This may sound daft but there are other cells that all line up to make the whole sheet look nice. The other option is can you select a value from the list but have a value from another list put in the cell e.g Col A Col B ------ ------- Deliver DL Customer Collect CC Hand Deliver HD Etc I would have Col A in the drop down but want to put the corresponding value in Col B. Hope this makes sense. Many thanks -- Steve R "Duncan" wrote: Steve, Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
Ah, Can I do this for multiple cells?
I'm going to try using an If statement for the Range. Thanks -- Steve R "Duncan" wrote: Steve, Do the validation for 2 letter codes and then put this code in, (The code would be long and messy and look something like this,) Private Sub Worksheet_Change(ByVal Target As Range) Set Rng = Range("C4") If Target = Rng Then If Target.Value = "HD" Then Target.Value = "Hand Deliver" End If If Target.Value = "DL" Then Target.Value = "Deliver" End If End If End Sub And you would have to add all of the ifs in for each name. There will be someone out there with a more efficient way because I am only a beginner, but this is how I would do it as not sure how to achieve it without code. Let us know how you get on with this. Duncan Steve wrote: This may sound daft but there are other cells that all line up to make the whole sheet look nice. The other option is can you select a value from the list but have a value from another list put in the cell e.g Col A Col B ------ ------- Deliver DL Customer Collect CC Hand Deliver HD Etc I would have Col A in the drop down but want to put the corresponding value in Col B. Hope this makes sense. Many thanks -- Steve R "Duncan" wrote: Steve, Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drop down lists
Which would be your range of cells? (the only one I allowed for was C4
but you can do the same to as many cells as you wish) Have a go at adding other cells to the range to be changed, I am sure you will manage but if not post back with more details and I'll happily have a go for you. Regards Duncan Steve wrote: Ah, Can I do this for multiple cells? I'm going to try using an If statement for the Range. Thanks -- Steve R "Duncan" wrote: Steve, Do the validation for 2 letter codes and then put this code in, (The code would be long and messy and look something like this,) Private Sub Worksheet_Change(ByVal Target As Range) Set Rng = Range("C4") If Target = Rng Then If Target.Value = "HD" Then Target.Value = "Hand Deliver" End If If Target.Value = "DL" Then Target.Value = "Deliver" End If End If End Sub And you would have to add all of the ifs in for each name. There will be someone out there with a more efficient way because I am only a beginner, but this is how I would do it as not sure how to achieve it without code. Let us know how you get on with this. Duncan Steve wrote: This may sound daft but there are other cells that all line up to make the whole sheet look nice. The other option is can you select a value from the list but have a value from another list put in the cell e.g Col A Col B ------ ------- Deliver DL Customer Collect CC Hand Deliver HD Etc I would have Col A in the drop down but want to put the corresponding value in Col B. Hope this makes sense. Many thanks -- Steve R "Duncan" wrote: Steve, Can you not use the data validation and input the full text list, but resize the cell on the book so it is only big enough for 2 letters? Duncan Steve wrote: Hi I'm kind of hoping that not toom much programming is required here. What I am trying to do is have a drop down list (not on a form but using the validation option) where the options are for example: AN - Delivery CU - Customer collection etc However, what I want to appear in the cell is just the first two letters. Is this possible? Many thanks -- Steve R |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop down lists from multiple source lists | Excel Worksheet Functions | |||
| How to create 2 dependent drop down lists from 1 original drop dow | Excel Discussion (Misc queries) | |||
| Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
| how do I use one drop-list to modify another drop-lists options? | Excel Discussion (Misc queries) | |||
| Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
Hybrid Mode
