Kick off macro when a cell is double clicked?

[scott511]

Does anyone have any tips for a macro that kicks off when a cell is double clicked?

What if it's multiple cells that get double clicked?

Thanks so much for help in advance! I really appreciate it!

[Bob Phillips]

Use the BeforeDoubleClick event procedure.

Test if Target.Cells.Count > 1.

[scott511]

Thanks for replying! I tried putting in at the beginning of my macro but it's not working. I highlighted below in red where I would normally double click. I just need the macro to automatically kick off any time I double click a cell and not just E8. Thanks again!

 Private Sub Worksheet_Change(ByVal Target As Range)
 
 If Target.Cells.Count > 1 Then
 
    Sheets("Test").Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = False
    Sheets("Data").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet4").Select
    Range("E8").Select
    Application.DisplayAlerts = False
    Sheets(1).Name = "Sheet2"
    Sheets("Sheet2").Delete
    Application.DisplayAlerts = True
    Selection.ShowDetail = True
    ActiveSheet.Name = "Data"
    Sheets.Add.Name = "Test"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data!R1C1:R50000C11", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Test!R3C1", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion14
    Sheets("Test").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Payer")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("new charge"), "Sum of new charge", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of new charge")
        .Caption = "Count of new charge"
        .Function = xlCount
    End With
    Range("B7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Payer").AutoSort _
        xlDescending, "Count of new charge", ActiveSheet.PivotTables("PivotTable1"). _
        PivotColumnAxis.PivotLines(1), 1
    Rows("4:4").Select
    ActiveWindow.FreezePanes = True
    Range("C2").Select
    
    
End Sub

[Kenneth Hobs]

IF you doubleclick, it is for one cell only or the cells are merged that you are editing.

e.g.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)  
 MsgBox "BeforeDoubleClick: " & Target.Address & vbLf & _
    "Selection Cell Count: " & Selection.Cells.Count
  
  If Target.Address(False, False) = "A5" Then
    MsgBox "A5 was doubleclicked."
    Cancel = True
  End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  MsgBox "SelectionChange: " & Target.Address & vbLf & _
    "Selection Cell Count: " & Selection.Cells.Count
End Sub

[scott511]

Would I use this at the beginning Kenneth?

Thanks!

[Kenneth Hobs]

Yes, right click your sheet tab, view code, and paste my routines in a test worksheet's object. You can then try some tests for single clicks and doubleclicks. You have to be really fast at doubleclicking to get that event to fire rather than the selection event.

Target.Cells.Count is the same as Selection.Cells.Count FWIW.

Notice how I used the address property to do a bit more in the beforedoubleclick event if cell A5 was doubleclicked. Normally, one uses Intersect() to intersect the Target and a set of cells to take some action. Select Case of an intersected range can be useful as well. It all depends on how complex you need to get to meet your needs. A search of Intersect on the forum will show some code examples.