There you’ll see a listing of what is compatible, what isn’t and what might behave differently. Off to put out the next fire for now.Īlso, 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. Since this is the type of thing you only need to do once, optimizing it further isn’t a priority right now. I would like to refine it further, but well see. Ws.visible = wsVisible 'set it back as it was originallyĪpplication.ScreenUpdating = True Error_Handler_Exit: If Cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then It may not be sold/resold or reposted on other sites (links ' copyright notice is left unchanged (including Author, Website and ' Copyright : The following may be altered and reused as you wish so long as the ' Purpose : Remove all data validation from a workbook ' Author : Daniel Pineault, CARDA Consultants Inc. VbCritical, "An Error has Occurred!" Resume Error_Handler_Exit "Error Description: " & Err.Description, _ "Error Source: ClearAllDataValidation" & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _ Ws.visible = wsVisible 'set it back as it was originally Next ws On Error Resume Next If Cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then 'No validation Else Ws.visible = xlSheetVisible 'Make the worksheet visibleįor Each Cell In WsVisible = ws.visible 'Original visibility setting Print "Processing worksheet :: " & ws.Name 'On Error GoTo Error_Handler Dim ws As WorksheetĪpplication.DisplayAlerts = False For Each ws In Worksheetsĭebug. ![]() It may not be sold/resold or reposted on other sites (links ' back to this site are allowed). ' Website : ' Purpose : Remove all data validation from a workbook ' To make it compatible with SharePoint ' Copyright : The following may be altered and reused as you wish so long as the ' copyright notice is left unchanged (including Author, Website and ' Copyright). '- ' Procedure : ClearAllDataValidation ' Author : Daniel Pineault, CARDA Consultants Inc. ![]() This is a brute force method, but it does work. ![]() As such, I put together the following routine to clean out any existing Data Validation from a workbook. 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. ![]() For a simple workbook, maybe and even then. 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…. 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. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |