VBA Application.ScreenUpdating=False Not Working

[dwhitey1124]

Hi, I can't figure out why the code below is not disabling screen updating. Any help would be much appreciated.

Sub Populate_Categories()
If Application.ScreenUpdating = True Then Application.ScreenUpdating = False
If Application.EnableEvents = True Then Application.EnableEvents = False
    If sheets(sheets("control").Range("p45").Value).Visible = False Then sheets(sheets("control").Range("p45").Value).Visible = True
    sheets("control").Range("E30").Select
    
    ActiveCell.FormulaR1C1 = "=iferror(INDEX(INDIRECT(R46C16),MATCH(C2,INDIRECT(R47C16),0),MATCH(R10C5,INDIRECT(R48C16),0)),IF(IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,1,1,1),"""")=0,"""",IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,1,1,1),"""")))"
    Range("F30").Select
    ActiveCell.FormulaR1C1 = "=iferror(INDEX(INDIRECT(R46C16),MATCH(C2,INDIRECT(R47C16),0),MATCH(R10C6,INDIRECT(R48C16),0)),IF(IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,2,1,1),"""")=0,"""",IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,2,1,1),"""")))"
    Range("G30").Select
    ActiveCell.FormulaR1C1 = "=iferror(INDEX(INDIRECT(R46C16),MATCH(C2,INDIRECT(R47C16),0),MATCH(R10C7,INDIRECT(R48C16),0)),IF(IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,3,1,1),"""")=0,"""",IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,3,1,1),"""")))"
Range("e30:g30").Select
    Selection.Copy
    
    Range("e31:e200").Select
 
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("e30:g200").Calculate
    Range("e10:g200").Copy
    Range("e10:g200").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
SendKeys ("{ESC}")
    
    Dim rng As Range
    Dim byt As Byte
       For Each rng In Range("e10:g200").Cells.SpecialCells( _
            xlCellTypeConstants, _
            xlTextValues)
            
            For byt = 1 To Len(rng.Value)
                If Mid$(rng.Value, byt, 1) = Chr(95) Then
                    rng.Characters(byt, 1).Font.Color = _
                        rng.Interior.Color
                End If
            Next byt
        Next rng
    
If sheets(sheets("control").Range("p45").Value).Visible = True Then sheets(sheets("control").Range("p45").Value).Visible = False
    
    Range("f7").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[hiker95]

dwhitey1124,

You are already using the following at the end of your macro:

Application.EnableEvents = True
Application.ScreenUpdating = True

Instead of using the following at the start of the macro:

If Application.ScreenUpdating = True Then Application.ScreenUpdating = False
If Application.EnableEvents = True Then Application.EnableEvents = False

Try this:

Application.ScreenUpdating = False
Application.EnableEvents = False

 

[shg]

An alternative, perhaps:

Sub Populate_Categories()
    Dim cell        As Range
    Dim iChr        As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    With Worksheets("control").Range("E30:G200")
        .Columns(1).FormulaR1C1 = "=iferror(INDEX(INDIRECT(R46C16),MATCH(C2,INDIRECT(R47C16),0),MATCH(R10C5,INDIRECT(R48C16),0)),IF(IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,1,1,1),"""")=0,"""",IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,1,1,1),"""")))"
        .Columns(2).FormulaR1C1 = "=iferror(INDEX(INDIRECT(R46C16),MATCH(C2,INDIRECT(R47C16),0),MATCH(R10C6,INDIRECT(R48C16),0)),IF(IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,2,1,1),"""")=0,"""",IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,2,1,1),"""")))"
        .Columns(3).FormulaR1C1 = "=iferror(INDEX(INDIRECT(R46C16),MATCH(C2,INDIRECT(R47C16),0),MATCH(R10C7,INDIRECT(R48C16),0)),IF(IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,3,1,1),"""")=0,"""",IFERROR(OFFSET(INDEX('Default Benchmarks'!C14:C17,MATCH(IMDBManagerAssetsUnderManagement(MarsPortfolioProduct(RC2),R3C2,R3C2,""assetclassname"",""---"",""USD""),'Default Benchmarks'!C14,0),1),0,3,1,1),"""")))"
        .Value = .Value

        For Each cell In .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
            For iChr = 1 To Len(cell.Text)
                If Mid$(cell.Value, iChr, 1) = "_" Then
                    cell.Characters(iChr, 1).Font.Color = cell.Interior.Color
                End If
            Next iChr
        Next cell
    End With

    Worksheets(Worksheets("control").Range("P45").Value).Visible = False

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub