How to assign variable to Data Validation Formula1:= in Excel VBA?

excel vba validation

13,820

Solution 1

I had a similar problem and did not try it via ... With ActiveCell.Validation: .Delete: .Add ... but via .Modify ... (Formula1 is read-only), which worked:

ActiveCell.Validation.Modify Formula1:= ...

(So with your code the value could be "=Sheet2!A2:A99", sheetRef.Range("A2:A" & iLastRow).Text or Rng.Text (no need for Set when using .Text) I guess.)

Solution 2

For a similar problem this essentially worked for me. The formula1 parameter accepts a string so...

Function DisplayName()

    Dim iLastRow As Integer ' This variable will get Last Cell which is not empty in a Column
    Dim formulaString As String

    iLastRow = Sheets("BM").Cells(Rows.Count, "A").End(xlUp).Row
    formulaString = "='BM'!A2:A" & iLastRow

    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=formulaString
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Select From List"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Function

13,820

Author by

Mahendra

Updated on July 10, 2022

Comments

Mahendra almost 2 years

I am trying to assign a variable Rng to Formula1:=.

Function DisplayName()

Dim iLastRow As Integer ' This variable will get Last Cell which is not empty in a Column
Dim Rng As Variant ' This variable is created for Dynamic Range selection in a sheet2 named as "BM"

Dim sheetRef As Worksheet
Set sheetRef = Sheets("BM")

iLastRow = sheetRef.Cells(Rows.Count, "A").End(xlUp).Row
Rng = sheetRef.Range("A2:A" & iLastRow)

    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween, Formula1:=Rng
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Select From List"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Function

The following error message points to .Add Type:=

Run-time error '1004': Application-defined or object-defined error

If I remove the variable and place "=Sheet2!A2:A99" it works.

Mahendra almost 9 years

Hi nicholas, I tried this. it doesn't work even if i set it
Mahendra almost 9 years

Here it is, Run-time error '1004': Application-defined or object-defined error
nicholas79171 almost 9 years

As per the documentation, the value for Formula1 needs to be of type Variant where as currently you're trying to give it a range.