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.

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.

--
Regards,
Tom Ogilvy

"Andy" wrote in message
news:jpvkc.325674$Ig.35171@pd7tw2no...
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.

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Andy" wrote in message
news:jpvkc.325674$Ig.35171@pd7tw2no...
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.

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

--
Regards,
Tom Ogilvy




"Andy" wrote in message
news:b4zkc.326937$Ig.121106@pd7tw2no...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Andy" wrote in message
news:jpvkc.325674$Ig.35171@pd7tw2no...
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.

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy




"Andy" wrote in message
news:b4zkc.326937$Ig.121106@pd7tw2no...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Andy" wrote in message
news:jpvkc.325674$Ig.35171@pd7tw2no...
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.