G'day there again One & All,

I'm currently almost finished a smallish project for my office,
and the last little bit has me stumped.

I have used the Controls (not Forms) toolbar to get a ListBox on a
worksheet. It's the only OLE control on the form. It's not intended to
select anything, but to display certain information taken from a list of
3 columns on the same sheet. This data source is a dynamic range named
listDates.

The workbook loads with an empty ListBox. Actually, I it may be
simpler if I explain a bit...

I need to interpret data from several text files that are dumped
from a sampling device at irregular intervals. The data is to be
collated into monthly figures for reporting. The data is a list of
various fields including the date, category, specific reading, etc. It's
possible that the dumped files could include data from a week or so
prior to the end of the month, well into the next or even (in slow
times) over a month. Or there might be 3 or more files in a single
month.

The front worksheet does nothing but calculate and display data
from the 2nd sheet - averages, totals, sums, etc. The second sheet is a
list built from the data. I import the text files into a new worksheet
for each file, copy each sheet full of data to the next vacant row on
the 2nd sheet and then sort that working list by date.

On the front page I have a 2 listboxes wherein I can select a
start & end date (usually 1st of the month and the last day of the
month). Once this is done, my code then removes extraneous records from
the 2nd sheet and on the 1st sheet a unique list of dates is built with
one of John Walkenbach's formula arrays. This list of unique dates is
then the first column for my list box. The 2nd is the number of samples
taken on each of those dates, and the 3rd is the number of those samples
whose readings fall within the target ranges.

As you can see from that, it's not possible to populate the
listbox on opening. It's necessary to load the data files first. I've
actually gotten it loading and unloading as the data is entered or
cleared, however it doesn't display. On loading the data, the figures on
the rest of the first sheet display, but the listbox remains blank until
I scroll it off screen and return to it whereupon it shows all the
necessary entries. Likewise, on clearing the data, the rest of the first
sheet clears, but the listbox retains its data until I scroll it off and
return, and then it's blank.

How can I get my listbox to display & clear as the data is put
into or removed from the named dynamic range that's the listbox's
source?

Hoping all of that makes sense,
Ken McLennan
Qld, Australia

untested but to force a repaint you could try
to toggle the control's visibility?


with MyListbox ' or activesheet.olebojects(1)
.visible=false
.visible=true
end with


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Ken McLennan wrote:

G'day there again One & All,

I'm currently almost finished a smallish project for my office,
and the last little bit has me stumped.

G'day there keepITcool,

untested but to force a repaint you could try
to toggle the control's visibility?


with MyListbox ' or activesheet.olebojects(1)
.visible=false
.visible=true
end with

It may have been untested, but it worked like a charm. Thank you
very much for that. I may have thought of it myself eventually, but I
doubt it. And definitely not this side of Christmas, THAT's for sure!!

Naturally enough, having got that bit working it showed up an
error or two that I had elsewhere, but those are now in hand anyway.


Thanks once more for your insight. Well done!!

See ya
Ken McLennan
Qld, Australia