Creating dynamic data validation lists in Excel using VBA

Creating dynamic data validation lists in Excel using VBA can be a very useful technique, especially when the lists change frequently based on other data or user selections. Below is a detailed explanation and code on how to achieve this.

Objective:

We will create dynamic data validation lists that update automatically based on changes in the source data.

Steps Involved:

1. Create the Source Data: The source data is typically a range or list of items from which the dynamic list will be populated.
2. Create the Data Validation: This involves defining a data validation rule in Excel, which will use the source data as the list.
3. Use VBA to Update the List: The VBA code will dynamically adjust the data validation list based on changes in the source data range.

Example Scenario:

Let’s assume we have a list of product categories in Sheet1!A2:A10, and we want to create a dynamic data validation list in Sheet2!B2, which will update automatically as items are added or removed from the source list.

Step-by-Step Code Explanation:

1. Set up the VBA Code: We will write a VBA code to automatically create a dynamic data validation list.

VBA Code:

Sub CreateDynamicDataValidation()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim validationRange As Range
    Dim validationFormula As String
    ' Set worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Source data sheet
    Set wsTarget = ThisWorkbook.Sheets("Sheet2") ' Target data validation sheet
    ' Find the last row of the source list (assuming data starts from A2)
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' Set the dynamic range for validation (we assume data is in column A)
    Set validationRange = wsSource.Range("A2:A" & lastRow)
    ' Create the formula for dynamic data validation
    ' The formula uses OFFSET to define the dynamic range
    validationFormula = "=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$" & lastRow & "), 1)"
    ' Apply the data validation to the target cell (Sheet2!B2)
    With wsTarget.Range("B2").Validation
        .Delete ' Remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=validationFormula
        .IgnoreBlank = True
        .InCellDropdown = True ' Show the dropdown arrow in the cell
        .ShowInput = True
        .ShowError = True
    End With
    MsgBox "Dynamic Data Validation List Created!"
End Sub

Explanation of the Code:

1. Define Worksheets:
   • wsSource refers to the worksheet where the source data is located (in this case, « Sheet1 »).
   • wsTarget refers to the worksheet where the data validation will be applied (in this case, « Sheet2 »).
2. Find the Last Row:
   • The lastRow variable is determined by finding the last row in column A of the source sheet. This ensures the range is dynamic and will adapt as more data is added or removed.
3. Define the Validation Range:
   • The range for data validation is defined dynamically using wsSource.Range(« A2:A » & lastRow). This means the range for data validation will include all rows from A2 to the last row with data.
4. Create the Validation Formula:
   • The formula uses the OFFSET function to create a dynamic range. OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$ » & lastRow & « ), 1) creates a dynamic range that adjusts as rows are added or removed in the source list.
   • COUNTA(Sheet1!$A$2:$A$ » & lastRow & « ) counts the non-empty cells in the source list and adjusts the range size accordingly.
5. Apply the Data Validation:
   • .Validation.Delete removes any existing validation rules from the target cell (if any).
   • .Add adds a new validation rule of type xlValidateList (for a drop-down list).
   • .Formula1 is where we apply the dynamic validation formula.
   • .InCellDropdown = True makes sure that the drop-down arrow appears inside the target cell.
   • .ShowInput and .ShowError ensure that input and error messages are shown when needed.
6. Completion Message:
   • A message box is displayed to let the user know that the data validation list has been successfully created.

How the Code Works:

• Every time the macro is run, it checks the source list (Sheet1!A2:A10), finds the last row with data, and updates the data validation list in Sheet2!B2 accordingly. If rows are added or removed in the source list, the drop-down list will automatically adjust to reflect the changes.

How to Use the Code:

1. Open your workbook.
2. Press Alt + F11 to open the VBA editor.
3. Insert a new module by right-clicking on the « VBAProject » pane, selecting Insert > Module.
4. Paste the code into the module.
5. Close the VBA editor and run the macro by pressing Alt + F8, selecting CreateDynamicDataValidation, and clicking Run.

Additional Notes:

• The code assumes that the source data begins at cell A2 and goes down to the last filled cell in column A. Adjust the ranges as needed if your data is located elsewhere.
• You can modify the target cell for the data validation (currently Sheet2!B2) to any cell or range that you wish to apply the validation.

Conclusion:

This VBA code allows you to create a dynamic data validation list that automatically updates as the source data changes. This is useful for situations where you have regularly updated lists, and you want to avoid manually adjusting data validation rules each time new data is added.

Voici un exemple détaillé pour créer des listes de validation de données dynamiques avec VBA dans Excel.