Evaluate Cell --- for Data validation Drop down list

[asingh]

Hi,

How can I inspect a cell to check if it has a Data Validation List in it...???


regards,
asingh

[david000]

[vba]Option Explicit
Option Base 1
Sub ValList()
Dim wb As Workbook
Dim wks As Worksheet
Dim ValArray As Variant
Dim i As Integer
Dim rng As Range
Set wb = ActiveWorkbook
Set wks = wb.ActiveSheet
With wks
On Error GoTo Msg
ValArray = Array("Whole Number", "Decimal", "list", "Date", "Time", "Text Length", "Custom")
Set rng = Union(.Cells.SpecialCells(xlCellTypeAllValidation), .Cells.SpecialCells(xlCellTypeAllValidation))
With rng
If .Areas.Count < 1 Then
MsgBox ValArray(.Validation.Type) & vbNewLine & rng.Address
Else
For i = 1 To rng.Areas.Count
MsgBox ValArray(.Areas(i).Validation.Type) & vbNewLine & rng.Areas(i).Address
Next i
End If
End With
End With
Exit Sub
Msg:
MsgBox Err.Description
End Sub

[/vba]

[Bob Phillips]

[vba]
Dim rng As Range

On Error Resume Next
Set rng = Intersect(ActiveCell, ActiveCell.SpecialCells(xlCellTypeAllValidation))
On Error GoTo 0
If Not rng Is Nothing Then

MsgBox "activecell has validation"
End If
[/vba]

[Bob Phillips]

Sorry, missed the bit about only List

[vba]

Dim rng As Range

On Error Resume Next
Set rng = Intersect(ActiveCell, ActiveCell.SpecialCells(xlCellTypeAllValidation))
On Error GoTo 0
If Not rng Is Nothing Then

If rng.Validation.Type = xlValidateList Then

MsgBox "activecell has validation list"
End If
End If
[/vba]

[asingh]

thanks a lot...will give it a try..!