EXCEL: Load cell Data Validation list from array??? 1

[VBAjedi]

Hi all!

I have a list of text values that contain commas (city, state). They are contained in an array named UniqueValues(). I need to set the data validation for a cell to refer to this array. Because my data contains commas, I can't just build a comma-seperated string like I normally would. I tried the following code, but it didn't work:

MyCell.Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=UniqueValues()

I could write the values to a range of cells and refer to the range, but I don't want to do that.

Any suggestions?

Thanks!
VBAjedi

 

[nath]

Hi there,

You can use the type xlValidateCustom
and for formula1 a "or" statement that lists your possibilities. something like:

    With Range("A1").Validation
    .Add Type:=xlValidateCustom, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=OR(A1=""Paris, France"",A1=""Bruxelles, Belgique"")" 
    .InputMessage = "Bruxelles, Belgique" & Chr(10) & "Paris, France"
    .ErrorMessage = "You must enter ""Bruxelles, Belgique"" or ""Paris, France"""
    End With

But it doesn't give the user the possibility to select the content of the list which is probably a big problem for you as well as it won't be fun for the user to type everything.

Let me know how you are getting on,

Nath

 

[VBAjedi]

Nath,

Thanks for your suggestion. As you guessed, however, the drawbacks to that approach are a problem for me.

I ended up writing a custom data validation routine. When the user selects a cell a combobox appears, sized to match the cell and populated with the values I want there. Then the combobox's Click event triggers code to put the correct value in the cell and hide the combobox.

Thanks again! If anyone out there knows the answer to my original question, I'd still be interested in hearing it!


VBAjedi

 

[xlbo]

Unfortunately, I think the only way round this is using a range to hold the data (which you don't want)
The problem is that the validation metod uses a similar process to text file conversion - it reads the list as comma seperated values and transposes it so that even if a comma is a string on it's own, it is still a seperator, You can even get lists that look like London,,,,,,,Dublin,,,,,,,,Edinburgh etc etc
The main issue is that validation doesn't look for 1 comma seperating values - it looks for ANY comma to seperate values

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[VBAjedi]

Thx for the info, Geoff! I had hoped that because I could use values containing commas if they were stored in a range, and a range is essentially an array, that there was a way. . .

Is there any way to set up a "pseudo-range" that doesn't physically exist on a worksheet but can be referred to by Excel in formulas, etc?


VBAjedi

 

[xlbo]

Kinda - that's how dynamic range names work but I don't think it'd work in this instance - the problem is that within a range, validation uses whatever is in the cell as discreet info ie it DOESN'T use a comma seperated list and the only way to make it behave like that is to reference a range and the only way to reference a rnage in code is to have a range....bit of a ctach 22
Do you mind me asking why you don't want to commit the values to a range ??? surely a named range on a seperate sheet would be ok ???

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[VBAjedi]

Well, big picture first: I'm developing a cool little advanced filter app. I have three worksheets full of large amounts of data. The data worksheets have up to twenty columns in common, but the order of the columns is different and some columns are unique to one worksheet.

So, I set up a fourth worksheet ("Summary Sheet&quot with a header section representing the twenty common columns. In the row underneath, I am putting drop-down lists of all unique values found in those three worksheets (thus my custom data validation routine). These lists are dynamically refreshed every time a user selects one of these cells. I use these values to simultaneously apply an advanced filter to all three data sheets (I bounce the filter values to the columns they need to be in on each data sheet), and then I return summary information about the filtered data from all three sheets to my "Summary Sheet"

Now, to answer your question: I don't really want to deal with a hidden worksheet containing twenty columns of values, or with updating named ranges or counting hundreds of values each time my data sheets change.

Clear as mud? LOL

It's coming together pretty well, but I'm still open to any suggestions!


VBAjedi

 

[xlbo]

heh heh heh - sounds like a right little doozy
'Fraid you may be stuck with your solution unless yuo can use something other than a comma to seperate your City/Country field - for any field that doesn't have commas in it, you shouldn't have too much of a problem although you will have to transform the array to a comma seperated string

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[VBAjedi]

Thanks Geoff! A star for your musings - they helped give me peace of mind that I was using the best available method.


VBAjedi