change a macro button from single click to double click

BazN · Mar 20th 2020

I have an excel workbook with two macros that work fine (thanks largely to this site...wish I had found OzGrid weeks earlier !), but now I have another query.

I have two macros, each with its "start" on-screen button. I find that with my sometimes unsteady fingers I will unintentionally click twice, resulting in two rows of data being instead of the required one. I have seen several posts on this and other sites describing the setting up of double click, but they all seem to cover doing some specific task like switching colours or shifting data and so on. I don't want the button to do anything at all except to Run the macro when double clicked.

Any simple solutions for my simple brain ?

**royUK** · Mar 20th 2020

What kind of button have you used? There's an ActiveX or Forms Button.

1. ActiveX Button- first click into Design Mode using the button in the Developers Tab, it has a Set Square & Ruler. Then double click on the button to open the VB Editor. You should see something like this

Code

Private Sub CommandButton1_Click()
'your code is here
End Sub

Next in the right hand drop down it will currently say Click, scroll down to select DblClick. Cut and paste your code into that event. Switch off Design Mode to allow your Macro to Run.

2. Forms Control. Unfortunately these buttons don't have DblClick

**Jack in the UK** · Mar 20th 2020

If you use this code to a Command Button, things should work

Code

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


    msgbox "Jack your double clicked Command Button 1"


End Sub

Now if you want to derail single click of Command Button, just don’t have any code assigned to single click and nothing will happen or for better coding try

Code

Private Sub CommandButton1_Click()


    ‘// jiuk – do nothing


Exit sub


End Sub

Jiuk

trunten · Mar 21st 2020

Hmmm, only way I could thin of doing anything remotely double-clicky with a form control button is this horrible kludge:

Code

'Only run code if macro is executed twice within 'clickSpeed' seconds
Sub doubleClick()
    Const clickSpeed As Double = 0.4 'Change this to alter double-click sensitivity (eg 0.5 = 1/2 a second)
    Static lastClick As Double

    If Timer - lastClick > clickSpeed Or lastClick = 0 Then
        lastClick = Timer
    Else
        lastClick = Timer
        MsgBox "Double clicked", vbInformation
    End If
End Sub

Display More

change a macro button from single click to double click

Participate now!

Share