VBA multiple data validation in one cell

[AlmazTyn]

Hi there!

I am having some problems with data validation through VBA.

The "Age" column should have several data validations, depending on which method was selected by the user
should look something like this (refer to image)

here is my code

Sub DateOfBirth()
Dim FORM As Worksheet
Dim COLUMN_RANGE As Range
Set FORM = Worksheets("FORM")
Set COLUMN_RANGE = FORM.ListObjects("Database").ListColumns("Age").DataBodyRange
With COLUMN_RANGE.Validation
    .Delete
    .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="01-01-1900", Formula2:="=Now()"
    .IgnoreBlank = True
    .InCellDropdown = False
End With
End Sub

Sub YearsOld()
Dim FORM As Worksheet
Dim COLUMN_RANGE As Range
Set FORM = Worksheets("FORM")
Set COLUMN_RANGE = FORM.ListObjects("Database").ListColumns("Age").DataBodyRange
With COLUMN_RANGE.Validation
    .Delete
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="110"
    .IgnoreBlank = True
    .InCellDropdown = False
End With
End Sub

Sub AgeCategory()
Dim FORM As Worksheet
Dim COLUMN_RANGE As Range
Set FORM = Worksheets("FORM")
Set COLUMN_RANGE = FORM.ListObjects("Database").ListColumns("Age").DataBodyRange
With COLUMN_RANGE.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=INDIRECT(""AgeCategoryList[Age Category]"")"
    .IgnoreBlank = True
    .InCellDropdown = True
End With
End Sub

Sub AgeMethodDropDownList()
Dim FORM As Worksheet
Dim COLUMN_RANGE As Range
Set FORM = Worksheets("FORM")
Set COLUMN_RANGE = FORM.ListObjects("Database").ListColumns("Age Method").DataBodyRange

With COLUMN_RANGE.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=INDIRECT(""AgeMethodList[Age Method]"")"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = "Age Method"
    .InputMessage = "Pick Age Method from List"
    .ShowInput = True
End With
If COLUMN_RANGE.Value = "Date of Birth" Then
    Call DateOfBirth
ElseIf COLUMN_RANGE.Value = "Years Old" Then
    Call YearsOld
ElseIf COLUMN_RANGE.Value = "Age Category" Then
    Call AgeCategory
Else
End If
End Sub

With this code I have a run-time error "Type mismatch" on if else statements.
It would be also great, if you could also help me with the "Format" of the "Cell" depending on which data validation is. (if "date of birth" then cell format smth like "dd/mm/yyyy")

I am also very new to VBA, so if anyone has any suggestions, if you could show the entire code (from SUB to END SUB) instead of just the code lines, as I am still learning what goes where.

Thank you!

 

[RoryA]

COLUMN_RANGE is multiple cells, and you cannot compare an array of values to a single value.

Your code doesn't really make sense to me as you appear to be trying to alter the data validation for the entire column every time, presumably based on the values in each row?

 

[AlmazTyn]

Yes, I roughly understand about entire column because when i'm using exact one cell it is working.
But the thing is these validations should apply for whole exact table

 

[RoryA]

Why? It doesn't make sense to set the validation for an entire table column based on the value in one row, given that you appear to want different validation for each row based on the adjacent column.

 

[AlmazTyn]

given that you appear to want different validation for each row based on the adjacent column.

Yes, that's the idea

 

[RoryA]

I would use a Worksheet__Change code on the FORM sheet to alter the DV as a selection is made in the method column:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, ListObjects("Database").ListColumns("Age Method").DataBodyRange) Is Nothing Then
      Dim cell As Range
      For Each cell In Intersect(Target, ListObjects("Database").ListColumns("Age Method").DataBodyRange).Cells
         Select Case cell.Value
            Case "Date of Birth"
               Call DateOfBirth(cell.Row)
            Case "Years Old"
               Call YearsOld(cell.Row)
            Case "Age Category"
               Call AgeCategory(cell.Row)
         End Select
      Next cell
   End If
End Sub

Then amend your existing code to:

Function GetAgeCell(rwNum As Long) As Range
   Dim FORM As Worksheet
   Set FORM = Worksheets("FORM")
   Dim ageColumn As Range
   Set ageColumn = FORM.ListObjects("Database").ListColumns("Age").DataBodyRange
   Set GetAgeCell = ageColumn.Cells(rwNum - ageColumn.Row + 1)

End Function
Sub DateOfBirth(rwNum As Long)
   With GetAgeCell(rwNum).Validation
       .Delete
       .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="01-01-1900", Formula2:="=Now()"
       .IgnoreBlank = True
       .InCellDropdown = False
   End With
End Sub

Sub YearsOld(rwNum As Long)
   With GetAgeCell(rwNum).Validation
      .Delete
      .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="110"
      .IgnoreBlank = True
      .InCellDropdown = False
   End With
End Sub

Sub AgeCategory(rwNum As Long)
   With GetAgeCell(rwNum).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=INDIRECT(""AgeCategoryList[Age Category]"")"
      .IgnoreBlank = True
      .InCellDropdown = True
   End With
End Sub

 

[AlmazTyn]

I would use a Worksheet__Change code on the FORM sheet to alter the DV as a selection is made in the method column:

I don't know why, but it didn't help me

 

[RoryA]

It will not work for existing data (unless you re-enter it) but it will update the dv settings for each row as you choose an option in the method column. Note that the change event code must be in the code module for the FORM worksheet.

 

[AlmazTyn]

It will not work for existing data (unless you re-enter it) but it will update the dv settings for each row as you choose an option in the method column. Note that the change event code must be in the code module for the FORM worksheet.

It turns out that it really works great!
It was my mistake!
Thank you very much!
You are amazing!!!

PS. I have a question. How can I set "Cell format" rules for each dv settings (Especially for "Date of birth" and "Years old")?

 

[RoryA]

How can I set "Cell format" rules for each dv settings (Especially for "Date of birth" and "Years old")?

You can use something like:

Sub YearsOld(rwNum As Long)
   With GetAgeCell(rwNum)
      With .Validation
         .Delete
         .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="110"
         .IgnoreBlank = True
         .InCellDropdown = False
      End With
      .NumberFormat = "0"
   End With
End Sub

and just amend the NumberFormat part of each code to whatever format you want - eg "dd/mm/yyyy" for dates.