Form Listbox - Issue on Load/Click

[tt333]

Hi Folks

I've got this issue with Loading a form containing a Listbox. The issue is that along with the trigger (ie. on DoubleKlick on the list), items within the listbox are being selected (without intent), Is there a work-around?​

​

Basically, the mouse click turns on each item that would load on the listbox) (at the same spot)​

 

[ranman256]

dbl-clicking an item in the list WILL select the item.
but shouldnt do it at 'form load' unless it has code to do so.

 

[tt333]

dbl-clicking an item in the list WILL select the item.
but shouldnt do it at 'form load' unless it has code to do so.

thank you for your reply. I reallized that i failed to explain my issue accurately:

So, the trigger to load the form is either a button press on a worksheet, or a doubleclick event on a form. Either way, a (separate) form containing a listbox is loaded.
And, as it happens, the last click Action is unluckily registered on the form (as the forms load in the middle of the Excel Window, the likelihood hitting one of the listbox items is very high

 

[Jaafar Tribak]

This is a timing issue.

Workarounds:

Codes below assume UserForm2 is the form with the ListBox1 . change to suit.

Solution 1) You can add a small delay before showing the second form that has the listbox.

Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Wait 0.2
    UserForm2.Show
End Sub

Private Sub Wait(ByVal Secs As Single)
    Dim t As Single
    t = Timer: Do: DoEvents: Loop Until Timer - t >= Secs
End Sub

Solution 2) the above *Wait* workaround may not be consistent accross diff users particularly with modeless forms... so, I would suggest using the following better alternative:

Option Explicit

Private WithEvents Lbx As MSForms.ListBox

Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With UserForm2
        Set Lbx = .ListBox1
        .ListBox1.Locked = True
        .Show
    End With
End Sub

Private Sub Lbx_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Lbx.Locked And Button = 0 Then
        Lbx.Locked = False
    End If
End Sub

 

[tt333]

This is a timing issue.

Workarounds:

Codes below assume UserForm2 is the form with the ListBox1 . change to suit.

Solution 1) You can add a small delay before showing the second form that has the listbox.

Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Wait 0.2
    UserForm2.Show
End Sub

Private Sub Wait(ByVal Secs As Single)
    Dim t As Single
    t = Timer: Do: DoEvents: Loop Until Timer - t >= Secs
End Sub

Solution 2) the above *Wait* workaround may not be consistent accross diff users particularly with modeless forms... so, I would suggest using the following better alternative:

Option Explicit

Private WithEvents Lbx As MSForms.ListBox

Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With UserForm2
        Set Lbx = .ListBox1
        .ListBox1.Locked = True
        .Show
    End With
End Sub

Private Sub Lbx_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Lbx.Locked And Button = 0 Then
        Lbx.Locked = False
    End If
End Sub

That's cool. Needed a bit of experimenting, but i went now with Solution2 (but omitted the 'WithEvents...' Part; why is it needed ?)

 

[Jaafar Tribak]

That's cool. Needed a bit of experimenting, but i went now with Solution2 (but omitted the 'WithEvents...' Part; why is it needed ?)

I guess you are using a Modeless userform. ... If the second userform (where the Listbox is located) is Modal, you will see that it dosn't work unless you use WitheEvents.