Activating Worksheet_BeforeDoubleClick With VBA

[dcunningham]

Hi Everyone,

So I have a workbook set up with a Worksheet_BeforeDoubleClick set up that launches some code if I double-click some cells in a given range. Now what I want to do is use VBA to double-click a certain cell based on some criteria (to activate what's inside the Worksheet_BeforeDoubleClick), and run a command.

A quick explanation as to why I want/need to do this. I have a dashboard made in Excel that summarizes some data about a selection of assets. In cells G11:G21 and H11:H21 I've got some cells with conditional formatting that turn the cells red if certain criteria are met. I currently can double-click these red cells to launch a series of commands that brings me to another worksheet in the same workbook and puts together a nice extended report for that asset for the type of criteria I'm looking for. What I want to do is have a way to produce these 'extended reports' for the assets with red conditional formatting in the G11:G21 and H11:H21 cells described above, and print them off as PDFs. Printing to PDF isn't an issue, I know how to do that, I just can't figure out how to simulate a way of double-clicking the cells or how to only "double-click" the cells that are red. I'll worry about the latter later, I need to figure out the former before I can even get started with that.

In my tinkering I tried the following, but it doesn't seem to activate my code properly.

Sub ClickCell()
    Worksheets("Dashboard").Activate
    ActiveSheet.Range("G14").Select
    Application.DoubleClick
End Sub

It seems to select a cell that's involved in part of what's inside the Private Sub Worksheet_BeforeDoubleClick but it doesn't actually execute the command. Here's the code, with the cell in question that seems to be getting activated. Basically what that line of code does is copy the asset name to a sheet called 'Calculations' that performs some work that gets copied into the 'Extended Report' sheet later.

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)


'Generates Extended Report
    
    If Not Application.Intersect(Target, Range("G10:G21")) Is Nothing Then
        If Target.Value = 0 Or Target.Value = "" Then
            MsgBox "Invalid selection. Please select data from another asset."
            Range("G6").Select
            Else
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationAutomatic
            Sheets("Extended Report").Visible = xlSheetVisible
            Sheets("Extended Report").Activate
            Sheets("Calculations").Visible = xlSheetVisible
            Sheets("Calculations").Activate
[COLOR=#ff0000]            Sheets("Calculations").Range("A55").Value = Target.Offset(, -5)[/COLOR]
            Call Recalc
            Call CopyToCalc
            Sheets("Extended Report").Activate
            Sheets("Calculations").Visible = xlSheetHidden
            Sheets("Dashboard").Visible = xlSheetHidden
[COLOR=#ff0000]            Sheets("Extended Report").Range("B7").Value = Target.Offset(, -5)[/COLOR]
            Application.Calculation = xlCalculationManual
            Application.ScreenUpdating = True
        End If
    End If
    If Not Application.Intersect(Target, Range("H10:H21")) Is Nothing Then
        If Target.Value = 0 Or Target.Value = "" Then
            MsgBox "Invalid selection. Please select data from another asset."
            Range("H6").Select
            Else
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationAutomatic
            Sheets("Extended Report").Visible = xlSheetVisible
            Sheets("Extended Report").Activate
            Sheets("Calculations").Visible = xlSheetVisible
            Sheets("Calculations").Activate
            Sheets("Calculations").Range("L55").Value = Target.Offset(, -6)
            Call RecalcAA
            Call CopyToCalcAA
            Sheets("Extended Report").Activate
            Sheets("Calculations").Visible = xlSheetHidden
            Sheets("Dashboard").Visible = xlSheetHidden
            Sheets("Extended Report").Range("B7").Value = Target.Offset(, -6)
            Application.Calculation = xlCalculationManual
            Application.ScreenUpdating = True
        End If
    End If
    
End Sub

Any help would be appreciated.

Regards,

Dan

 

[RoryA]

Ideally you would put the processing code into a routine in a normal module and call it from the event and anywhere else you need it. If need be you can also do it with application.run assuming you know the codename of the worksheet in question.