Activate Macro

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Activate Macro

Post by JoeExcelHelp »

Hi Everyone,

Trying to activate this macro when E3 is double-clicked and only on sheet names with 3 characters

Hoping you guys can assist

Thank You

Code: Select all

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$E$3" Then
    Dim wsh As Worksheet
    Dim sFile As String
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    sFile = "Attrition!"
    For Each wsh In Worksheets
        If Len(wsh.Name) = 3 Then
            

    With wsh.Range("G90:CD91")
    .FormulaR1C1 = "=Round((SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)+ " _
    & "IF(R12C>EOMONTH(TODAY(),R3C5),SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Post Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)))*R157C,0)"
    .Value = .Value
    End With
    With wsh.Range("G131:CD132")
    .FormulaR1C1 = "=Round((SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)+ " _
    & "IF(R12C>EOMONTH(TODAY(),R3C5),SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Post Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)))*R157C,0)"
    .Value = .Value
    End With
    
    End If
    Next wsh
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
Last edited by HansV on 05 Aug 2021, 19:17, edited 1 time in total.
Reason: to replace quote tags with code tags

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: Activate Macro

Post by JoeExcelHelp »

Also,

Can you have multiple types of double click codes the same as
"Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)ou have multiple"
in the 'this workbook'?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Activate Macro

Post by HansV »

I'm not sure I understand what you want, but I think you shouldn't loop through the worksheets.

Code: Select all

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Len(Sh.Name) = 3 Then
        If Target.Address = "$E$3" Then
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual
            With Sh.Range("G90:CD91")
                .FormulaR1C1 = "=Round((SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)+ " _
                    & "IF(R12C>EOMONTH(TODAY(),R3C5),SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Post Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)))*R157C,0)"
                .Value = .Value
            End With
            With Sh.Range("G131:CD132")
                .FormulaR1C1 = "=Round((SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)+ " _
                    & "IF(R12C>EOMONTH(TODAY(),R3C5),SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Post Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)))*R157C,0)"
                .Value = .Value
            End With
            Application.Calculation = xlCalculationAutomatic
            Application.ScreenUpdating = True
        End If
    End If
End Sub
There can only be one Workbook_SheetBeforeDoubleClick event procedure. If you need multiple double-click actions, you need to add them to the above procedure.
Best wishes,
Hans

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: Activate Macro

Post by JoeExcelHelp »

Thank You Hans