Excel VBA Data Validation Lists
Data validation lists are indispensable for Excel users, as they are highly efficient tools.
In this study, we will create dynamic data validation lists that contained unique alphabetically sorted values with VBA codes for the whole column A without using a formula.
In our workbook; there are two sheets named “MainSheet” and “Products”. The data are got from column “B” of Products sheet, are listed the data by creating Data Validation Lists on MainSheet column “A”.
To create dynamic data validation lists, we added Create_data_val_list procedure to a module in the VBA Window. Codes of Create_data_val_list procedure :
Sub Create_data_val_list() Dim st As Object, son As Long, veri As Range Set st = CreateObject("System.Collections.ArrayList") son = Sheets("Products").Cells(Rows.Count, "B").End(xlUp).Row For Each veri In Sheets("Products").Range("B2:B" & son) On Error Resume Next If Len(veri) > 255 Then MsgBox "Inconvenient value for the data validation list !", vbCritical, "Alert" veri.Clear End If If Len(veri) <= 255 Then If st.Contains(Replace(veri.Value, ",", ".")) = False Then st.Add (Replace(veri.Value, ",", ".")) End If End If If veri = Range("B1") Then st.Remove Range("B1").Text End If Next st.Sort With Sheets("MainSheet").Range("A:A").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(st.toarray, ",") End With Sheets("MainSheet").Range("A1").Validation.Delete End Sub
When we examine the above codes , we see that the values in column B cells are added to ArrayList object named “st”. For the value in the cell to not exceed 255 characters;
added this codes. Because ,corruption occurs if the text string for the data validation list source is more than 255 characters.
codes, if there is “,” (comma) in the product values, it will “.” replaces with dot. Because the comma character is used in the array and when Excel sees a comma, it acts with the assumption of a new list item.
Sometimes column B may not have a cell value except the header. In such cases, in order not to add the header cell (cell B1) as an item to the data validation list;
We added these codes.
Also, we added a code snippet to the Workbook_BeforeClose procedure to avoid this error : “Excel found unreadable content in ‘…….. xlsm’. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.” (The error seen on opening in workbooks that contain VBA data validation lists) .
With this code ,when the workbook is closed we removed all data validation lists. Thus, no warning is encountered when the workbook is opened.
We added this code snippet to the Worksheet_Activate procedure of the Products sheet to generate data validation lists on MainSheet when the workbook is opened :
We added the following codes to the Worksheet_Change procedure of Products sheet to automatically reflect those changes (delete, add, etc.) to the data validation lists whenever there is any changing in column B of Products sheet :
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub Else Call Create_data_val_list End If End Sub