VBA Is it possible to activate double click macro?

[Kra]

Hi all!
I have a macro button in user form that highlights column and row of selected cell. It is really useful, but it is kind of annoying to run it every time I need to use it. I know that I could set up a double click macro, but I don't want it to be active all the time (so I can double click sometimes without running macro).

So my 1st question - is it possible to create a button in user form that activates double click event, so then I can highlight cells using double click and then switch it off using the same or other button?

And if it is possible, then is there a way to adjust this macro below - right now it changes color of selection, but sometimes I have to use in worksheets with already colored cells and this macro overwrites current color. Can it be adjusted to only select column and row like this, instead of changing colors?

Private Sub CommandButton7_Click()
    Static xRow
    Static xColumn
    If xColumn <> "" Then
        With Columns(xColumn).Interior
            .ColorIndex = xlNone
        End With
        With Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If
    pRow = selection.Row
    pColumn = selection.Column
    xRow = pRow
    xColumn = pColumn
    With Columns(pColumn).Interior
        .ColorIndex = 34
        .Pattern = xlSolid
    End With
    With Rows(pRow).Interior
        .ColorIndex = 34
        .Pattern = xlSolid
    End With

 

[offthelip]

Try this:
Put this in the worksheet code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If ClickHi Then
    PRow = Target.Row
    PColumn = Target.Column
   
    With Columns(PColumn).Interior
        .ColorIndex = 34
        .Pattern = xlSolid
    End With
    With Rows(PRow).Interior
        .ColorIndex = 34
        .Pattern = xlSolid
    End With
 End If
End Sub

and then put this in a standard module:

Public ClickHi As Boolean
Sub Turnclickon()
ClickHi = True
End Sub

Sub Turnclickoff()
ClickHi = False
End Sub

Then you can create two buttons or you can do it with one with this code:

Sub Toggleclick()
ClickHi = Not (ClickHi)
End Sub

 

[Micron]

is it possible to create a button in user form that activates double click event,

A command button to allow/disallow code to run would require you to set a public variable that procedures would have to check the value of. Much easier to use a checkbox or option button and in the dbl click events, exit right away if the button value is True. That way you'd also have a visual of the state you're in. If "activate" means call/run code then yes a button can call a dbl click event but success depends on scope/visibility between each code procedure.

Try
If xColumn <> "" Then
Union(Columns(xColumn), Rows(xRow)).Select
End If

 

[Kra]

Try this:
Put this in the worksheet code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If ClickHi Then
    PRow = Target.Row
    PColumn = Target.Column
  
    With Columns(PColumn).Interior
        .ColorIndex = 34
        .Pattern = xlSolid
    End With
    With Rows(PRow).Interior
        .ColorIndex = 34
        .Pattern = xlSolid
    End With
 End If
End Sub

and then put this in a standard module:

Public ClickHi As Boolean
Sub Turnclickon()
ClickHi = True
End Sub

Sub Turnclickoff()
ClickHi = False
End Sub

Then you can create two buttons or you can do it with one with this code:

Sub Toggleclick()
ClickHi = Not (ClickHi)
End Sub

Hi, thanks! Sadly double click event is not working for me. When I double click I can see that excel thinks for a moment and then nothing happens. I give up with this project, will use simple hotkey. Thank you for help!