How to run macro on double click of a listbox

[Oliver Dewar]

Hi All.

So I had a beautiful worksheet with 3 activex listboxes doing wonderful things on double click. Then I ran into all the horrible resizing, distorting issues associated with activex controls on different computers / screens.

I've now re-done the sheet using form control listboxes instead, and so need to assign macros that run on a double click of the listbox (as per an activex version).

Can someone help me with that code? (How to initiate a macro on double click).

It will also need to use the value of the listbox (the current selection), will .value work for a forms control as per an activex?

Now... I'd also like to give something simple back to the community here. It's fairly well known that you can't change the font size of form controls, and the font is very small! My simple workaround for this was to zoom this particular sheets in and then build around the listboxes with buttons etc at smaller font sizes and dimensions than usual... maker the listbox font bigger by comparison.

Thanks in advance for any help!

 

[Andrew Poulsom]

A ListBox from the Forms Toolbar has only on event, which fires when you click or doubleclick an item in the list that's not currently selected. So you're out of luck I'm afraid.

 

[Oliver Dewar]

Thanks Andrew.

One idea on my part:

Could I disable the macro (or not assign one) to the listbox then put a double click event for the listbox in the worksheet module?

(If so what kind of code would I need?)

 

[Andrew Poulsom]

Sorry, no.

 

[mikerickson]

Here is something that will emulate a double click event with a control from the Forms menu.

Sub FormsMenuDoubleClick()
    Static LastClick As Date
    
    If Now - LastClick < TimeValue("00:00:01") Then
        Range("A1") = "double clicked"
    Else
        Range("A1") = "single-clicked"
        LastClick = Now()
    End If
End Sub

Application.Timer has a 1 second "graininess" on my Mac. If Windows Timer has better resolution, using that instead of Now would be a smoother interface.