Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    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:
    Code:
    Sub Populate_Listbox()
    Dim FileList
    Dim cMyListBox As MSForms.ListBox
        
        Set cMyListBox = Sheet1.ListBox1
        FileList = Dir(Get_Path & "*.mp3")
        
        With cMyListBox
            .Clear
            While FileList <> ""
                .AddItem FileList
                FileList = Dir()
            Wend
            .Selected(0) = True   
            
        End With
        
        Set cMyListBox = Nothing
        
    End Sub
    Last edited by JerryDal; 07-20-13 at 00:21. Reason: spelling

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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.

    Code:
    Sheet1.ListBox1.Visible = False
    Sheet1.ListBox1.Visible = True
    Solved
    Last edited by JerryDal; 07-22-13 at 01:13. Reason: spelling & wording

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •