Using VBA to Create Dependent Validations

[FinProdpm]

Hello All,

I've utilized these forums for some time now and have found them extremely helpful, however this is the first time I am posting a questions. I'd like to say thank you to anyone that has helped anyone out though, because helping one person probably helps 10x or 20x more people that read the post.

Now on to my issue... I am utilizing VBA to create two columns worth of validations. The first column (G) is created off of a named range "SC1_List", which has the following options {Security, Loan, Deposit, Borrowing}. I want the second column to be dependent based on the selection of the first column for the corresponding row (i.e. h2 dependent on g2, etc.), so I have four named ranges set up, as "SC2_List_Security", "SC2_List_Loan", etc. In VBA, I am using the following code to create the validation:

'Set SC1 Validation
ActiveCell.Offset(0, 6).Select
With Selection.Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=SC1_List"
    End With
'Set SC2 Validation
ActiveCell.Offset(0, 1).Select
    'Create text string for dependend validation formula
    strValidation = "=indirect(concatenate(""SC2_List_"",G" & i + 1 & "))"
With Selection.Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strValidation
     End With

I am getting an error 1004 application-defined or object defined error and I am not sure why.

I appreciate any help in advance. Thanks all.

 

[FinProdpm]

Additionally, I've found that changing

strValidation = "=INDIRECT(CONCATENATE(""SC2_List_"",G" & i + 1 & "))"

to

strValidation = "INDIRECT(CONCATENATE(""SC2_List_"",G" & i + 1 & "))"

Does not result in an error, however my data validation is populated with the resulting text string only.

 

[FinProdpm]

My understanding is that the 1004 error, application or object definition is because the variables are defined as the wrong type of object. Is that correct?

 

[FinProdpm]

It seems like setting the dependent validation works if column G is populated. Is there anyway to force VBA to continue setting up the dependent validations, recognizing that the user will have to define column G after the validations are established?

 

[FinProdpm]

To anyone that reads this thread, I've found that while not necessary if setting up dependent validations in excel, setting up dependent validations in VBA requires the precedent validation be populated in order to create the dependent.

As a result, I established the precedent validation, populated it with a valid value, set up the dependent validation and then cleared the precedent validation. This seems to work well, although not ideal.