CODEWITHSUNDEEP
vbauserform
Cody Cook
Cody Cook

Reputation: 111

VBA UserForm refresh

I have a word VBA UserForm that I am using as a Leaderboard to track results from a SQL query as it updates. Is there a way to auto refresh the form once it is launched every 10 minutes? I have tried this:

Private Sub UserForm_Initialize()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim AgingSQL As String

cnn.ConnectionString = "DATABASE INFO"
cnn.Open

AgingSQL = "SQL QUERY"
rst.Open AgingSQL, cnn
rst.MoveFirst
With UserForm1.AgingLeaderboard -- List Box I am using to display info
     .Clear
     Do
        .AddItem
        .List(i, 0) = rst![StatusBy]
        .List(i, 1) = rst![Count]
        i = i + 1
        rst.MoveNext
    Loop Until rst.EOF
End With
rst.Close
**Call Refresh**
End Sub

Sub Refresh()
   Application.OnTime Now + TimeValue("00:00:10"), "UserForm_Initialize"
End Sub

But that doesn't seem to do anything. Any help is appreciated!

Upvotes: 2

Views: 8959

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

You can't use Application.OnTime to execute anything other than a macro - a public, parameterless method exposed by a standard, procedural module.

UserForm_Initialize isn't a macro - it's an event handler, that handles the Initialize event of a UserForm object.. which is only ever called once per instance, when the object, well, initializes. You should never, ever need to explicitly call an event handler. If you find yourself doing that, alarm bells should go off, big huge red flags should fly, something is being done terribly wrong.

Take everything you have in UserForm_Initialize and move it to a public RefreshLeaderboard method - you won't need to handle Initialize:

Option Explicit

Public Sub RefreshLeaderboard()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim AgingSQL As String

    cnn.ConnectionString = "DATABASE INFO"
    cnn.Open

    AgingSQL = "SQL QUERY"
    rst.Open AgingSQL, cnn
    rst.MoveFirst
    With AgingLeaderboard '-- note: was With UserForm1.AgingLeaderBoard

         .Clear
         Do
            .AddItem
            .List(i, 0) = rst![StatusBy]
            .List(i, 1) = rst![Count]
            i = i + 1
            rst.MoveNext
        Loop Until rst.EOF
    End With
    rst.Close    
End Sub

Now you need a macro that you can schedule, that can access the form instance.

Option Explicit
Private leaderboardForm As MyAwesomeForm ' whatever the UserForm class name is

Public Sub ShowLeaderboard()
    Set leaderboardForm = New MyAwesomeForm
    ScheduleNextRefresh
    leaderboardForm.Show 'vbModal?
    'Set leaderBoardForm = Nothing '' only if the form was modal and not X'd-out
End Sub

Public Sub ScheduleNextRefresh()
    If Not leaderboardForm Is Nothing Then
        leaderboardForm.RefreshLeaderboard
        Application.OnTime Now + TimeValue("00:00:10"), "ScheduleNextRefresh"
    End If
End Sub

You'll probably want to handle the QueryClose event in the form's code-behind, to handle it being closed and/or destroyed by a user that clicks the [X] button.

Upvotes: 5

Related Questions