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
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