Excel用户窗体按钮单击快速单击时响应速度慢 [英] Excel UserForm button click slow response time when clicked fast

问题描述

我在Excel中有一个VBA用户窗体，具有非常简单的代码.它一次显示一个对象的集合(实际上是一个字典)，并带有第一个，上一个，下一个和最后一个"按钮.一切正常，但是如果我要连续单击下一个按钮来浏览各项，则必须缓慢单击(大约每秒一次).如果我单击速度更快，则单击将被忽略.例如，如果我在两秒钟内单击四次，它将仅注册"第一次和第三次单击并前进两次，而不是四次.

I have a VBA UserForm in Excel, with very simple code. It displays a collection (a dictionary, actually) of objects, one at a time, with buttons for "first, previous, next, and last". Everything works great, but if I were to continually click the next button to go through the items, I have to click it slowly (roughly once a second). If I click any faster, the click is ignored. For example, if I click four times over two seconds, it will only 'register' the first and third click and advance twice, instead of four times.

下面是下一个"按钮的示例代码(以及userform模块中的其他适用代码):

Below is example code for the 'next' button (and the other applicable pieces of code in the userform module):

Private dQIDs As Dictionary

Public Sub TransferQIDs(ByVal dIncomingQIDs As Dictionary)
    Set dQIDs = dIncomingQIDs
End Sub

Private Sub bNext_Click()
    Call LoadQID(CLng(lIndex.Caption) + 1)
End Sub

Private Sub LoadQID(lQID As Long)
    Dim QID As cQID
    Set QID = dQIDs(lQID)
    lIndex.Caption = lQID
    lItems.Caption = "Viewing new QID " & lQID & " of " & dQIDs.Count
    Me.tQID = QID.lQID
    Me.tTitle = QID.sTitle
    Me.tVID = QID.sVendorID
    Me.bOS = QID.bOSPatch
    Me.bApp = Not QID.bOSPatch
    Me.bPrev.Enabled = Not (lQID = 1)
    Me.bFirst.Enabled = Not (lQID = 1)
    Me.bNext.Enabled = Not (lQID = dQIDs.Count)
    Me.bLast.Enabled = Not (lQID = dQIDs.Count)
End Sub

有什么想法吗?

推荐答案

我个人只是在加载内容时禁用按钮.

Personally I would just disable to button while content is loaded.

Private Sub bNext_Click()
  Dim b1 As Button
  Set b1 = ActiveSheet.Buttons("LoadQID")
  REM or Me.LoadQID

  b1.Font.ColorIndex = 15
  b1.Enabled = False
  Application.Cursor = xlWait

  Call LoadQID(CLng(lIndex.Caption) + 1)

  b1.Enabled = True
  b1.Font.ColorIndex = 1
  Application.Cursor = xlDefault
End Sub

之所以会发生这种情况，是因为在Excel中访问单个对象需要花费大量时间.这样，如果您可以单击它将被注册.

Reason why this happens is that accessing a single object takes quite a bit of time in Excel. This way if you can click it will be registered.

或者，您也可以使用以下方式切换用户界面更新:

Alternatively you can toggle UI update with:

Application.ScreenUpdating = False
Application.ScreenUpdating = True

这篇关于Excel用户窗体按钮单击快速单击时响应速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！