VBA UserForm刷新

Question

我有一个单词VBA UserForm，我使用它作为一个主板来跟踪来自一个SQL查询的结果，当它更新时。表单每10分钟启动一次，是否有一种自动刷新的方法？我试过这样做：

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

但这似乎什么也做不了。任何帮助都是非常感谢的！

Answer 1

除了宏之外，您不能使用Application.OnTime来执行其他任何东西--一个由标准的过程模块公开的公共、无参数的方法。

UserForm_Initialize不是宏-它是一个处理UserForm对象的Initialize事件的事件处理程序。当对象初始化时，每个实例只调用一次。您应该从不，永远不需要显式调用事件处理程序。如果你发现自己这样做了，警钟就会响起来，巨大的红旗应该升起，有些事情做得非常糟糕。

使用UserForm_Initialize中的所有内容，并将其移动到公共RefreshLeaderboard方法--您不需要处理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

现在您需要一个可以调度的宏，该宏可以访问表单实例。

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

您可能希望处理表单代码隐藏中的QueryClose事件，以处理被单击X按钮的用户关闭和/或销毁的事件。