Kapat

VBA Dynamic Data Validation List With Unique Distinct Sorted Values

excel invoice 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”.

excel vba data validation list with unique sorted values

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 : excel populate data validation list with unique distinct sorted value

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;
If Len (veri)> 255 Then
MsgBox "Inconvenient value for the data validation list!", VbCritical, "Alert"
veri.Clear
End If

added this codes. Because ,corruption occurs if the text string for the data validation list source is more than 255 characters.
Replace(veri.Value, ",", ".") 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;
If veri = Range ("B1") Then
st.Remove Range ("B1"). Text
End If

We added these codes. excel vba arraylist

vba data validation list

Excel found unreadable content in 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) .
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
On Error Resume Next
sht.Cells.SpecialCells(xlCellTypeAllValidation).Validation.Delete
On Error GoTo 0
Next sht
End Sub

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 :
Private Sub Worksheet_Activate()
Call Create_data_val_list
End Sub

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 : excel dynamic data validation list

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

📥 Download sample Excel workbook that contains data validation list