Update listbox every few seconds

[Sharid]

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.

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

 

[Sharid]

I have fixed the error with

If UserForm2.MultiPage2.Value = 1 Then

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

 

[Sharid]

I fixed it like this, so far it seems to work, if anybody knows a better way, please post

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]

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 ...

Excel Userform Listbox Flickering.

I have a userform with a multipage, on each page I have a listbox, the data for the listbox come from each sheet. The listboxes flickers when the data is updates in them. If I place a wait time in my FOR LOOP OR Sheet Change Event then the flickering goes away. The only problem is that it slows...

www.mrexcel.com