Home » Blogs » Vishesh's blog

Excel VBA Data Validation Code

Vishesh's picture
Submitted by Vishesh on 27 March, 2010 - 04:05

 

This is a generalised code for Excel VBA Data Validation. It can be used across sheets in a workbook.

 

Sub createValidation(rngValidation As Range, rngReference As Range)
 
    rngValidation.Validation.Delete
 
    rngValidation.ClearContents
 
    Dim strRefRange As String
 
    strRefRange = "=indirect(" & """" & "'" & rngReference.Parent.Name & "'!" & rngReference.Address & """" & ")"
 
    With rngValidation.Validation
 
         .Delete
 
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
 
         xlBetween, Formula1:=strRefRange
 
         .IgnoreBlank = True
 
         .InCellDropdown = True
 
         .InputTitle = ""
 
         .ErrorTitle = ""
 
         .InputMessage = ""
 
         .ErrorMessage = ""
 
         .ShowInput = True
 
         .ShowError = True
 
     End With
 
End Sub
 
 
»