If you're using xl97, then this won't work.
I created a named range called myList that was used for the data|validation
rules.
I rightclicked on the worksheet tab that held the cell with data|validation. I
selected View Code and pasted this into the code window:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
'on cell at a time
If Target.Cells.Count > 1 Then Exit Sub
'only look at A1 -- where the data|validation cell is
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
res = Application.VLookup(Target.Value, _
Worksheets("sheet2").Range("mylist").Resize(, 2), 2, False)
Application.EnableEvents = False
If IsError(res) Then
'this shouldn't happen
Target.Value = "Missing"
Else
Target.Value = res
End If
errHandler:
Application.EnableEvents = True
End Sub
=======
Change A1 to the cell's address that has that data|validation.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/Events.aspx
David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm
And the macro will not work if the user disables macros or disables events.
Okay can you tell me another way to do what I need to do?
I want a list box, I want to choose from column a from another sheet, I want
to populate the same cell with the corresponding row from column b. Is this
possible?
