sorry i did not see that post.... I am working on it now. Thanks Bro.

"Dave Peterson" wrote:

Only what I suggested before.

Shawnn wrote:

First, thanks for all the help! Next, I got the multiple cells to work as
long as they are using the same list. I am having a problem getting other
cells to do it for different lists.

Here is my code:::

Private Sub Worksheet_Change(ByVal Target As Range)

Dim res As Variant
If Target.Cells.Count 1 Then
Exit Sub
End If
If Intersect(Target, Me.Range("N7, AE7")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Worksheets("Sheet2")
res = Application.Match(Target.Value, .Range("Weekday"), 0)
If IsError(res) Then
MsgBox "Design error!" 'this shouldn't happen
Else
Target.Value = .Range("Weekday").Offset(0, 1)(res).Value
End If
End With
Application.EnableEvents = True
End Sub
Private Sub object_Change()

Dim res As Variant
If Target.Cells.Count 1 Then
Exit Sub
End If
If Intersect(Target, Me.Range("AI9")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Worksheets("Sheet2")
res = Application.Match(Target.Value, .Range("Security"), 0)
If IsError(res) Then
MsgBox "Design error!" 'this shouldn't happen
Else
Target.Value = .Range("Security").Offset(0, 1)(res).Value
End If
End With
Application.EnableEvents = True
End Sub

As you can see, I want AI9 to read from the list called security, not
weekday. I will need to repeat this for another 20 or so cells... any advise?

"Dave Peterson" wrote:

I'd try changing this line:

If Intersect(Target, Me.Range("N8")) Is Nothing Then
to something like:
If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then

Shawnn wrote:

Ok I will try that. In the mean time... how do i do this for multiple cells
and lists? Lets just say I have 10 different areas i need to use this code.
How would I set that up?

"Dave Peterson" wrote:

First, merged cells cause nothing but grief. I do my best to avoid them.

But I merged N8:P8 and this seemed to work ok for me. I also named the range
List (not DID). In xl2007, DID will look like a column, so I wouldn't use that.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim res As Variant

If Target.Cells.Count 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("N8")) Is Nothing Then
Exit Sub
End If

If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Worksheets("Sheet2")
res = Application.Match(Target.Value, .Range("List"), 0)
If IsError(res) Then
MsgBox "Design error!" 'this shouldn't happen
Else
Target.Value = .Range("list").Offset(0, 1)(res).Value
End If
End With

Application.EnableEvents = True

End Sub




Shawnn wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson