Excel VBA - Data Validation - Using =INDIRECT() - creating dependent drop downs

0
votes

I'm currently struggling to create data validation drop downs, that are dependent on a previous selection

(Excel Version 16.32 on OS X)

What I want to achieve: In column F, I'd like to create data validation with the formula "=INDIRECT($Ei)" - so for cell F2 =INDIRECT(E2), cell F3 =INDIRECT(E3)

Problem: The current code works if a selection in column E has already been done. If cells in column E are empty, as it should be by default, I get an 1004 error

The code looks currently like this:

Dim currentrows As Integer
Dim i As Integer
Dim indirect_address As Variant

For Each ws In Sheets
    If ws.Name <> "Overview" And ws.Name <> "Hidden" Then
    currentrows = ws.UsedRange.Rows.Count
    ws.Activate
        With Range("E2:E" & currentrows).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=Hidden!$A$2:$A$4"
        End With
        For i = 2 To currentrows
            indirect_address = "E" & i
            With Range("F" & i).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=INDIRECT(" & Range(indirect_address).address(False, False) & ")"
            End With
        Next i
    End If
Next ws

I'm specifically struggling with this part, since I do not understand it to it's full extend

Formula1:="=INDIRECT(" & Range(indirect_address).address(False, False) & ")"

I used following snippet before, but it this result in =INDIRECT("Ei") which does not work because of the "" in the formula

Formula1:="=INDIRECT(""" & Range(indirect_address).address(False, False) & """)"

Thanks in anticipation -CM

2
excelvba

2 Answers

0
votes

let's try,change

Operator:=xlBetween, Formula1:="=INDIRECT(" & Range(indirect_address).address(False, False) & ")"

to

Operator:=xlBetween, Formula1:="=INDIRECT(""" & indirect_address & """)"
0
votes

I think I've found the reason and a work-around for this problem. It looks like during the run-time of the macro, VBA needs a valid result to not error out. This means fields in column E need to be populated with valid options to create a valid data validation in column F

This means I pre-populate column E with options before drop downs are created in column F. After that I set column E values back to .value = "" and everything works a s intended