Increase the list fill range with vba

[dpaton05]

How do I increase the list fill range so that when I add items to my list, a combo box that has a list of the items in it is updated automatically. The Listfillrange property of my combo box is YPNames!$A$2:$A48 but I want it to be A2 to the bottom of the list on YPNames.

 

[dpaton05]

I tried that link you sent but it didn't seem to work. The named range refers to =OFFSET(Tracker!$A$2,0,0,COUNTA(Tracker!$A:$A),1)
but that doesn't do anything.

 

[Michael M]

Did for me !!
I added 3 names via the Add Young Person button
When I clicked on the dropdown arrow for the ComboBox the names were there !

 

[dpaton05]

I deleted all the names from the list and tried to add a name. All that was left in the list is the header in A1. The file gets created with the name that is entered but I get an error "Could not set the list property. Invalid property array index." If I press debug, this line is highlighted

Tracker.lboYP.List = YP.Range("A2:A" & Lastrow).Value

in this procedure
Sub PopLB(TrackerWB As Workbook)
Dim Lastrow As Long
TrackerWB.Activate
Lastrow = YP.Cells(Rows.Count, "A").End(xlUp).Row
Tracker.lboYP.List = YP.Range("A2:A" & Lastrow).Value
End Sub

 

[Michael M]

So maybe you need to modify the code

Sub PopLB(TrackerWB As Workbook)
Dim Lastrow As Long
TrackerWB.Activate
Lastrow = YP.Cells(Rows.Count, "A").End(xlUp).Row
If Lastrow=1 then Lastrow=Lastrow+1
Tracker.lboYP.List = YP.Range("A2:A" & Lastrow).Value
End Sub

But do you need that code if the list automatically updates ???
OR even...

Sub PopLB(TrackerWB As Workbook)
Dim Lastrow As Long
TrackerWB.Activate
Lastrow = YP.Cells(Rows.Count, "A").End(xlUp).Row
If Lastrow=1 
MsgBox "There are no names on the list"
exit sub
end if
Tracker.lboYP.List = YP.Range("A2:A" & Lastrow).Value
End Sub

 

[dpaton05]

I get permission denied with the last line highlighted.

 

[Michael M]

But why do you need that code ??
The list is updated when you add a new person ??

 

[dpaton05]

If I comment out the PopLB code, the combo box and list box do not get updated. The list of names in the sheet YPNames is being updated each time I execute the code but the new names are not added to the list. 2 of the initial names I entered have been added but no new names have been added for a while. In the name manager, tblYPNames has a {...} in the value but if I click edit and put the cursor in the refers to field, the entire list of names is selected, including the new names that have been added. Although, the selection goes to one row below the last item in the list.

 

[dpaton05]

Not sure why this happened Michael, I deleted all the names and tried to add in one at a time and it is working just as you described it to be. I will just get my boss to manually add the names in as that seems to work. Thanks for your help.

 

[Michael M]

See my link in your Inbox

 

[dpaton05]

Thanks Michael but that is not quite what I need. I have done it though.

I am getting better at programming vba, yay