Create Data Validation Lists Using VBA - Excel found unreadable content in error

Occasional Contributor

Dynamic-datavalidation-lists-VBA.gif

 

Using the VBA codes, drop-down lists that filled with unique and the sorted alphabetically values can be created.

The items of the drop-down lists can be populated in from the same sheet or from other sheet.

When a modify is made to list items (add, delete, etc.), this modify can automatically be seen in the drop-down lists.

 

Excel found unreadable content in ... : 

When I opened the workbook containing the data validation lists that I created using VBA codes, I was encountering this problem. On this, I added code snippet to delete data validation lists while closing the workbook :

 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeAllValidation).Validation.Delete
On Error GoTo 0
Next ws
End Sub

 

I also added a code so that the items to be added to the data validation list do not exceed 255 characters.

 

Details , sample file at : Vba data validation list

0 Replies