Execute Code when cell changes

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

Execute Code when cell changes

Post by JoeExcelHelp »

Hi Everyone,

I'm trying to call the following code when cell E3 changes within a (3) character sheet and the event should only apply to sheet names with (3) characters
Can't seem to find the problem

Thank You

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Worksheet
For Each wsh In Worksheets
    If Len(wsh.Name) = 3 Then

        If Not Intersect(Target, Target.Worksheet.Range("E3")) Is Nothing Then
        Call Module11.Distribute_F_FO
        End If
    End If
Next wsh
End Sub

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

Re: Execute Code when cell changes

Post by HansV »

You shouldn't use the Worksheet_Change event for this. Instead, create a Workbook_SheetChange event procedure in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Len(Sh.Name) = 3 Then
        If Not Intersect(Target, Sh.Range("E3")) Is Nothing Then
            Call Module11.Distribute_F_FO
        End If
    End If
End Sub
Best wishes,
Hans

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

Re: Execute Code when cell changes

Post by JoeExcelHelp »

Thank You Hans

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

Re: Execute Code when cell changes

Post by JoeExcelHelp »

Sorry to resurface this Hans,

I no longer want to call the code and would prefer the user to activate the code each time cell E3 is changed within a WS
I tried the following but its not working for me

Thank You

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsh As Worksheet
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each wsh In Worksheets
    If Not Intersect(Target, Target.Worksheet.Range("E3")) Is Nothing Then
        If Len(wsh.Name) = 3 Then
                With wsh.Range("G33:CD33")
                .FormulaR1C1 = _
                "=IF(AND(TODAY()<=R11C,TODAY()>=R12C),""Active Forecast Base Models  ""&""F ""&""= ""&R16C1&""  ""&""FO ""&""= ""&R51C5,"""")"
                .Value = .Value
                End With
                With wsh.Range("G2:CD2")
                .FormulaR1C1 = _
                "=IF(OR(R12C>EOMONTH(TODAY(),R3C5),R12C<EOMONTH(TODAY(),R3C5-1)),"""",""Target >"")"
                .Value = .Value
                End With
                With wsh.Range("G72:CD72")
                .FormulaR1C1 = _
                "=IF(OR(R12C>EOMONTH(TODAY(),R3C5),R12C<EOMONTH(TODAY(),R3C5-1)),"""",""Target >"")"
                .Value = .Value
                End With
    End If
        End If
    Next wsh
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

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

Re: Execute Code when cell changes

Post by HansV »

Do you want this to happen in one sheet only? If so, you need the Worksheet_Change event procedure in the worksheet module, but you should not loop through the worksheets.
If you want this to happen in all worksheets whose name has length 3, you need to use the Workbook_SheetChange event procedure as in my previous reply. Again, you should not loop through the worksheet. Instead, use the variable Sh provided by the event procedure; it represents the worksheet in which a change occurred.
Best wishes,
Hans

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

Re: Execute Code when cell changes

Post by HansV »

Perhaps this, in the ThisWorkbook module?

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Len(Sh.Name) = 3 Then
        If Not Intersect(Target, Sh.Range("E3")) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Application.Calculation = xlCalculationManual
            With Sh.Range("G33:CD33")
                .FormulaR1C1 = "=IF(AND(TODAY()<=R11C,TODAY()>=R12C)," & _
                    """Active Forecast Base Models  ""&""F ""&""= " & _
                    """&R16C1&""  ""&""FO ""&""= ""&R51C5,"""")"
                .Value = .Value
            End With
            With Sh.Range("G2:CD2")
                .FormulaR1C1 = "=IF(OR(R12C>EOMONTH(TODAY(),R3C5)," & _
                    "R12C<EOMONTH(TODAY(),R3C5-1)),"""",""Target >"")"
                .Value = .Value
            End With
            With Sh.Range("G72:CD72")
                .FormulaR1C1 = "=IF(OR(R12C>EOMONTH(TODAY(),R3C5)," & _
                    "R12C<EOMONTH(TODAY(),R3C5-1)),"""",""Target >"")"
                .Value = .Value
            End With
            Application.Calculation = xlCalculationAutomatic
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
End Sub
Best wishes,
Hans

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

Re: Execute Code when cell changes

Post by JoeExcelHelp »

Perfect Hans Thank You