Have you tried to publish as a web app an Excel Workbook and received an error regarding Data Validation (as shown in the image below) stating “The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser. Data Validation. …”.

[begin rant]Isn’t it great that MS has made its own feature incompatible with its own software! Brilliant (again)! What is even better is they do not provide a tools to convert, render a file compatible.[/end rant]

 

 

Now the only source of help I could locate was an explanation that you could use the find utility to locate cells with data validation and the delete them one by one…. For a simple workbook, maybe and even then. But when you are working with 10s, 100s of worksheets with 100s, 1000s or rows/column it simply become unfeasible to even consider doing this manually. As such, I put together the following routine to clean out any existing Data Validation from a workbook. This is a brute force method, but it does work.

 

'---------------------------------------------------------------------------------------
' Procedure : ClearAllDataValidation
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Remove all data validation from a workbook to make it compatible with
'             SharePoint
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-Jan-22                 Initial Release
' 2         2023-10-11                  Updated Header
'                                       Added missing variable declarations
'                                       Updated Error Handler
'---------------------------------------------------------------------------------------
Function ClearAllDataValidation()
On Error GoTo Error_Handler
    Dim ws              As Worksheet
    Dim Cell            As Range
    Dim wsVisible       As Boolean

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In Worksheets
        Debug.Print "Processing worksheet :: " & ws.Name
        wsVisible = ws.Visible    'Original visibility setting
        ws.Visible = xlSheetVisible    'Make the worksheet visible
        ws.Activate
        For Each Cell In ActiveSheet.UsedRange.Cells
            On Error Resume Next
            If Cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
                'No validation
            Else
                Cell.Validation.Delete
            End If
            On Error GoTo 0
        Next
        ws.Visible = wsVisible    'set it back as it was originally
    Next ws

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: d" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

I would like to refine it further, but well see. Since this is the type of thing you only need to do once, optimizing it further isn’t a priority right now. Time permitting down the road. Off to put out the next fire for now.

Also, if you are looking for a listing of what is and isn’t compatible between regular MS Excel workbooks and those open using a web-based browser, see: https://web.archive.org/web/20110222121738/http://office.microsoft.com/en-us/sharepoint-server-help/differences-between-using-a-workbook-in-the-browser-and-in-excel-HA010369179.aspx. There you’ll see a listing of what is compatible, what isn’t and what might behave differently. For instance, workbooks with VBA will have the VBA non-functional and the file itself will not be editable!

So at the end of the day, using Excel files through a web-browser will only work for the most basic workbook! Be forewarned.