Variable Input Range for List Box VBA

[GreenyMcDuff]

Hey guys

I have a sheet with a 5 list boxes in.

I want a user to be able to select from this list. The problem is I don't know how to make the input range a variable that I can define in VBA.

Should be fairly simply... I hope.

Example:

in "Sheet1" I have a list of 10 names (Range("A1:A10"))

The drop down box will have the 10 names in it

This list then changes to 15 names (Range("A1:A15"))

After the user has pressed a refresh button I want the list box now to have those 15 names in it.


I have the code to find the size of the range:

Sub Fund_Names()
Dim Name_Count As Integer
Dim LastRow As Integer
Dim Name_Range As Range
    With Sheets("Sheet1")
 
        .LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        .Name_Count = LastRow
        .Name_Range = Range("A1:A" & Name_Count)
 
    End With
 
End Sub

Just need to assign it to the list box, and I'm not sure how :S

Thanks

 

[GreenyMcDuff]

OK well, I managed to find out after having forgotten about the record macro button :S

Still having difficulty syntaxing this part correctly:

.ListFillRange = "Sheet1'! A12:A &LastRow&"

Any ideas? LastRow is an Integer Variable

Thanks

 

[John_w]

Try:

.ListFillRange = "Sheet1!A1:A" & LastRow