Dynamic Data Validation

[mikeymay]

I am using a Worksheet_SelectionChange event to populate a cell with a drop-down based on the selection in the adjacent cell

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

 

[Akuini]

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]

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

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

Formula1:="D_BFSpentCategories"

to no avail.....

 

[Akuini]

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]

I've left this bit of the build for the moment but I'll revisit your reply when I go back to it.