I have several areas that require a code similar to a state abbreviation.
For example the word Statutory Burglary mast be recorded as SB. So I have
set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the
abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations
and so on.

I used the DV dropdown in my document cell N8 (which is a merged cell) to
show the list from A1:A10. Once the ull name is selected I need it to show
the abbreviation in the form. I need this for many areas in my report.

I used the following code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 14 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Sheet2").Range("B1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

I would be happy to send you a copy if it will help.

"Dave Peterson" wrote:

Nope.

Maybe you could share some details.

The address of the range that holds the names and the id's.
The cell that's getting the data|validation.
And the code that you tried.

Shawnn wrote:

I would use an adjacent cell to make this project much easier on me.
Unfortunately I have to follow strict state reporting guidelines. With that
being said, I have tried for several hours to duplicate the example in the
download you provided but I keep getting errors. The example is exactly what
I want to do though. Do you know of a step by step guide?

"Dave Peterson" wrote:

You could use some code to replace the name with the id number.

But I would would use an adjacent cell so that I could display both.

If A1 contained the name (a cell with data|validation???), then in B1:
=if(a1="","",vlookup(a1,sheet2!a:b,2,false)

Where Sheet2 contained the list of names in column A and the ID's in column B.

But if you want, Debra Dalgleish has a sample workbook he
http://contextures.com/excelfiles.html#DataVal
Look for:
DV0004 - Data Validation Change -- Select a Product from the Data Validation
list; an event procedure changes the product name to a product code.
DataValCode.zip 8 kb



Shawnn wrote:

I have a form that uses a dropdown list to show employee names. What I want
to do is allow the names to be selected in the dropdown list but the employee
number to actually be inserted into the form. Is this even an option?

--

Dave Peterson


--

Dave Peterson