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.