Popilating a luser form listbox in vba

[Peter Thompson]

Hello,

I have been trying to run the code below to get a list box in a user formto be populated from a named range (called Requestors) in a worksheet (Report_Sheet) but I keep getting errors when I try and run it. The top few lines work fine but when it goes to the with statement it gets an error.

Can anyone see whats going wrong?

Private Sub UserForm_Initialize()
Dim vListSource As Variant

Dim wksRepSheet As Worksheet
Set wksRepSheet = Worksheets("Report_Sheet")

With wksRepSheet.Names("Requestor")
vListSource = .RefersToRange.Value
End With

frmBriefing.ListBox1.List = vListSource

End Sub

 

[Norie]

Doesn't Refers to Range return the address of the named range.

Anyway, try this.

frmBriefing.ListBox1.List = Range("Requestor").Values

Or this.

frmBriefing.ListBox1.RowSource = "Requestor"

 

[Comfy]

or

Private Sub UserForm_Initialize()
Dim vListSource As Variant

Dim wksRepSheet As Range
Set wksRepSheet = ThisWorkbook.Names("Requestor").RefersToRange

With wksRepSheet
vListSource = .Value
End With

frmBriefing.ListBox1.List = vListSource

End Sub

 

[Peter Thompson]

Thanks,

Norje - your first suggestion didnt work, but the second one worked fine.

Comfe - your suggestion also worked.

The rowsource option appears easier. Is there any disadvantge to using this approach rather than the variant apprach?