How to add data validation to a cell using VBA

by

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:="='" & ws.Name & "'!" & 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:="='" & ws.Name & "'!" & range1.Address for list data validation provides address of list with values (in format =Sheet!A1:A5)

More Related Contents:

  1. Simple VBA code to open cells one after the other
  2. Open folder in new instance
  3. Copy data from cell in selected row
  4. Refer to sheet using codename
  5. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  6. Assign code to a button created dynamically
  7. Last not empty cell (column) in the given row; Excel VBA
  8. Split comma separated entries to new rows [closed]
  9. Select first empty cell in column F starting from row 1. (without using offset )
  10. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  11. Excel VBA: AutoFill Multiple Cells with Formulas
  12. VBA.Find and adding a value
  13. What does ReDim Preserve do?
  14. VBA – How the colon `:` works in VBA code with condition
  15. Running VBA from a HYPERLINK()
  16. Sending formatted Lotus Notes rich text email from Excel VBA
  17. How can I generate GUIDs in Excel?
  18. Remove Duplicates from range of cells in excel vba
  19. Excel Select vs Activate
  20. Fast compare method of 2 columns [closed]
  21. How can an Excel Add-In respond to events in any worksheet?
  22. Fastest way to check if two ranges are equal in excel vba [duplicate]
  23. Remove double quotation from write statement in vba
  24. Meaning of .Cells(.Rows.Count,”A”).End(xlUp).row
  25. Missing VBA compiler message for wrong method name
  26. How do I loop an excel 2010 table by using his name & column reference?
  27. Is there a way to make UDF that gives a description like the native Excel functions have when user clicks the Fx button?
  28. How to improve the speed of VBA macro code?
  29. Delete Hidden/Invisible Rows after Autofilter Excel VBA
  30. How to copy only a single worksheet to another workbook using vba

Leave a Comment