Combo Box wrong lookup range.
 

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

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

Combo Box wrong lookup range.
 

Private Sub ComboBox1_Change()

With Worksheets("Sheet2")
.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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"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