Update listbox every few seconds

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
865
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
865
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
865
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
1,105
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 ...

 

Watch MrExcel Video

Forum statistics

Threads
1,114,443
Messages
5,547,948
Members
410,820
Latest member
Prepost
Top