PDA

View Full Version : Evaluate Cell --- for Data validation Drop down list



asingh
09-01-2008, 07:46 PM
Hi,

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


regards,
asingh

david000
09-02-2008, 01:05 AM
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

Bob Phillips
09-02-2008, 02:23 AM
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

Bob Phillips
09-02-2008, 02:25 AM
Sorry, missed the bit about only List



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

asingh
09-03-2008, 08:36 AM
thanks a lot...will give it a try..!