Unanswered: Refresh a Worksheet Listbox from a Module?
I have a worksheet listbox that gets populated from a separate
module, with a list of files in a folder. With the list populated,
when I change the contents of the folder (delete or add files),
then repopulate the listbox, I see no change.
For example if I delete a file, it still appears after I repopulate the
llistbox, and it is the only one that can not be selected.
In the program, a Sheet1 media player control has been created
to play from the list, one at a time; the status of the WMP control is
checked from Sheet1 module to determine if the player is stopped,
and when it is, that control is deleted and a new control is created to
play the next in the list.
Is there a method to refresh or repaint the listbox from code in a module?
I'm using Excel 2003. Module1 code:
Dim cMyListBox As MSForms.ListBox
Set cMyListBox = Sheet1.ListBox1
FileList = Dir(Get_Path & "*.mp3")
While FileList <> ""
FileList = Dir()
.Selected(0) = True
Set cMyListBox = Nothing
Last edited by JerryDal; 07-19-13 at 23:21.
Below is code for the solution that worked, to refresh a worksheet listbox from a separate module,
with Excel 2003. The list is from a folder of MP3 files. I have options to shuffle or sort the list.
Also the folder contents could be changed while the workbook is open and I noticed after the sorting or folder content
changes, the listbox was not updated, but would update itself as the program caused the listbox to highlight each line
with the advance to the next song.
This was a project that happened along the way to doing something else, so this was a matter of curiosity
to find a solution to refresh the listbox. The challenge was to play MP3 files with a Windows Media Play control,
and display a listbox highlighted at the currently playing song, and play one after another by checking the
WMP control status for the text "Stopped".
I tried adding the code in the sub that re-populated the list box, but that did
not refresh the listbox. What worked was to place the code in the sub that creates
a WMP control, just after assigning a value to the control's URL, the file path and file
name of the MP3 file.