[Cell Validation on the fly] Dynamically Add Data Validation to a Column of Cells #VBA #Excel

AddValidation.bas

Sub AddValidation(cell as range)
'designed to work in tandem with worksheet function (see next)
If cell.rows.count>1 or cell.columns.count>1 then exit sub

With cell.validation
	.delete
    .add Type:=xlValidateList, AlertStyle:=xlValidateAlertStop, Operator: _
    	xlBetween, Formula:="A,list,of,your,values"
    .ignoreblank = True
    .incelldropdown = true
end with

end sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim v as integer
v=0
If not intersect(Target,Range("A;A")) is nothing then 'apply to your column
	on error resume next
    v = target.specialcells(xlcelltypesamevalidation).count
    on error goto 0
    If v= 0 then
    	call AddValidation(target)
    end if
end if
end sub