Using the Excel.Validation Variable

[medberg]

Hello,

I have a cell that I want to get the Validation information for. Basicailly, I declare a variable like this:

Dim validation As Excel.validation

Now, I want to set validation equal to something. I was thinking something along the lines of this:

validation = Sheet1.Cells(row, column).validation

This does not work at all. Does anyone know the correct syntax to get this working? Thanks!

 

[Jerry Sullivan]

The macro recorder can be really helpful for cases like this where you know how to do something manually, but you don't know the VBA syntax.

Start by recording a macro like this...

Sub Macro2()
'
' Macro2 Macro
'

'    
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="1", Formula2:="10"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Guess"
        .ErrorTitle = "Error"
        .InputMessage = "Pick a number between 1 and 10"
        .ErrorMessage = "Your number is not between 1 and 10"
        .ShowInput = True
        .ShowError = True
    End With
End Sub

You can them make the code more flexible for your purpose....

Sub Add_Validation()
    Dim lMax As Long, lMin As Long
    lMin = 10
    lMax = 100
    With Sheets("Sheet1").Range("A1:E1").Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=lMin, Formula2:=lMax
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Guess"
        .ErrorTitle = "Error"
        .InputMessage = "Pick a number between " & lMin & " and " & lMax
        .ErrorMessage = "Your number is not between " & lMin & " and " & lMax
        .ShowInput = True
        .ShowError = True
    End With
End Sub