Dynamic Data Validation

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,540
Office Version
  1. 365
Platform
  1. Windows
I am using a Worksheet_SelectionChange event to populate a cell with a drop-down based on the selection in the adjacent cell
VBA Code:
With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, _
               AlertStyle:=xlValidAlertStop, _
               Operator:=xlBetween, _
               Formula1:="=D_FDBFSTypes"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "ERROR"
            .InputMessage = ""
            .ErrorMessage = "Make a selection from the drop-down list only"
            .ShowInput = False
            .ShowError = True
         End With
I am having a problem with the Formula1 line where the (dynamic) named range in a different worksheet is applied.

Sometime this works (I can't work out why it works when it does work), but the majority of times it doesn't work.

I have tried to create a string that includes all the items in the dynamically established list but that didn't work either.

Any pointers anyone?


TIA
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,975
Office Version
  1. 365
Platform
  1. Windows
I am using a Worksheet_SelectionChange event to populate a cell with a drop-down based on the selection in the adjacent cell
Are you talking about dependent data-validation?
1. Instead of Worksheet_SelectionChange, people usually use Indirect function to set up dependent data-validation, here's an example: LINK
Any particular reason why you're using Worksheet_SelectionChange?
2. Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive? And then share the link here.
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,540
Office Version
  1. 365
Platform
  1. Windows
Yes, dependant DV

Column A has a set DV where a category can be selected
Column B DV will be dependant on what is in Column A

When the cell in column B is selected, the Column A value is added to a cell which creates a dynamic list of the drop-down values for column B.

The code to do this is
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Or Intersect(Target, Range("BFSpent_ColType")) Is Nothing Then
   Else
   Range("BFSpent_ColType").Validation.Delete
   
   With Target
      If .Offset(0, -1) = "" Then
         Else
         Application.ScreenUpdating = False
         
         Sheets("File Data").Range("FD_Category") = Target.Offset(0, -1)
                  
         DoEvents
         
         Application.Calculation = xlManual
         
         With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, _
               AlertStyle:=xlValidAlertStop, _
               Operator:=xlBetween, _
               Formula1:="=" & Range("D_BFSpentCategories").Address
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "ERROR"
            .InputMessage = ""
            .ErrorMessage = "Make a selection from the drop-down list only"
            .ShowInput = False
            .ShowError = True
         End With
         
         Application.Calculation = xlAutomatic
         
         Application.ScreenUpdating = True
      End If
   End With
End If
  
End Sub
The list of Column B values to go into the drop-down are in the worksheet in which this code sits as I thought it was an issue around the list being in a nother worksheet, but I am still getting the problem.

I have tried
VBA Code:
Formula1:="D_BFSpentCategories"
to no avail.....
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,975
Office Version
  1. 365
Platform
  1. Windows
1. Did you try using Indirect function as described in the link I gave you?
2. Also it would be helpful if you could upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive. And then share the link here.
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,540
Office Version
  1. 365
Platform
  1. Windows
I've left this bit of the build for the moment but I'll revisit your reply when I go back to it.
 

Forum statistics

Threads
1,175,610
Messages
5,898,437
Members
434,709
Latest member
Ev1lZer0

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
Top