Search code examples
performancevbabuttonclickuserform

Excel UserForm button click slow response time when clicked fast

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.

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

Any ideas?

Solution

  • 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

    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