Refresh listboxes data on userform without reloading entire user form.

jeff_creator · Apr 12th 2016

So i have listboxes on a userform that display status of items at any given time based on data in an excel sheet(s) behind the userform. I can get all of the listboxes to populate/show status but to update these listboxes with current data every 60 seconds i have to exit the entire userform and reload the form. (which is no issue since that can be automated.)

But is there a way to refresh these listboxes with the the new data on the excel sheet behind without closing the entire userform and reopening it? (really just trying to make it look less tacky by not showing all of those ugly excel cells behind it every 60 seconds.)

skywriter · Apr 12th 2016

Re: Refresh listboxes data on userform without reloading entire user form.

How is the sheet updated, are you adding data manually or is this some kind of automatic update, something being pulled from the internet?

What's the code that's being used to initially populate the list boxes?

If you can post the code, use code tags.

Paste the code into your reply, highlight the code then click the # on the toolbar and after you save your post the code will look like this.

Code

Sub Test
End Sub

Instead of this, which no one likes.
Sub Test
End Sub

jeff_creator · Apr 12th 2016

Re: Refresh listboxes data on userform without reloading entire user form.

it extracts information from a external website than re-uploads some data to a local SP site, so yes it automatically updates. below is my listbox code for population:

VB:

Code

CurrentColumn = CurrentColumn + 6 'go to next listbox's data in callboard data
        VariablesRow = VariablesRow + 1 'go to next row in variables for next listbox's settings
    Next Itm




    'clear listboxes
    ListBox11.Clear
    ListBox12.Clear
    ListBox13.Clear
    ListBox21.Clear
    ListBox22.Clear
    ListBox23.Clear
        
        'Jeff Fix this please
    'Add values to listboxes
    Sheet1.Activate
    CallboardRows = WorksheetFunction.CountA(Range("A:A"))
    If CallboardRows < 3 Then
        CallboardRows = 3
    End If
    UserForm2Test.ListBox11.RowSource = Sheet1.Range("A3:F" & CallboardRows).Address
    CallboardRows = WorksheetFunction.CountA(Range("G:G"))
    If CallboardRows < 3 Then
        CallboardRows = 3
    End If
    UserForm2Test.ListBox12.RowSource = Sheet1.Range("G3:L" & CallboardRows).Address
    CallboardRows = WorksheetFunction.CountA(Range("M:M"))
    If CallboardRows < 3 Then
        CallboardRows = 3
    End If
    UserForm2Test.ListBox13.RowSource = Sheet1.Range("M3:R" & CallboardRows).Address
    CallboardRows = WorksheetFunction.CountA(Range("S:S"))
    If CallboardRows < 3 Then
        CallboardRows = 3
    End If
    UserForm2Test.ListBox21.RowSource = Sheet1.Range("S3:X" & CallboardRows).Address
    CallboardRows = WorksheetFunction.CountA(Range("Y:Y"))
    If CallboardRows < 3 Then
        CallboardRows = 3
    End If
    UserForm2Test.ListBox22.RowSource = Sheet1.Range("Y3:AD" & CallboardRows).Address
    CallboardRows = WorksheetFunction.CountA(Range("AE:AE"))
    If CallboardRows < 3 Then
        CallboardRows = 3
    End If
    UserForm2Test.ListBox23.RowSource = Sheet1.Range("AE3:AJ" & CallboardRows).Address

Display More

skywriter · Apr 12th 2016

Re: Refresh listboxes data on userform without reloading entire user form.

I don't know if this will work for you but I stumbled upon something while trying to figure out how to help you.

I'll admit I have no experience with your particular request, but if there's one thing I'm always willing to do, it's try.

See the attached file.

I created a worksheet "Sheet1"

I used the code below to initialize the two list boxes.

I set the ShowModal property of the form to False so that I can do other things while the form is open.

I noticed that if I add things to the A or B columns the list boxes immediately reflect those changes.

Click the load form button and add items to the columns and you should see the list boxes change immediately.

So maybe if the code you use to populate all your list boxes is in the initialize event and your form ShowModal property is set to False your list boxes will reflect the current data when it changes.

Form Code:

Code

Private Sub UserForm_Initialize()
With Worksheets("Sheet1").Cells(1).CurrentRegion
    Me.ListBox1.RowSource = .Columns(1).Address
    Me.ListBox2.RowSource = .Columns(2).Address
End With
End Sub

Code in module 1

Code

Sub FormShow()
UserForm1.Show
End Sub

skywriter · Apr 12th 2016

Re: Refresh listboxes data on userform without reloading entire user form.

I see what it's doing now.

If 1 column is longer than the other then the current region address is encompassing some blank cells and when you fill in those blank cells it's showing those values in the list box.

I'll work on it further and see if I can figure out how to do this.

jeff_creator · Apr 12th 2016

Re: Refresh listboxes data on userform without reloading entire user form.

hmmm still couldn't get it to work with Showmodal set to false. In fact it wouldn't show my userform at all! The display just stuck on the initial menu screen (another userform used to access the 2nd userform which i am working with.) Maybe i will try to clone the userform to make a second UF and have that start running halfway through the 60 seconds so that even when it does exit off one form there's always the second form open in the background.

skywriter · Apr 12th 2016

Re: Refresh listboxes data on userform without reloading entire user form.

Here's what I came up with.

The example code will update the list box every 5 seconds, this is just so you can add items and see how it works.

Thanks to snb for his wonderful website where I learned about application.ontime.

This code in a regular module.

Code

Option Explicit
Private t
Sub ShowForm()
UserForm1.Show
End Sub
Sub StartThem()
    UpdateListBoxes
    t = DateAdd("s", 5, Time) ' Change the 5 to 60 to refresh every 60 seconds.
    Application.OnTime t, "StartThem"
End Sub
Sub StopThem()
    Application.OnTime t, "StartThem", , False
    Unload UserForm1
End Sub
Sub UpdateListBoxes()
    With Worksheets("Sheet1").Cells(1).CurrentRegion
        UserForm1.ListBox1.RowSource = .Address
    End With
End Sub

Display More

This code in the user form module.

Code

Option Explicit


Private Sub CommandButton1_Click()
    StopThem
End Sub


Private Sub UserForm_Initialize()
StartThem
End Sub

Display More

Refresh listboxes data on userform without reloading entire user form.

Participate now!

Share