Use hover over in a macro

[Oliver Dewar]

Hi All.

I have a listbox (from the forms menu) and I'd like to make it run a macro on double click (at the moment it only fires on change or click).

Here's what I'm thinking:

1. Link listbox to a cell so the number value can be turned into the text string from the listbox when the macro is run.

2. Put a macro in the beforedoubleclick event of the worksheet.

3. Set the macro to run only if mouse is hovering over the listbox.

My question therefore is what code to put into the beforedoubleclick event that tests for hover over the listbox and only runs if the mouse is hovering over the listbox?

Thanks all.

 

[ZVI]

Hi Oliver,
This won’t work because worksheet’s BeforeDoubleClick event happens only over cells.
Use ListBox from Visual Basic panel instead. It has DblClick event:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Call MyMacro
End Sub

Put ListBox1 from Visual Basic panel on a sheet.
Switch to design mode via Visual Basic panel button.
Do right click on ListBox1, choose Properties and set "ListFillRange" and "Linked cell" similar to Form’s Lisbox.
Switch off design mode
Do right click on Sheet tab, chose "Source code" and put the code into sheet’s VBA module.

Regards,

 

[Oliver Dewar]

Hi.

Thanks for your reply. I can't seem to work out how to put a listbox from the vba panel onto a sheet... how's it done?

 

[ZVI]

...how to put a listbox from the vba panel onto a sheet... how's it done?

Excel 2003:
1. Open the "Control Toolbox" toolbar by going to the "View" menu, pointing to "Toolbars" and selecting "Control Toolbox." The toolbox appears as a floating toolbar.
2. Click the List Box control in the "Control Toolbox." If you are not sure which control this is, hover your mouse over each button in the "Control Toolbox" and click on the one that says "List box"
3. Click onto the worksheet where you want to place the List Box and drag the box to "draw" the size you want it to be.
4. Right-click the List Box and select "View Code" to add a macro or select "Properties" to change the properties of control.
5. Click the "Exit Design Mode" button in the "Control Toolbox" to leave design mode and enable the List Box.

Excel 2007/2010:
The details are in the links
1. Add a list box or combo box to a worksheet see chapter "Add a list box (ActiveX control)".
2. Listbox