VBA multiple data validation in one cell

AlmazTyn

New Member
Joined
Nov 18, 2022
Messages
5
Office Version
  1. 2021
Platform
  1. Windows
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
VBA 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!
 

Attachments

  • 2022-11-18 150557.png
    2022-11-18 150557.png
    19.3 KB · Views: 13

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
I would use a Worksheet__Change code on the FORM sheet to alter the DV as a selection is made in the method column:

VBA Code:
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:

VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
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")?
 
Upvote 0
How can I set "Cell format" rules for each dv settings (Especially for "Date of birth" and "Years old")?
You can use something like:

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top