Both workbook "freezes" when updating the first (xlsm) while the second (xlsx) is open

Question

The problem

I have a workbook (A) which includes some macros as well as being linked to another Excel-file (data source). The workbook works perfectly and without problems if I only have this individual workbook open. If I open another random xlsx-file everything looks OK at first. But as soon as I make any updates whatsoever (e.g. pressing F2 in an empty cell and then enter) in workbook A, both workbooks seems to freeze. I can still close the workbooks in a normal procedure and the macro-button in workbook A works fine (and will trigger the macro), but I'm unable to change tabs in wb A and the tabs in the second workbook disappears. I can see the cursor, but the green Excel-border/box around target-cells are gone in both workbooks. I am also unable to update any of the cells in the workbooks...

What I've tried

I have tried to;

• remove all data source connections

• remove all macros (one by one, but problem still exist without any macros in wb A...)

• go through the macros to see if they causes any errors (which they don't as far as I can see)

• two other xlsm-files (unrelated to wb A) does not cause this type of error

• i've tried to use application.ScreenUpdating = False, .EnableEvents = False, .Calculation = xlCalculationManual, and setting them back to original values at the end.

Code

-Range("B4") is a dropdown-list without blanks. The following macro lies in Sheet1:

Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ApplicationON:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
       Call conditionalFormatting.conditionalFormatting

ApplicationON:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

-The following macro lies in a module called "conditionalFormatting":

Sub conditionalFormatting()

    On Error GoTo ApplicationON:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    Dim DASHBOARD   As Worksheet
    Dim rng1        As Range
    Dim rng2        As Range
    Dim cel         As Range
    Dim col1        As Integer
    Dim col2        As Integer

    Set DASHBOARD = Sheets("DASHBOARD")
    Set rng1 = Range("R15:R45")
    Set rng2 = Range("R15:Z45")
    col1 = 18
    col2 = 26

    With rng2
        .Cells.Font.Bold = False
        .Cells.Font.Italic = False
        .Cells.Font.Size = 11
    End With

    For Each cel In rng1
        Select Case cel.Value

        Case _
        "Case1", _
        "Case2"
            Range(Cells(cel.Row, col1), Cells(cel.Row, col2)).Font.Bold = True

        Case _
        "Case3", _
        "Case4"
           Range(Cells(cel.Row, col1), Cells(cel.Row, col2)).Font.Size = 8

        Case _
        "Case5", _
        "Case6"
           Range(Cells(cel.Row, col1), Cells(cel.Row, col2)).Font.Italic = True

       End Select
    Next  

ApplicationON:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 

End Sub

-The following macro lies in a module called "ExportToPDF" and has a button in worksheet "DASHBOARD" (sheet1):

Sub ExportToPDF()

    On Error GoTo ApplicationON:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    Dim pt As Range

    dateStamp = Format(Now(), "yyyymmdd\_hhmm")
    workbookPath = ActiveWorkbook.Path & "\"
    workbookName = ActiveWorkbook.Name
    file_Name = dateStamp & "_" & Sheets("DASHBOARD").Range("A1") & ".pdf"
    filePath = workbookPath & file_Name

    With Worksheets("DASHBOARD").PageSetup
    .PrintArea = "A6:O42"
    .Orientation = xlLandscape
    End With

    Set pt = 
  Worksheets("DASHBOARD").Range(Worksheets("DASHBOARD").PageSetup.PrintArea)

    pt.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

    MsgBox "PDF file has been created: " _
          & filePath

ApplicationON:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 

End Sub

After some additional testing

It seems it has to do with looping and module Workbook_change + my dropdown-list which does not include any blank values. But still strange that it works perfectly without any other wb open, but only becomes a problem when opening an additional wb. Can't see that the code is running either when both wbs freezes...

1) made a copy and removed the data connections (so it doesn't interfere), and saving and closing the workbook

2) Opening the workbook (without any of the errorHandling and application-statement) without make any changes/updates in the spreadsheets, and opening a second file (slsx) - error occur

3) Opening the workbook (without any of the errorHandling and application-statement) and writing "=1+1" in an random empty cell, and opening a second file (slsx) - error occur

4) Opening the workbook (without any of the errorHandling and application-statement) and changing the dropdown-list once (calling the worksheet_change macro), and opening a second file (slsx) - error occur

5) Opening the workbook (in original state as posted) without making any changes/updates in the spreadsheets, and opening a second file (slsx) - error occur

6) Opening the workbook (in original state as posted) and writing "=1+1" in an random empty cell, and opening a second file (slsx) - error does not occur

7) Opening the workbook (in original state as posted) and changing the dropdown-list once (calling the worksheet_change macro), and opening a second file (slsx) - error occur

8) If I insert a blank value in the drop-downlist (workbook in original state as posted) and selecting the blank value, and opening a second file (slsx) - error does not occur

9) Selecting a value in dropdown-list - error occur

8) Removing the ExportToPDF- and conditionalFormatting-modules, and including proposed adjustment to Worksheet_change module (i.e. removing code from sheet1 and inserting it into module).

• 8.1 just opening second file without making changes to xlsm, and having value in dropdown-list - error occur
• 8.2 typing 1+1 in random empty cell - error occur
• 8.3 changing dropdown-list once (to another value) - error occur
• 8.4 changing dropdown-list to blank - error does not occur

Answer 1

Seems like there was a mysterious error with the Excel-file. I rebuilt the dashboard and it worked as expected. To be on the safe-side I skipped the "Workbook_change"-code linked to a filter and just used a simple button to execute the code instead. Thanks for everyone's input.

Answer 2

If you cause an error in any of these functions, you set Application.EnableEvents = True. So... If those are triggered from a call originating from inside an event handler, you lose your re-entry protection. You also unconditionally do this at the top of everything:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

That smacks of cargo-cult behavior. You should only be performing this work where it is necessary, and close to the place where it is necessary. Whatever performance gains you think you're getting by doing this are probably just illusory. In fact, repeatedly messing with the Application state is probably doing more harm than good from a performance standpoint.

The solution is to not rely on Excel to guard re-entry into your event handler - do it manually:

Private reentryFlag As Boolean    'Module level

Sub Worksheet_Change(ByVal Target As Range)
    If reentryFlag Then Exit Sub
    reentryFlag = True

    On Error GoTo Handler
    If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub

    conditionalFormatting.conditionalFormatting
Handler:
    reentryFlag = False
End Sub