Turning off Excel Compatibility Checker for "Significant Loss of Functionality" errors

Question

I've found that, while it's possible to programatically disable Excel's "Compatibility Checker" via code (by using ActiveWorkbook.CheckCompatibility = False, either before a SaveAs call or globally by trapping the ActiveWorkbook.BeforeSave event), it doesn't seem to work if there is a "Significant loss of functionality" detected. Quick way to test this:

• Create a new Excel 2010 workbook.
• Select A1:A2 and choose a conditional formatting (doesn't matter what).
• Select A2:A3 and choose a different conditional formatting. A2 should have two different conditional formats applied.

• Open the VBA editor, and add the following code to the Workbook module:

  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      ActiveWorkbook.CheckCompatibility = False
  End Sub
  

• Put a breakpoint in the code.

• Back in the spreadsheet, select File > Save As. The code will immediately jump to the breakpoint. If you step through the code, you can verify the CheckCompatibility setting in the Immediate pane.
• Once the code has completed, choose the Excel 97-2003 file type and click "Save."
• The compatibility checker still comes up.

I suspect this is because the error isn't a "minor compatibility issue" (cf. http://msdn.microsoft.com/en-us/library/office/gg132972(v=office.14).aspx) but nothing I do seems to suppress this error, not even creating a registry entry to disable it. Anyone have any idea how to suppress the checker even with "significant" incompatibility?

ETA: Without going into a lot of unnecessary detail, I'm trying to automate a process where a number of vendor templates are opened, populated with data, processed according to an enormous (and always slightly different) set of quality control rules, and saved back out as an .xls file (per the vendor's requirements). Because this happens on dozens of different template workbooks every two hours on an unattended system, I can't simply uncheck the compatibility requirement on a per-workbook basis. I mean, I suppose I could, but that would become my full-time job. I need to be able to turn off compatibility checking at run-time for any workbook, the first time, without human intervention.

Answer 1

Try Application.DisplayAlerts = False as a workaround.

Answer 2

Created a workaround that isn't completely full-featured but it at least hits everything I personally need; maybe it'll serve as a launching point for someone else. Note that this doesn't address the compatibility checker in all cases, just in case of overlapping custom formats.

In a nutshell, this goes through all active cells, and for any cell containing a conditional format, evaluates whether or not the custom formatting should be applied (in the correct order), then manually applies it. Finally, all custom formats are deleted. This leaves the workbook formatted but removes the cause of the compatibility checker being forced to appear. YMMV.

Sub FlattenFormats()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim asheet As Worksheet
    Set asheet = wb.ActiveSheet

    Dim cellvalue_regex As New RegExp
    cellvalue_regex.Pattern = "^""(.*)""$"

    Dim c As Range
    Dim conds As Collection

    For Each c In asheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
        If c.FormatConditions.Count > 0 Then
            Set conds = New Collection
            Dim fc As FormatCondition
            Set fc = Nothing
            For Each fc In c.FormatConditions
                conds.Add fc
            Next fc
            c.FormatConditions.Delete

            Sort conds

            Set fc = Nothing
            For Each fc In conds
                Select Case fc.Type
                    Case XlFormatConditionType.xlCellValue
                        Dim theMatches As MatchCollection
                        Set theMatches = cellvalue_regex.Execute(fc.Formula1)
                        Dim match1 As Match
                        Set match1 = theMatches.Item(0)
                        Dim checkFor As String
                        checkFor = match1.SubMatches(0)
                        If c.Value2 = checkFor Then
                            c.Interior.Color = fc.Interior.Color
                            If fc.StopIfTrue Then
                                Exit For
                            End If
                        End If
                    Case XlFormatConditionType.xlExpression
                        If Evaluate(fc.Formula1) Then
                            c.Interior.Color = fc.Interior.Color
                            If fc.StopIfTrue Then
                                Exit For
                            End If
                        End If
                End Select
            Next fc
        End If
    Next c

    ActiveSheet.Cells.FormatConditions.Delete
End Sub

Private Sub Sort(ByRef c As Collection)
    Dim i As Integer, j As Integer
    Dim temp As FormatCondition
    Dim i_item As FormatCondition, j_item As FormatCondition

    For i = 1 To c.Count - 1
        Set i_item = c(i)

        For j = i + 1 To c.Count
            Set j_item = c(j)

            If i_item.Priority > j_item.Priority Then
                Set temp = c(j)
                c.Remove j
                c.Add temp, temp.Priority, i
            End If
        Next j
    Next i
End Sub