ListBox Populate and Refresh

[ciaran.davison]

Hi there,

I want to populate a listbox from a range with a varying number of
cells that are downloaded using a webquery. I've set this up
referencing the range by name in the listbox properties but am having a
few problems. These are:

1. The items do not refresh unless I change sheets - when I return to
the listbox sheet, the contents have updated.

2. The number of items in the listbox does not refresh with the number
of cells in the named range.

Can anyone help with this?

The listbox is currently an object in my worksheet but not a userform.
Is it essential for a listbox to be contained in a userform to work?

Apologies if these are daft newbie questions. I haven't used listboxes
in excel before!

Thanks heaps in advance.
Ciaran

 

[Bob Phillips]

Hi there,

I want to populate a listbox from a range with a varying number of
cells that are downloaded using a webquery. I've set this up
referencing the range by name in the listbox properties but am having a
few problems. These are:

1. The items do not refresh unless I change sheets - when I return to
the listbox sheet, the contents have updated.

What do you mean by Refresh? Do you have any Worksheet_Activate code?

2. The number of items in the listbox does not refresh with the number
of cells in the named range.

Assuming that you are using a dynamic range, you could worksheet change
event code to update it

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "Test"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.ListBox1.ListFillRange = "=Test"
End If

ws_exit:
Application.EnableEvents = True
End Sub