Update listbox every few seconds

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,064
Office Version
  1. 2016
Platform
  1. Windows
I am trying to update a listbox on a userform every few seconds. I have two examples on the attached sheet, The first example has the listbox on the userform without any multipages and nested multipages, this works fine. I am now trying to build one that will work when the right TAB of a multipage is selected, but can not work it out.

DOWNLOAD LINK Here

IN THE SECOND EXAMPLE

I have attached a quick example of what I am trying to do on the download. The listbox is on a USERFORM, the form has Multipages and NESTED Multipages. You will first need to navigate to Multipage1.pages (2) and then the nested Multipage2.PAGES (4). There you will see the listbox and a STOP button.

What the code should do.
When the user has navigated to the right tab, the code should AUTO RUN. The code updates the listbox every 5 seconds, with the data that goes into to sheet1, when the stop button is clicked, the listbox stops updating. The code SHOULD NOT update THE LISTBOX as the data goes into the sheet, It should ONLY update after a set amount of seconds e.g. 5 seconds.

VBA Code:
Private Sub MultiPage2_Layout(ByVal Index As Long)
If MultiPage2.Pages(4).Value = True Then
'data of worksheet
  With UserForm2.ListBox1
      .ColumnCount = 3
      .ColumnWidths = "200;100;100"
      .RowSource = "'" & Sheet1.Name & "'!$A$1:$c$" & Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
       
 'update listbox every 5 seconds
 UpdateListBoxes
    t = DateAdd("s", 5, Time) ' Change the 5 seconds to suit your needs.
    Application.OnTime t, "StartThem"
        
    End With
End If
End Sub

Error I keep getting
1594719714330.png


1594719748864.png
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,064
Office Version
  1. 2016
Platform
  1. Windows
I have fixed the error with
VBA Code:
If UserForm2.MultiPage2.Value = 1 Then

However I can not get the page to update on the timer
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,064
Office Version
  1. 2016
Platform
  1. Windows
I fixed it like this, so far it seems to work, if anybody knows a better way, please post

VBA Code:
Option Explicit
Private t

Private Sub MultiPage2_Layout(ByVal Index As Long)

If UserForm2.MultiPage2.Value = 1 Then
    Call StartThem1
End If
End Sub

Private Sub StartThem1()
    UpdateListBoxes
    t = DateAdd("s", 5, Time) ' Change the 5 to 60 to refresh every 60 seconds.
    Application.OnTime t, "StartThem1"
End Sub

Sub StopThem()
    Application.OnTime t, "StartThem1", , False
    Unload UserForm2
End Sub

Sub UpdateListBoxes()
   With UserForm2.ListBox1
      .ColumnCount = 3
      .ColumnWidths = "200;100;100"
      .RowSource = "'" & Sheet1.Name & "'!$A$1:$c$" & Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
  End With
End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
I'm not saying it's a better way (although I try to avoid OnTime statements as much as possible), but it seems to be related to this issue ...

 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,808
Members
430,323
Latest member
Regash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top