CODEWITHSUNDEEP
vbaexcel-2010activex
User
User

Reputation: 363

VBA How to programaticly select an item in a listbox without triggering the on click event

I am using Excel 2010, Windows 10, with VBA. I have a function which runs upon clicking an item in an ActiveX ListBox control. The issue is that if you click the list box I ask if they are sure if they want to change the selection. If you click "yes" I continue, but if you say "no" I set the selection back to what it previously was.

So the issue is that when I programmatically set the list box selection back to the previous selection my function will re-run the code that runs if a user clicks an item in the list box ...which is what I don't want.

Does anyone have a better way to stop a list box selection and change it back to the old one without causing the on list box selection event to trigger?

Function prototype for on click of the list box

lsQuestions_Click()

Code for setting the list box selection

'Prototype: setListBoxSelection(query As String, listBoxName As String) As Boolean
'  Purpose: Set listbox selection based on text
Public Function setListBoxSelection(query As String, listBoxName As String) As Boolean
  Dim lsBox As MSForms.listBox
  Set lsBox = Workbooks(mainFile).Worksheets(entrySheet).OLEObjects(listBoxName).Object

  Dim I As Integer

  For I = 0 To lsBox.ListCount - 1
      If lsBox.List(I) = query Then
          lsBox.Selected(I) = True
          setListBoxSelection = True
          Exit Function
      End If
  Next I

  setListBoxSelection = False
End Function

Please note that I think the line of code below is what is triggering my click event which is what I don't want.

lsBox.Selected(I) = True

Upvotes: 0

Views: 3800

Answers (1)

Bill Hileman
Bill Hileman

Reputation: 2836

The way I do this with my VB6 projects is to define a module-scope variable

Private blnChangingInCode As Boolean

Then, when I need to utilize it, I set it to true, call the even/sub, set it back to false.

blnChangingInCode = True
btnLogin_Click()
blnChangingInCode = False

Inside the affected subs/events I start with

If blnChangingInCode Then
    Exit Sub ' or Exit Function
End if

This might not be elegant, but it works, and I don't need to do it very often.

Upvotes: 1

Related Questions