Data Validation with description

staticbob

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Hi,

I would like to set-up data validation, which I know how to do.

By problem is tho, cell entries will just be a letter, A-P. Is it possible to also link a description cell into the validation drop down, but when selected only the letter is entered ??

eg,
A = Blue
B = Red
C = Green

Cell entry would be the letter only. This would save me putting input messages on the cells, which IMHO look a tad messy.

Thanks in advance,
Bob
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Try this:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Range("B1:B5"), Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Left(Target.Value, 1)
    Application.EnableEvents = True
End Sub

It assumes your data validation cells are in the range B1:B5 - change to suit.
 
Upvote 0
In your workbook create a vlookup list corresponding to the letters A to P in one column and your description in the second column. I have assumed columns A & B are used for this purpose.

Using data validation choose list and point the list to your second column.

On the sheet code add the following
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C As Range, R As Range
    
    On Error GoTo xit
    Set C = Target
    Set R = Columns("A:B")
    X = WorksheetFunction.VLookup(C, R, 2, 0)
    Target.Value = X
xit:
End Sub

When you choose a value from the dropdown list the code will execute BUT because you are going to substitute the list value for a letter then the code will execute a second time hence the need for an error trap.

HTH :)
 
Upvote 0
Thanks Andrew,

I also have another validation list, this time the cell entry would be a code, 3 - 5 characters, with an explantion as before. Obviously the left cmd only takes the first character.

How would I take everything to the left of = (there is always spaces each side of =)

EG,

MOP = Member of Public

Cheers
 
Upvote 0
Try (assuming there is always a space before the = sign):

Code: 
Target.Value = Left(Target.Value, Instr(1, Target.Value, " ") - 1)
 
Upvote 0
Goto Data -> Validation

here select the list and put the entry like A;Blue,B;Red,C;Green

Now in worksheet change event .. put this code:
Private Sub Worksheet_Change(ByVal Target As Range)

4 is the column where you want the valdiation.range

If Target.Column = 4 Then
'
Target.Value = Left(Target, 1)
End If

End Sub
 
Upvote 0
nisht said:
Goto Data -> Validation

here select the list and put the entry like A;Blue,B;Red,C;Green

Now in worksheet change event .. put this code:
Private Sub Worksheet_Change(ByVal Target As Range)

4 is the column where you want the valdiation.range

If Target.Column = 4 Then
'
Target.Value = Left(Target, 1)
End If

End Sub
Click to expand...

I got a compile error on the very first line of that code. And isn't it essentially the same as I have already posted, but recursive because Events are not disabled?
 
Upvote 0
Where did you put the code..

you need to put the code in your worksheet event suppose your worksheet name is "sheet1" then you need to select event for "sheet1" by going to VBE edit windows left combobox select select worksheet and in rightcombobox select events.
 
Upvote 0
nisht said:
Where did you put the code..

you need to put the code in your worksheet event suppose your worksheet name is "sheet1" then you need to select event for "sheet1" by going to VBE edit windows left combobox select select worksheet and in rightcombobox select events.
Click to expand...

I put the code in the module for the Worksheet. But as I said I got a compile error on the first line. It's because you forgot to comment it out.

What is the point of posting a solution that has already been provided over an hour earlier? Especially when the code doesn't actually work and is slower because it calls itself.
 
Upvote 0
Andrew Poulsom said:
nisht said:
Where did you put the code..

you need to put the code in your worksheet event suppose your worksheet name is "sheet1" then you need to select event for "sheet1" by going to VBE edit windows left combobox select select worksheet and in rightcombobox select events.
Click to expand...

I put the code in the module for the Worksheet. But as I said I got a compile error on the first line. It's because you forgot to comment it out.

What is the point of posting a solution that has already been provided over an hour earlier? Especially when the code doesn't actually work and is slower because it calls itself.
Click to expand...

The purpose is to provide a alternate solution to questionaire. sorry for not putting comments
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Data Validation Matching
Replies
3
Views
272
etaf
etaf
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
221
Peter_SSs
Peter_SSs
excelbytes
  • Solved
Data Validation Show Codes Only
2 3
Replies
20
Views
400
HighAndWilder
H
M
  • Solved
Input box triggered when specific value selected from data validation list.
Replies
5
Views
413
Peter_SSs
Peter_SSs
M
  • Question Question
Data Validation using VBA and Excel function
Replies
0
Views
413
ManUBlueJay
M

Forum statistics

Threads
1,224,583
Messages
6,179,673
Members
452,937
Latest member
Bhg1984

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top