listbox not dsplaying all records in a named range

[Andy]

I have a listbox that gets it's items from an advanced query moved to a named
range (using offset to define the range). The query works and when I examine the
named range, all the rows are selected as they should be. Yet, when the listbox
is populated from the named range, not all the rows are listed. It seems to
happen when the first selection shows fewer items than the next. It then
'remembers' this smaller range and will not display the rest in the filter
output. Any ideas on how to have the listbox control the accurate number of rows
in the range consistently? Thank you so much for any replies.

 

[Tom Ogilvy]

ActiveX Listboxes that use dynamically calculated named ranges don't update
with each calculate. You will have to use some code to re-assign the
rowsource to stimulate the update. Perhaps put this in the calculate event.

 

[Andy]

Thanks for the reply Tom. I believe I can use something like ListBox4.RowSource
= "Category_Table" to help stimulate the update, but I'm not sure which change
event to try it in. I believe I've tried the events at the worksheet and form
level, but I've had no luck in implementing your solution. I feel dense so any
additional details you may supply are most welcome O.o

 

[Tom Ogilvy]

I used code like this with a modeless userform and it worked for me:

Private Sub Worksheet_Calculate()
Dim uform As msforms.UserForm
If UserForms.Count > 0 Then
On Error Resume Next
Set uform = UserForm1
On Error GoTo 0
If Not uform Is Nothing Then
' MsgBox "update"
uform.ListBox1.RowSource = "Category_Table"
End If
Else
' MsgBox "Not found"
End If
End Sub

 

[Andy]

Works like a charm for all of the listboxes. Thank you for helping me with this,
and helping others.