Adding Drop Down Lists Using Data Validation in Excel VBA

It is for Excel programming like Macro, function, formula, Pivot and other platform support..
alpeshviranik
Posts: 81
Joined: Thu Jul 17, 2014 4:58 pm
Contact:

Adding Drop Down Lists Using Data Validation in Excel VBA

Postby alpeshviranik » Wed Jul 30, 2014 5:15 pm

I need Excel VBA code to make Drop Down Lists Using Data Validation. I want to add "Data Validation List" by VBA code or dynamically.


Guest

Re: Adding Drop Down Lists Using Data Validation in Excel VB

Postby Guest » Wed Jul 30, 2014 5:23 pm

Apply the data validation for Column A from row number 5 to 100. The list for drop down option will be taken from column C.

Code: Select all

            dim startRowC as integer
            startRowC=5
           dim  endRowC as integer
           endRowC=40
        Range("A5:A100").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$C$" & startRowC & ":$C$" & endRowC
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = "Value is not Valid"
            .ShowInput = True
            .ShowError = True
        End With

you can give range by below code

Code: Select all

Test_RangeName=Range("C5:C40").Value
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & Test_RangeName
               


Return to “Microsoft Excel and VBA Forum”

Who is online

Users browsing this forum: No registered users and 0 guests