Data validation - display descriptions with the data

  • Thread starter Thread starter Laurence Lombard
  • Start date Start date
L

Laurence Lombard

Is there a way to have Data validation display a description next to the
data, but only insert the data into the activecell, similar to a Combobox?
For example display
1 Groceries
2 Car
3 Medical
but insert 1,2 or 3 into the cell.

Thanks
Laurence Lombard
 
Hi Laurence

this can only be done with VBA. See
http://www.contextures.com/DataValNameID.xls
for an example. To give you a short overview you have to do the
following:
1. In your data list for the listbox add a concatenated column of your
number and the name
2. Choose this concatenated column as data source for your list
3. Process the worksheet_change event and change the listbox entry to
your number
But look at the example, should guide you through these steps

HTH
Frank
 
Thank you very much.
Laurence
Frank Kabel said:
Hi Laurence

this can only be done with VBA. See
http://www.contextures.com/DataValNameID.xls
for an example. To give you a short overview you have to do the
following:
1. In your data list for the listbox add a concatenated column of your
number and the name
2. Choose this concatenated column as data source for your list
3. Process the worksheet_change event and change the listbox entry to
your number
But look at the example, should guide you through these steps

HTH
Frank
Click to expand...
 
You could use a helper cell and a formula (if the data|validation list came from
a nice range).

=match(a1,sheet2!$a$1:$a$10,0)

Where sheet2 A1:A10 contained the data validation list.
A1 was the cell with the Data|validation.

or even:
=if(a1="","",match(a1,sheet2!$a$1:$a$10,0))
 
Thank you for your reply. I have two questions.
1. Why do you use the Offset/Match statement instead of Vlookup - the latter
works as well and is simpler to understand.
2. Why is it necessary to Disable events for the Offset/Match function to
work. I notice that I get an error message if I comment out the
"Application.EnableEvents = False" line but do not understand what it means.

Thanks
Laurence


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
'Target.Value = Worksheets("Codes").Range("C1") _
' .Offset(Application.WorksheetFunction _
' .Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
ActiveCell.Value = Application.WorksheetFunction.VLookup _
(ActiveCell.Value, Worksheets("Codes").Range("B1:C5"), 2, 0)
Application.EnableEvents = True
End If

End Sub
 
I'm not Frank or Debra (author of the code that Frank posted).

But my guess is that some people use offset/match for its "generalism". If the
columns moved around so that the key column was to the right, then it would be
an easier tweak to change.

You'd have to do something more with =vlookup().

(but if you're designing the code and the worksheet, then I think it's pretty
much personal preference--unless you're returning lots of values from that row.
Find the matching row once and use it in the index() functions as many times as
you want.)

===

The "Application.EnableEvents = False" line doesn't actually make the
offset/match function work. It just tells excel to stop looking for changes in
the worksheet when the macro is making the change.

If you comment out that line and put a break point in on:
If Target.Cells.Count > 1 Then Exit Sub

You can watch the code while it runs.

You'll see that with "application.enableevents = false", you can make a change
using the data|validation dropdown and the new value is put into the cell.

But as soon as the new value (w/o the code number) is put into the cell, excel
says: "Hey, the sheet just changed--fire the worksheet_change event.

This time through, the value of the cell is "bookshelf" -- not "Bookshelf -- ID#
89307", so there's not a match in column B (.range("prodlist").

Then the application.worksheetfunction.match() bombs out and cause the code to
break.

But with "application.enableevents = false", excel just won't be watching for
changes. And the worksheet_event doesn't fire a second time (and so the blow up
is avoided).
 
Back
Top