VBA Data validation dropdown from list (modular sub- no hardcode)

Question

I want to create data validation list by VBA EXCEL code, each validation from a single list that is dynamically updated (e.g. if items are added to the validation source list the range will update by code, not manually).

I have many individual validations like this.

First I created separate VBA subs for each validation with hardcode ranges but that is not modular, the code repeated itself with similar code, It was not easy to maintain each time I need to update the source and destination range or add a new validation. It is sensitive to coding errors, having to remind myself where exactly should I write the range inside the code. I want a general and modular VBA validation list I can keep using without repeating the same code structure again and again.

I have a "Data" Sheet with the validation comboboxes and a "Info" sheet with the validation lists.

Here is an example of VBA Validation structure that has hardcoded sheet and cell ranges. A real headache to rewrite each time:

Public Sub HardcodedValidate()
Dim lastRowSource10 As Long
Dim rangeCombo10  As Range
Const F1_TransalteTo As String = "O"
Const F2_TransalteTo As String = "E"
    lastRowCombo10 = 9999
    lastRowSource10 = Transalte.Cells(Transalte.Rows.Count, "F").End(xlUp).Row
    Set rangeCombo10 = Phones.Range(F2_TransalteTo & "2:" & F2_TransalteTo & lastRowCombo10)
    With rangeCombo10.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="=Transalte!$F$2:$F$" & lastRowSource10
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = vbNullString
        .ErrorTitle = vbNullString
        .InputMessage = vbNullString
        .ErrorMessage = vbNullString
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Noam Brand · Accepted Answer · 2021-07-24 18:54:24Z

0

Option Explicit

'' Validation list with scrollable dropdown.
'' * General Sub without hardcoded specific ranges (modular).
'' * No empty cells at the end of each dropdown.
'' * Calculated Source LastRow.
'' See at the end an example of Call of main sub by RunGeneralValidate

Public Sub GeneralValidate( _
ByVal sheetSource As Worksheet, ByVal columnSource As String, ByVal firstRowSource As Long, _
ByVal sheetCombo As Worksheet, ByVal columnCombo As String, ByVal firstRowCombo As Long, ByVal lastRowCombo As Long)

Dim rangeSource As Range
Dim rangeCombo As Range
Dim lastRowSource As Long

   lastRowSource = sheetSource.Cells(sheetSource.Rows.Count, columnSource).End(xlUp).Row

    Set rangeCombo = sheetCombo.Range(columnCombo & firstRowCombo & ":" & columnCombo & lastRowCombo)
    Set rangeSource = sheetSource.Range("$" & columnSource & "$" & firstRowSource & ":$" & columnSource & "$" & lastRowSource)
    
    With rangeCombo.Validation
        .Delete ''delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="=" & "'" & sheetSource.Name & "'" & "!" & rangeSource.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = vbNullString
        .ErrorTitle = vbNullString
        .InputMessage = vbNullString
        .ErrorMessage = vbNullString
        .ShowInput = True
        .ShowError = True
    End With      
End Sub
   
Public Sub RunGeneralValidate()
''Example of running the sub
Dim Info As Worksheet
Dim Data As Worksheet

Call GeneralValidate(Info, "A", 2, _
                     Data, "D", 4, 100)
                     
Call GeneralValidate(Info, "B", 2, _
                     Data, "E", 4, 100)

Call GeneralValidate(Info, "C", 2, _
                     Data, "F", 4, 100)
End Sub

edited Jul 24, 2021 at 18:54

answered Apr 19, 2021 at 12:12

Noam Brand

3463 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

FunThomas Over a year ago

So basically you found a way to use a range variable?

Noam Brand Over a year ago

Yes, You could say that. I think its a practice that makes a huge code readability difference.

FunThomas Over a year ago

Sure. But I'm not sure why you post this on SO

Noam Brand Over a year ago

Ok I accept it is not a Nobel prize solution, but it is a real issue I was puzzled with and couldn't find online ,so I thought it might help beginners or save people some time or learn good practice.

Collectives™ on Stack Overflow

VBA Data validation dropdown from list (modular sub- no hardcode)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related