CODEWITHSUNDEEP
excelvbalistvalidation
Rodal
Rodal

Reputation: 1

VBA Excel Validation list over Variable Range

I tried this code to set a validation list in Excel. I'm trying to substitute specific cells with variables, but I can't make it work out with fixed cells.

Thank you.

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Range(Cells(5, 15), Cells(6, 15))"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Upvotes: 0

Views: 374

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Try this:

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= "=" & Range(Cells(5, 15), Cells(6, 15)).Address

or this:

   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= "=$O$5:$O$6"

Upvotes: 2

Related Questions