If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Validation drop-down boxes
i know this is a long shot but i thought i'd try asking...
I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#2
|
|||
|
|||
Validation drop-down boxes
After a selection is made from the DV dropdown you can color the selection
using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#3
|
|||
|
|||
Validation drop-down boxes
i apolgize, i think my example was confusing because i used color names.
assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc. So any other options? Tami "Gord Dibben" wrote: After a selection is made from the DV dropdown you can color the selection using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#4
|
|||
|
|||
Validation drop-down boxes
You want when you select 01 that Swimwear appears in the same cell?
Select 02 and Jackets appears in the cell? You can do that with event code and a lookup table. Create the lookup table first. Then create the dropdown in A1 Now insert the code below to the sheet module by right-click on the sheet tab and "View Code". Copy/Paste into that module................Edit to suit then Alt + q to return to Excel. Private Sub Worksheet_Change(ByVal Target As Range) Dim Vals As Range Dim R As Range Dim RR As Range Set R = Me.Range("A1") 'DV dropdown cell Set Vals = Me.Range("P1:Q100") 'lookup table 'column P is list range for DV dropdown 'column Q has list of return numbers 'these could be on another worksheet 'Set Vals = Sheets("Sheet3").Range("P1:Q100") If Intersect(Target, R) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False On Error Resume Next For Each RR In Intersect(Target, R) 'Only check changed cells RR = Application.VLookup(RR.Value, Vals, 2, False) Next RR endit: Application.EnableEvents = True End Sub Gord On Sat, 19 Sep 2009 10:27:01 -0700, Tami wrote: i apolgize, i think my example was confusing because i used color names. assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc. So any other options? Tami "Gord Dibben" wrote: After a selection is made from the DV dropdown you can color the selection using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#5
|
|||
|
|||
Validation drop-down boxes
thanks much. unfortunately, i have about 17 fields (columns) that have Data
Validation and the file has about 500 rows. I was hoping there was an easier way to provide a description to the user instead of just seeing a list of valid cryptic codes. i've heard of combo boxes or control boxes?? "Gord Dibben" wrote: You want when you select 01 that Swimwear appears in the same cell? Select 02 and Jackets appears in the cell? You can do that with event code and a lookup table. Create the lookup table first. Then create the dropdown in A1 Now insert the code below to the sheet module by right-click on the sheet tab and "View Code". Copy/Paste into that module................Edit to suit then Alt + q to return to Excel. Private Sub Worksheet_Change(ByVal Target As Range) Dim Vals As Range Dim R As Range Dim RR As Range Set R = Me.Range("A1") 'DV dropdown cell Set Vals = Me.Range("P1:Q100") 'lookup table 'column P is list range for DV dropdown 'column Q has list of return numbers 'these could be on another worksheet 'Set Vals = Sheets("Sheet3").Range("P1:Q100") If Intersect(Target, R) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False On Error Resume Next For Each RR In Intersect(Target, R) 'Only check changed cells RR = Application.VLookup(RR.Value, Vals, 2, False) Next RR endit: Application.EnableEvents = True End Sub Gord On Sat, 19 Sep 2009 10:27:01 -0700, Tami wrote: i apolgize, i think my example was confusing because i used color names. assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc. So any other options? Tami "Gord Dibben" wrote: After a selection is made from the DV dropdown you can color the selection using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#6
|
|||
|
|||
Validation drop-down boxes
Correction
'column Q has list of return numbers...........should read ' column Q has list of apparel Gord On Sat, 19 Sep 2009 10:46:32 -0700, Gord Dibben gorddibbATshawDOTca wrote: You want when you select 01 that Swimwear appears in the same cell? Select 02 and Jackets appears in the cell? You can do that with event code and a lookup table. Create the lookup table first. Then create the dropdown in A1 Now insert the code below to the sheet module by right-click on the sheet tab and "View Code". Copy/Paste into that module................Edit to suit then Alt + q to return to Excel. Private Sub Worksheet_Change(ByVal Target As Range) Dim Vals As Range Dim R As Range Dim RR As Range Set R = Me.Range("A1") 'DV dropdown cell Set Vals = Me.Range("P1:Q100") 'lookup table 'column P is list range for DV dropdown 'column Q has list of return numbers 'these could be on another worksheet 'Set Vals = Sheets("Sheet3").Range("P1:Q100") If Intersect(Target, R) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False On Error Resume Next For Each RR In Intersect(Target, R) 'Only check changed cells RR = Application.VLookup(RR.Value, Vals, 2, False) Next RR endit: Application.EnableEvents = True End Sub Gord On Sat, 19 Sep 2009 10:27:01 -0700, Tami wrote: i apolgize, i think my example was confusing because i used color names. assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc. So any other options? Tami "Gord Dibben" wrote: After a selection is made from the DV dropdown you can color the selection using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#7
|
|||
|
|||
Validation drop-down boxes
sorry i once again made my request confusing...
i want the user to enter or select 02 from a drop doown list and the cell value would be 02,... i just feel like it would be nice for the user to know what 02 means by showing a drop down list like this: (a code with a brief description) 01-Swimwear 02-Pants 03-Tops 04-Jackets etc does that make sense? "Gord Dibben" wrote: Correction 'column Q has list of return numbers...........should read ' column Q has list of apparel Gord On Sat, 19 Sep 2009 10:46:32 -0700, Gord Dibben gorddibbATshawDOTca wrote: You want when you select 01 that Swimwear appears in the same cell? Select 02 and Jackets appears in the cell? You can do that with event code and a lookup table. Create the lookup table first. Then create the dropdown in A1 Now insert the code below to the sheet module by right-click on the sheet tab and "View Code". Copy/Paste into that module................Edit to suit then Alt + q to return to Excel. Private Sub Worksheet_Change(ByVal Target As Range) Dim Vals As Range Dim R As Range Dim RR As Range Set R = Me.Range("A1") 'DV dropdown cell Set Vals = Me.Range("P1:Q100") 'lookup table 'column P is list range for DV dropdown 'column Q has list of return numbers 'these could be on another worksheet 'Set Vals = Sheets("Sheet3").Range("P1:Q100") If Intersect(Target, R) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False On Error Resume Next For Each RR In Intersect(Target, R) 'Only check changed cells RR = Application.VLookup(RR.Value, Vals, 2, False) Next RR endit: Application.EnableEvents = True End Sub Gord On Sat, 19 Sep 2009 10:27:01 -0700, Tami wrote: i apolgize, i think my example was confusing because i used color names. assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc. So any other options? Tami "Gord Dibben" wrote: After a selection is made from the DV dropdown you can color the selection using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#8
|
|||
|
|||
Validation drop-down boxes
Do you really have 17x500 DV dropdowns?
If you want to send me a copy of your workbook by email I can have a look. Should be able to alter the code to cover 17 columns and 500 rows. gorddibbATshawDOTca Make the changes to send to my email. Gord On Sat, 19 Sep 2009 11:15:01 -0700, Tami wrote: thanks much. unfortunately, i have about 17 fields (columns) that have Data Validation and the file has about 500 rows. I was hoping there was an easier way to provide a description to the user instead of just seeing a list of valid cryptic codes. i've heard of combo boxes or control boxes?? "Gord Dibben" wrote: You want when you select 01 that Swimwear appears in the same cell? Select 02 and Jackets appears in the cell? You can do that with event code and a lookup table. Create the lookup table first. Then create the dropdown in A1 Now insert the code below to the sheet module by right-click on the sheet tab and "View Code". Copy/Paste into that module................Edit to suit then Alt + q to return to Excel. Private Sub Worksheet_Change(ByVal Target As Range) Dim Vals As Range Dim R As Range Dim RR As Range Set R = Me.Range("A1") 'DV dropdown cell Set Vals = Me.Range("P1:Q100") 'lookup table 'column P is list range for DV dropdown 'column Q has list of return numbers 'these could be on another worksheet 'Set Vals = Sheets("Sheet3").Range("P1:Q100") If Intersect(Target, R) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False On Error Resume Next For Each RR In Intersect(Target, R) 'Only check changed cells RR = Application.VLookup(RR.Value, Vals, 2, False) Next RR endit: Application.EnableEvents = True End Sub Gord On Sat, 19 Sep 2009 10:27:01 -0700, Tami wrote: i apolgize, i think my example was confusing because i used color names. assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc. So any other options? Tami "Gord Dibben" wrote: After a selection is made from the DV dropdown you can color the selection using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#9
|
|||
|
|||
Validation drop-down boxes
You could alwys use a message box that pops up when user selects an item.
That too would require code. Or without code, a VLOOKUP formula in an adjacent cell that returns the description. Gord On Sat, 19 Sep 2009 11:32:01 -0700, Tami wrote: sorry i once again made my request confusing... i want the user to enter or select 02 from a drop doown list and the cell value would be 02,... i just feel like it would be nice for the user to know what 02 means by showing a drop down list like this: (a code with a brief description) 01-Swimwear 02-Pants 03-Tops 04-Jackets etc does that make sense? "Gord Dibben" wrote: Correction 'column Q has list of return numbers...........should read ' column Q has list of apparel Gord On Sat, 19 Sep 2009 10:46:32 -0700, Gord Dibben gorddibbATshawDOTca wrote: You want when you select 01 that Swimwear appears in the same cell? Select 02 and Jackets appears in the cell? You can do that with event code and a lookup table. Create the lookup table first. Then create the dropdown in A1 Now insert the code below to the sheet module by right-click on the sheet tab and "View Code". Copy/Paste into that module................Edit to suit then Alt + q to return to Excel. Private Sub Worksheet_Change(ByVal Target As Range) Dim Vals As Range Dim R As Range Dim RR As Range Set R = Me.Range("A1") 'DV dropdown cell Set Vals = Me.Range("P1:Q100") 'lookup table 'column P is list range for DV dropdown 'column Q has list of return numbers 'these could be on another worksheet 'Set Vals = Sheets("Sheet3").Range("P1:Q100") If Intersect(Target, R) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False On Error Resume Next For Each RR In Intersect(Target, R) 'Only check changed cells RR = Application.VLookup(RR.Value, Vals, 2, False) Next RR endit: Application.EnableEvents = True End Sub Gord On Sat, 19 Sep 2009 10:27:01 -0700, Tami wrote: i apolgize, i think my example was confusing because i used color names. assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc. So any other options? Tami "Gord Dibben" wrote: After a selection is made from the DV dropdown you can color the selection using CF. But.........the individual entries cannot be colored within the dropdown. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 09:49:01 -0700, Tami wrote: i know this is a long shot but i thought i'd try asking... I have a spread sheet that has the validation drop down box pointing to a list in column A. column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc? |
#10
|
|||
|
|||
Validation drop-down boxes
On Sep 20, 6:32*am, Tami wrote:
sorry i once again made my request confusing... i want the user to enter or select 02 from a drop doown list and the cell value would be 02,... i just feel like it would be nice for the user to know what 02 means by showing a drop down list like this: * (a code with a brief description) 01-Swimwear 02-Pants 03-Tops 04-Jackets etc does that make sense? Try this. Make up your drop down validation list using 01, 02, 03 etc. Now format each of these with a custom format "Swimwear" or "01- Swimwear" if you prefer. The original codes are still stored internally but they just look different. Now the dropdown list gives the words and but the codes alone are entered in the cells. xt |
|
Thread Tools | |
Display Modes | |
|
|