Combo Box wrong lookup range.
Add the WITH...END WITH as I did below...change the word target for the name of your sheet where you want to post the data. Add the spot "." before each of the "Cells"
Private Sub ComboBox1_Change()
with Worksheets("target")
.Cells(11, 2).Value = Mid(ComboBox1.Value, 4)
.Cells(12, 2).Value = Mid(ComboBox1.Value, 1, 2)
.Cells(13, 2).Value = Cells(ComboBox1.ListIndex + 2, 3)
.Cells(14, 2).Value = Cells(ComboBox1.ListIndex + 2, 4)
End With
End Sub
--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
"Pal" wrote in message news:cR50c.415449$I06.4623200@attbi_s01...
I have a combo Box on one sheet that references a range of data on another
sheet.
The lookup works fine.
But the code attached to it does not. It returns values on the same page the
list box is located
and I want it to pull the data from the linked cell range on the other
sheet.
How can I fix this?
Thanks
Pal
Private Sub ComboBox1_Change()
Cells(11, 2).Value = Mid(ComboBox1.Value, 4)
Cells(12, 2).Value = Mid(ComboBox1.Value, 1, 2)
Cells(13, 2).Value = Cells(ComboBox1.ListIndex + 2, 3)
Cells(14, 2).Value = Cells(ComboBox1.ListIndex + 2, 4)
End Sub
|