Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation using multiple columns
In a Data Validation dropdown, I need to display more than one column of
data, but only "store" the value from the first column? For example: Column A = ID Number (4 characters long) Column B = Last Name Column C = First Name Column D = Column A & & Column B & & Column C Column E = Data Validation where Source = $D1:$D20 When a user clicks on the Data Validation dropdown arrow, the dropdown would display the ID Number, Last Name, and First Name. But once a person is selected, only the ID Number would be "stored" in the cell. excelent was kind enough to provide me with the following macro: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("E6:E20")) Is Nothing Then Exit Sub Target = Left(Target, 4) End Sub Unfortunately, after I select a name from the dropdown list, the list stays open and Excel appears to lock up until I press the Esc key. Then I get a "Code execution has been interrupted" error message. Can anyone tell me how to modify the macro above so that it works properly? Any help would be greatly appreciated. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation using multiple columns
There's a sample file here that you could adapt:
http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0004 - Data Validation Change' Bob wrote: In a Data Validation dropdown, I need to display more than one column of data, but only "store" the value from the first column? For example: Column A = ID Number (4 characters long) Column B = Last Name Column C = First Name Column D = Column A & & Column B & & Column C Column E = Data Validation where Source = $D1:$D20 When a user clicks on the Data Validation dropdown arrow, the dropdown would display the ID Number, Last Name, and First Name. But once a person is selected, only the ID Number would be "stored" in the cell. excelent was kind enough to provide me with the following macro: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("E6:E20")) Is Nothing Then Exit Sub Target = Left(Target, 4) End Sub Unfortunately, after I select a name from the dropdown list, the list stays open and Excel appears to lock up until I press the Esc key. Then I get a "Code execution has been interrupted" error message. Can anyone tell me how to modify the macro above so that it works properly? Any help would be greatly appreciated. Bob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation using multiple columns
Debra,
Thanks for the tip! Since I am a VBA novice, may I ask you two quick questions? The range of my "lookup" table is K4:L12. So would I change your line of code from: Target.Value = Worksheets("Codes").Range("A1") to: Target.Value = Worksheets("Codes").Range("K4:L12")? Also, given my different lookup range, would the "0), 0)" in the line: ..Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0) remain the same? Thanks again for all your help. Regards, Bob "Debra Dalgleish" wrote: There's a sample file here that you could adapt: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0004 - Data Validation Change' Bob wrote: In a Data Validation dropdown, I need to display more than one column of data, but only "store" the value from the first column? For example: Column A = ID Number (4 characters long) Column B = Last Name Column C = First Name Column D = Column A & & Column B & & Column C Column E = Data Validation where Source = $D1:$D20 When a user clicks on the Data Validation dropdown arrow, the dropdown would display the ID Number, Last Name, and First Name. But once a person is selected, only the ID Number would be "stored" in the cell. excelent was kind enough to provide me with the following macro: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("E6:E20")) Is Nothing Then Exit Sub Target = Left(Target, 4) End Sub Unfortunately, after I select a name from the dropdown list, the list stays open and Excel appears to lock up until I press the Esc key. Then I get a "Code execution has been interrupted" error message. Can anyone tell me how to modify the macro above so that it works properly? Any help would be greatly appreciated. Bob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation using multiple columns
The line:
Target.Value = Worksheets("Codes").Range("A1") should change to refer to your starting cell: Target.Value = Worksheets("Codes").Range("K4") The line: .Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0) shouldn't have to change, assuming you have a sheet named codes, and a range named ProdList, which is the range of cells with the combined data, e.g. L4:L12 Bob wrote: Debra, Thanks for the tip! Since I am a VBA novice, may I ask you two quick questions? The range of my "lookup" table is K4:L12. So would I change your line of code from: Target.Value = Worksheets("Codes").Range("A1") to: Target.Value = Worksheets("Codes").Range("K4:L12")? Also, given my different lookup range, would the "0), 0)" in the line: .Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0) remain the same? Thanks again for all your help. Regards, Bob "Debra Dalgleish" wrote: There's a sample file here that you could adapt: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0004 - Data Validation Change' Bob wrote: In a Data Validation dropdown, I need to display more than one column of data, but only "store" the value from the first column? For example: Column A = ID Number (4 characters long) Column B = Last Name Column C = First Name Column D = Column A & & Column B & & Column C Column E = Data Validation where Source = $D1:$D20 When a user clicks on the Data Validation dropdown arrow, the dropdown would display the ID Number, Last Name, and First Name. But once a person is selected, only the ID Number would be "stored" in the cell. excelent was kind enough to provide me with the following macro: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("E6:E20")) Is Nothing Then Exit Sub Target = Left(Target, 4) End Sub Unfortunately, after I select a name from the dropdown list, the list stays open and Excel appears to lock up until I press the Esc key. Then I get a "Code execution has been interrupted" error message. Can anyone tell me how to modify the macro above so that it works properly? Any help would be greatly appreciated. Bob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation using multiple columns
Debra,
Thanks for all your help! I sincerely appreciate it. Regards, Bob "Debra Dalgleish" wrote: The line: Target.Value = Worksheets("Codes").Range("A1") should change to refer to your starting cell: Target.Value = Worksheets("Codes").Range("K4") The line: .Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0) shouldn't have to change, assuming you have a sheet named codes, and a range named ProdList, which is the range of cells with the combined data, e.g. L4:L12 Bob wrote: Debra, Thanks for the tip! Since I am a VBA novice, may I ask you two quick questions? The range of my "lookup" table is K4:L12. So would I change your line of code from: Target.Value = Worksheets("Codes").Range("A1") to: Target.Value = Worksheets("Codes").Range("K4:L12")? Also, given my different lookup range, would the "0), 0)" in the line: .Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0) remain the same? Thanks again for all your help. Regards, Bob "Debra Dalgleish" wrote: There's a sample file here that you could adapt: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0004 - Data Validation Change' Bob wrote: In a Data Validation dropdown, I need to display more than one column of data, but only "store" the value from the first column? For example: Column A = ID Number (4 characters long) Column B = Last Name Column C = First Name Column D = Column A & & Column B & & Column C Column E = Data Validation where Source = $D1:$D20 When a user clicks on the Data Validation dropdown arrow, the dropdown would display the ID Number, Last Name, and First Name. But once a person is selected, only the ID Number would be "stored" in the cell. excelent was kind enough to provide me with the following macro: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("E6:E20")) Is Nothing Then Exit Sub Target = Left(Target, 4) End Sub Unfortunately, after I select a name from the dropdown list, the list stays open and Excel appears to lock up until I press the Esc key. Then I get a "Code execution has been interrupted" error message. Can anyone tell me how to modify the macro above so that it works properly? Any help would be greatly appreciated. Bob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation across multiple columns | Excel Worksheet Functions | |||
Data Validation on multiple columns | Excel Discussion (Misc queries) | |||
Data Validation & multiple columns | Excel Discussion (Misc queries) | |||
Data validation for Multiple columns | Excel Discussion (Misc queries) |