how do i reference a formula that uses quotations in excel VBA when trying to create a Validation table referencing. keep getting runtime error 1004

My dynamic table column reference is "CapitalWorks[Project Name]" I want to use this with the Indirect function.

The code:

excelapp.ActiveSheet.Range("B3").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Indirect(""CapitalWorks[Project Name]"")"

Cannot get this to work no matter what I can do, tried converting the whole formula to a string and referencing is, no cigar.

might be worth mentioning the table is in a different sheet, not sure if this is affecting the code although it shouldn't?

CodePudding user response：

Data Validation Using Exel Table Column

• If you insist on using ActiveSheet, remember that you can always do ActiveSheet.Parent.Worksheets("Sheet1") to reference Sheet1 in the same workbook.

Option Explicit

Sub ApplyDataValidationExcelTable()
    
    Const WorksheetName As String = "Sheet2"
    Const TableWorksheetName As String = "Sheet1"
    Const TableColumnAddress As String = "CapitalWorks[Project Name]"
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    With wb.Worksheets(WorksheetName).Range("B3").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:=xlEqual, _
        Formula1:="='" & TableWorksheetName & "'!" _
            & Range(TableColumnAddress).Address
    End With

End Sub

CodePudding user response：

I think you are missing 1 step and you don't need INDIRECT.

Create a name from the

Then create your validation list:

ActiveSheet.Range("F6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=R_LIST"

Notice this part: Formula1:="=R_LIST"

And this is what I got:

If I add new values in the column, list gets autoupdated :)