ApplyValidation_DropDown

Adds Validation drop-down to a cell (Data --> Validation --> List), or to a Selection.
List of items in list is a string with items separated by commas, like "Champio,Analyst,None,Both"
Applies to any cell in any sheet in open workbooks

Function ApplyValidation_DropDown(CellAddress, ValidationListString, Optional WB = "This", Optional She = "Active")
 ' Adds Drop-down validation, giving list in string separated with commas
 If WB = "This" Then WB = ThisWorkbook.Name
 If She = "Active" Then She = Workbooks(WB).ActiveSheet.Name
 With Workbooks(WB).Worksheets(She).Range(CellAddress).Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
   xlBetween, Formula1:=ValidationListString
  ' list is as "Champio,Analyst,None,Both"
  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = ""
  .ErrorTitle = ""
  .InputMessage = ""
  .ErrorMessage = ""
  .ShowInput = True
  .ShowError = True
 End With
End Function

' Original call
' With Selection.Validation
' .Delete
' .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
' xlBetween, Formula1:="=pays"
' .IgnoreBlank = True
' .InCellDropdown = True
' .InputTitle = ""
' .ErrorTitle = ""
' .InputMessage = ""
' .ErrorMessage = ""
' .ShowInput = True
' .ShowError = True
' End With

Parameters

CellAddress, ValidationListString, Optional WB, Optional She

Views 47 Downloads 16

Perm link