Applying Validation

ryangus · Jul 6th 2017

I'm trying to apply validation using a dynamic list from sheet4 to a range on sheet10, but the following code fails at the ".Add" line? I suspect it might be looking for a string not a range. Anyone able to explain why, and how to fix it?

Code

Sub DataValidation()
    Dim rList As Range
    Set rList = Sheet4.Range("C2", Sheet4.Range("C" & Rows.Count).End(xlUp))
    
    With Sheet10.Range("x7:x12")
        With .Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & rList
        End With
    End With
End Sub

Display More

**Carim** · Jul 6th 2017

Re: Applying Validation

Hello,

You should make a tiny modification ...

Code

Formula1:="=" & rList.Address

Hope this will help

ryangus · Jul 6th 2017

Re: Applying Validation

Quote from Carim;795404
Hello,

You should make a tiny modification ...
Code
Formula1:="=" & rList.Address
Hope this will help
Display More

Thanks, worked perfectly!

**Carim** · Jul 6th 2017

Re: Applying Validation

Glad you could fix your problem ...:wink:

Thanks ... for your Thanks ...:smile:

ryangus · Jul 6th 2017

Re: Applying Validation

Well, this is strange. I successfully tested the code last night in isolation using Excel 2016. This morning, the exact same code is falling over in Excel 2010.

**KjBox** · Jul 7th 2017

Re: Applying Validation

This worked perfectly for me with Excel 2010

Code

Sub DataValidation()
    Dim rList As Range
    Set rList = Sheet4.Range("C2", Sheet4.Range("C" & Rows.Count).End(xlUp))
     
    With Sheet10.Range("x7:x12")
        With .Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & rList.Address
        End With
    End With
End Sub

Display More

Make sure the file you are using has the 2 required sheets with code names Sheet4 and Sheet10, or change the code to suit your actual sheet code names.

ryangus · Jul 7th 2017

Re: Applying Validation

I worked it out. It was failing because there was already validation in place. The following code fixed it:

Code

Sub DataValidation()
    Dim rList As Range
    Set rList = Sheet4.Range("C2", Sheet4.Range("C" & Rows.Count).End(xlUp))
     
    With Sheet10.Range("x7:x12")
        With .Validation
          [COLOR=#ff0000]  .Delete[/COLOR]
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & rList.Address
        End With
    End With
End Sub

Display More

**KjBox** · Jul 7th 2017

Re: Applying Validation

ryangus · Jul 7th 2017

Re: Applying Validation

Made a change in the code to ensure it grabs any new values for the list:

Code

Sub DataValidationProgramManager2()
    Dim rList As Range
    Set rList = Sheet4.Range("$D$2", Sheet4.Range("$D$" & Rows.Count).End(xlUp))
     
    With Sheet10.Range("x7", Sheet10.Range("x" & Rows.Count).End(xlUp))
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & rList.Address
        End With
    End With
End Sub

Display More

Unfortunately, it's not grabbing the correct values (I think it's grabbing the values from Sheet10 where the code is run from). Then I have a brainwavem and decided to use a dynaic named range for the list values:

Code

Sub DataValidationProgramManager2()
    Dim rList As Range
    Set rList = Sheet4.Range("$D$2", Sheet4.Range("$D$" & Rows.Count).End(xlUp))
     
    With Sheet10.Range("x7", Sheet10.Range("x" & Rows.Count).End(xlUp))
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & rList.Address
        End With
    End With
End Sub

Display More

This works well, except for some reason the named range is including a blank value at the bottom, and as validation works, of there's a blank it automatically goes to this when the drop-down list is used.

So... any ideas how to ensure the dynamic named range does not include blanks, and/or why the dynamic range in the first pice of code isn't grabbing the correct values?

ryangus · Jul 7th 2017

Re: Applying Validation

Answered my own question regarding the dynamic named range solution. I was using row 2 as the start of the dynamic named range, when I changed it to row 1, it stopped populating a blank.

I'm still curious why the dynamic range code doesn't work...

**Carim** · Jul 7th 2017

Re: Applying Validation

Quote from ryangus;795466

I'm still curious why the dynamic range code doesn't work...

Hello again,

Would you mind posting the formula you are using for your dynamic named range ... :wink:

ryangus · Jul 10th 2017

Re: Applying Validation

Here you go:

Code

=OFFSET(Lists!$T$1,0,0,MATCH("*",Lists!$T:$T,-1),1)

used like this:

Code

Sub ValList()
    Dim rList As String
    rList = "ValidationList"
     
    With Sheet10.Range("AE7", Sheet10.Range("AE" & Rows.Count).End(xlUp))
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & rList
        End With
    End With
End Sub

Display More

**Carim** · Jul 11th 2017

Re: Applying Validation

Hello,

Your Data Validation Offset formula is fine ...

In order to use it in your macro ...

Code

Sub ValList() 
    Dim rList As Range 
    Set rList = Range("ValidationList") 
     
    With Sheet10.Range("AE7", Sheet10.Range("AE" & Rows.Count).End(xlUp)) 
        With .Validation 
            .Delete 
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
            xlBetween, Formula1:="=" & rList.Address 
        End With 
    End With 
End Sub

Display More

Hope this will help

Applying Validation

Participate now!

Share