I can't figure out listfillrange

[Guest]

I'm trying to create a listbox named ClientStatus from Control Toolbox
toolbar on worksheet 'Cover Info'. My list data is in column A of worksheet
'Data Formatting'. It won't let me enter anything in the ListFillRange under
Properties, and I can not for the life of me figure out how to use the
ListFillRange in VBA. (I'm new to all of this. Just learning by reading
other's answers.)

 

[Guest]

hi,
is the listfillrange on the same sheet as your listbox. if
it is on another sheet, it will "disapear" on you or
reference the range on the same sheet with the list box.

 

[Guest]

yes, it is on another sheet, and it does "disappear" on me. How do I get it
to pull from another sheet?

 

[Dave Peterson]

I could manually type in the range that I wanted:

'Data Formatting'!A1:A13

(note the single quotes and exclamation mark)

In code:

With Worksheets("Cover Info").ListBox1
.ListFillRange = Worksheets("data formatting").Range("a1:a13") _
.Address(external:=True)
End With

 

[Guest]

Thanks so much, Dave! It's embarrassing that it was that simple, but when
you can't get it to work it seems so hard!

 

[Guest]

So how do you format that using UNC ("\\server\path\filename) to reference a
remote file (stored on a different drive) as the source? I keep getting a
"Subscript out of range" error...

 

[Guest]

Good question! I hit the same problem later, and I haven't been able to
resolve it.