Ok, I know I have seen this before but I can't seem to locate an answer for this. I am using a combo box from the forms menu and I need to have the macro set the .listfillrange to a value based on the last cell that contains data. The actual control is on a different sheet than the range of data that will fill it. Here is what I have right now but its not working, and like I said I know I have seen this before, but I just can't seem to find the answer

Sub ComboBoxFill(
Dim LastRow As Lon

Sheets("MenuControls").Selec
LastRow = Sheets("DataSheet").Cells(Rows.Count, "I").End(xlUp).Ro
ActiveSheet.Shapes("Drop Down 16").Selec
With Selectio
.ListFillRange = "DataSheet!I2:I" & LastRo
.LinkedCell = "DataSheet!$G$2
.DropDownLines = LastRow -
End Wit
End Su

Any help would be great, this is just frustrating me, and I know somewhere I had the solution before

Tim

Tim,

What is not working. It seems fine to me. What are you getting?

--

HTH

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

"Tim Halligan" wrote in message
...
Ok, I know I have seen this before but I can't seem to locate an answer
for this. I am using a combo box from the forms menu and I need to have the
macro set the .listfillrange to a value based on the last cell that contains
data. The actual control is on a different sheet than the range of data
that will fill it. Here is what I have right now but its not working, and
like I said I know I have seen this before, but I just can't seem to find
the answer.


Sub ComboBoxFill()
Dim LastRow As Long

Sheets("MenuControls").Select
LastRow = Sheets("DataSheet").Cells(Rows.Count, "I").End(xlUp).Row
ActiveSheet.Shapes("Drop Down 16").Select
With Selection
.ListFillRange = "DataSheet!I2:I" & LastRow
.LinkedCell = "DataSheet!$G$2"
.DropDownLines = LastRow - 1
End With
End Sub

Any help would be great, this is just frustrating me, and I know somewhere
I had the solution before.

Tim