How to add data validation to a cell using VBA

Question

I want to  add  data validation  in a cell which is variable  using VBA and the range which is to come in the  data validation list is also variable  Till now I have been using this  Here  range1  is the range which is to come in the data validation list and  rng  is the cell where I want the data validation  Dim range1  rng As range Set range1   range  a1 a5   Set rng   range  b1   With rng With  Validation      Add Type  xlValidateList  AlertStyle  xlValidAlertStop  Operator         xlBetween  Formula1   range1  End With End With   I am getting  application defined and object defined error   Also can someone explain me the meaning of different arguments in  With  Validation  Add Type  xlValidateList  AlertStyle  xlValidAlertStop  Operator         xlBetween  Formula1   range1

User · Accepted Answer

Use this one   Dim ws As Worksheet Dim range1 As Range  rng As Range  change Sheet1 to suit Set ws   ThisWorkbook Worksheets  Sheet1    Set range1   ws Range  A1 A5   Set rng   ws Range  B1    With rng Validation      Delete  delete previous validation      Add Type  xlValidateList  AlertStyle  xlValidAlertStop            Formula1        amp  ws Name  amp        amp  range1 Address End With   Note that when you re using Dim range1  rng As range  only rng has type of Range  but range1 is Variant  That s why I m using Dim range1 As Range  rng As Range  About meaning of parameters you can read is MSDN  but in short    Type  xlValidateList means validation type  in that case you should select value from list AlertStyle  xlValidAlertStop specifies the icon used in message boxes displayed during validation  If user enters any value out of list  he she would get error message  in your original code  Operator   xlBetween is odd  It can be used only if two formulas are provided for validation  Formula1        amp  ws Name  amp        amp  range1 Address for list data validation provides address of list with values  in format  Sheet A1 A5

[vba] How to add data validation to a cell using VBA

Examples related to vba

Examples related to excel