VBA - Error handling and adding data validation for dynamic drop-downs programmatically

[Goose306]

Hi all,

I have what I think is a bit of a unique issue (at least I wasn't able to get any hits on Google). I have a file where I have to add data validation programmatically. This data validation is dynamic dependent drop-downs. These have to be based off named ranges - they can't be strictly defined in VB, as I need it to be able to handle users adding to the lists and updating automatically at any given time.

The issue I have is adding the second and further levels of the dependent drop-down. When doing this by hand, you get a message that it evaluates to an error - this is because it is dependent on the setting in the first cell (thus, a dependent drop-down) - they work when a setting is made in the first cell.

In VBA, I can't find a way to handle this. An alert doesn't pop, so "DisplayAlerts" can't resolve it. Instead, I get an error 1004, for Application-Defined Error. Wrapping error handling around it doesn't work either - it just doesn't add the validation, if that's the case.

I figure I can make it set the first value in the first cell's validation prior to adding subsequent cell's validation, then removing that value after the validation is set, but that seems a messy solution. Does anyone have any ideas on how to best bypass this message so the validation will actually take - again, it does actually evaluate correctly, when the first cell is set to a value, but when adding in validation initially there is no value in the first cell.

Here's a snippet of code (it's adding validation based on formulas being read off another sheet and loaded into an array):

'Make changes tied to validation array
For x1 = LBound(selvallist, 2) To UBound(selvallist, 2)


  'Locate cells and add validation
  With builderwb.Worksheets("Data").Cells.Find(selvallist(3, x1), LookIn:=xlValues, lookat:=xlWhole).Offset(1, 0).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=selvallist(4, x1)
  End With
Next x1

The formula attempting to be added in as Validation is: =OFFSET(Anchor, 1, MATCH(V2, GenReason, 0)-1, COUNTA(OFFSET(Anchor, , MATCH(V2, GenReason, 0)-1, 50, 1))-1, 1)

The message that appears when adding it by hand is this (again, this actually works - if a selection is made in the first cell before adding validation, this error doesn't appear):

Thanks for any help you can provide!