Excel Validation Lists

[ClulessChris]

The following code runs great when I step through.

 sListRange = "=$AJ$" & X & ":" & "=$AZ$" & X 

        With Range("A" & X).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=sListRange
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With

However when set it to run, I get error:
-2147417848
Method 'Add' of object 'Validation' failed

On Line

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=sListRange

Am I missing something?

Everybody is somebodys Nutter.

 

[combo]

Delete "=" in:
sListRange = "=$AJ$" & X & ":" & "[!]=[/!]$AZ$" & X

combo

 

[ClulessChris]

Combo
Thanks for your responce. That was a mis type of mine. that line acualy reads:

sListRange = "=$AJ$" & X & ":" & "$AZ$" & X

As I say the starnge thing is that it works when steping through, but crashes in runtime.

Everybody is somebodys Nutter.

 

[combo]

Try to add proper worksheet reference to the range:
With Worksheets(IndexOrName).Range("A" & X).Validation

combo

 

[SkipVought]

Hi,

Consider a different way to define ranges...

        sListRange = Range(Cells(X, "AJ"), Cells(X, "AZ"))"=$AJ$" 
        With Cells(X, "A").Validation
            .Delete
            .Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:=[b]"=" & [/b]sListRange
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With

If you were to use a NAMED RANGE...

        With Cells(X, "A").Validation
            .Delete
            .Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:="MyListRangeName"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Rofeu]

Your code works fine on my machine. Maybe the cell your trying to put the validation on is protected?

Also, the operator is redundant with xlValidateList

Cheers,

Roel

 

[ClulessChris]

combo

If I use:

sListRange = Worksheets("Sheet1").Range("AJ" & X) & ":" & _
Worksheets("Sheet1").Range("AZ" & X)

then sListRange is filled with the values of these ranges.

Everybody is somebodys Nutter.

 

[ClulessChris]

Thanks all for your quick responce.
Skip,
I can't get this to work at all.

Rofeu,
It's a strange one, no protected cells. And it works when stepped.

I think I'll walk away for 30 min and come back to this one.

Once again I apprisiate the input.

Everybody is somebodys Nutter.

 

[combo]

Leave the sListRange as it was - this is a string. Apply worksheet reference in 'With' statement.

combo

 

[SkipVought]

How did you name your range?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ClulessChris]

Ok, Here is the bulk of the proc as is:

    For X = 3 To 65536
'        Create array of possible pids for name given
        sPossPids = Split(AskAd4PID(Range("C" & X), Range("B" & X)), ";")

        Z = 36  ' start at 36'th Col ("AJ") and create list PIDs
        For Y = LBound(sPossPids) To UBound(sPossPids) - 1
            If UBound(sPossPids) = 1 Then
                Cells(X, 1) = sPossPids(0)
                GoTo skip
            Else
                Cells(X, Z) = sPossPids(Y)
                sListRange = Cells(X, Z).Address
                Z = Z + 1
            End If

        Next

        sListRange = "=$AJS" & X & ":" & sListRange

        With Range("A" & X).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=sListRange
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
        
        sListRange = ""
skip:
    Next

AskAd4PID is a function to query AD and return a CSV list of user ID's from a given name (e.g. Dave Jones returns 123456;654321;987654;852369

combo,
are suggesting that I start the with:
With Worksheets("Sheet1").Range("A" & X).Validation
or a somehow apply a worksheet reference in the add statement?

Everybody is somebodys Nutter.

 

[ClulessChris]

Ok, I've now found that I can put the delimited list into the add method so I've cut much of the redundant code:

    For X = 3 To 65536
        sPossPids = AskAd4PID(Range("C" & X), Range("B" & X))
        
        If InStr(Left(sPossPids, Len(sPossPids) - 1), ",") = 0 Then
            Cells(X, 1) = Left(sPossPids, Len(sPossPids) - 1)
            
        Else
            With Range("A" & X).Validation
                .Delete
                .Add _
                    Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:=Left(sPossPids, Len(sPossPids) - 1)
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputMessage = "Please select a PID from the list."
                .ErrorMessage = "You Must select a PID from the list"
                .ShowInput = True
                .ShowError = True
            End With
        
        End If
    Next

however the issue remains that it'll work if I step through, but crashes in run time.

Everybody is somebodys Nutter.

 

[ClulessChris]

Sorry to keep chiping in here, however. I've found this

http://support.microsoft.com/default.aspx/kb/270589

that relates to this error. This suggests that the error is due to late binding, I don't see how this can be applied to this issue.
Am I wrong?

Everybody is somebodys Nutter.

 

[ClulessChris]

Sorry It's me again.
Please disregard my last stupidness.

Range("A" & X).Activate
With Selection.Validation

Solves the issue.

Everybody is somebodys Nutter.