Data validation (list) from name of array constant

[kdw]

I want to add an array constant as name.
Then I wish to use that name in data validation list.
Does anyone have solution for this?

 

[Jaafar Tribak]

Can you use vba ? If so, see if this routine works for you :

In a Standard Module:

Public Sub NameToValList( _
    ByVal ArrayConstant_Name As Name, _
    ByVal ValidationList_TargetRange As Range _
)

    Dim vArr() As Variant
    Dim i As Long, lArrayItemsCount As Long
    
    lArrayItemsCount = UBound(Split(ArrayConstant_Name.Value, ",")) + 1&
    For i = 1& To lArrayItemsCount
        ReDim Preserve vArr(i - 1&)
        vArr(i - 1&) = Trim(Replace(Replace(Replace(Replace(Split(ArrayConstant_Name.Value, ",") _
                       (i - 1&), Chr(34&), ""), "=", ""), "{", ""), "}", ""))
    Next i
    With ValidationList_TargetRange.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(vArr, ",")
    End With

End Sub

Usage:
NameToValList Application.Names("TYPE_YOUR_ARRAY_CONSTANT_NAME_HERE"), Range("TYPE_YOUR_DATAVALIDATION_RANGE_ADDRESS_HERE")

An example :

NameToValList Application.Names("Products"), Range("B2")

Where Products is the name of the array constant Name and B2 is the cell to add the validation list to... Adapt as required.

 

[kdw]

Can you use vba ? If so, see if this routine works for you :

In a Standard Module:

Public Sub NameToValList( _
    ByVal ArrayConstant_Name As Name, _
    ByVal ValidationList_TargetRange As Range _
)

    Dim vArr() As Variant
    Dim i As Long, lArrayItemsCount As Long
   
    lArrayItemsCount = UBound(Split(ArrayConstant_Name.Value, ",")) + 1&
    For i = 1& To lArrayItemsCount
        ReDim Preserve vArr(i - 1&)
        vArr(i - 1&) = Trim(Replace(Replace(Replace(Replace(Split(ArrayConstant_Name.Value, ",") _
                       (i - 1&), Chr(34&), ""), "=", ""), "{", ""), "}", ""))
    Next i
    With ValidationList_TargetRange.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(vArr, ",")
    End With

End Sub

Usage:
NameToValList Application.Names("TYPE_YOUR_ARRAY_CONSTANT_NAME_HERE"), Range("TYPE_YOUR_DATAVALIDATION_RANGE_ADDRESS_HERE")

An example :

NameToValList Application.Names("Products"), Range("B2")

Where Products is the name of the array constant Name and B2 is the cell to add the validation list to... Adapt as required.

I did not understand how to use it. Like where and how do I create the name?
Like say name of my array const is "Cars". where and how shall I add "BMW","Audi","VW"?

Also, where shall I use this;

NameToValList Application.Names("Products"), Range("B2")

 

[jputman]

Jaafar's VBA "Usage" line assumed that you already had a defined Name with an array in it called "Products".

If you want to create the defined Name in code and then pass it in to his code, you would add something like this:

Public Sub CreateValidationListInActiveCell()
    ' This creates the defined Name "Cars" and adds the 3 values
    ActiveWorkbook.Names.Add "Cars", Array("BMW", "Audi", "VW")
    
    ' This calls Jaafar's code that pulls the values from "Cars"
    ' and adds them to a data validation list in the ActiveCell
    NameToValList Application.Names("Cars"), ActiveCell
End Sub

If you add this code along with Jaafar's code, you will create both the "Cars" defined name with the values you asked for, and then you will be using those same values in a data validation list for the current ActiveCell.

However... the data validation list doesn't actually run off of the defined name. What Jaafar is doing here only takes the values out of the name and uses them for the list. In other words, if you were to update the "Cars" name in the Name Manager and add another car ("Mercedes"), the data validation list would not be updated with the new value until you run Jaafar's code again, because the List is actually just pointing to an array of strings and not the Name.

I came across this because I want to be able to create a defined name and use it directly in a data validation list... and that way any updates made to the defined name would automatically update the list. Seems like you should be able to do it, but I have not found a way yet.

 

[Jaafar Tribak]

@jputman

Thanks for the clarification.

I want to be able to create a defined name and use it directly in a data validation list... and that way any updates made to the defined name would automatically update the list. Seems like you should be able to do it, but I have not found a way yet.

One hacky trick that may work is maybe placing an array fomula in a column (say Col A:A) in a hidden worksheet. The array formula would be like ={TRANSPOSE(Cars)}.

Then in the hidden Worksheet_Calculate event handler, update the validation list as follows:

Option Explicit

Private Sub Worksheet_Calculate()
    'Automatically update the validation list in cell Sheet1!A1.
    NameToValList Application.Names("cars"), Sheet1.Range("A1")
End Sub

Private Sub NameToValList( _
    ByVal ArrayConstant_Name As Name, _
    ByVal ValidationList_TargetRange As Range _
)

    Dim vArr() As Variant
    Dim i As Long, lArrayItemsCount As Long
 
    lArrayItemsCount = UBound(Split(ArrayConstant_Name.Value, ",")) + 1&
    For i = 1& To lArrayItemsCount
        ReDim Preserve vArr(i - 1&)
        vArr(i - 1&) = Trim(Replace(Replace(Replace(Replace(Split(ArrayConstant_Name.Value, ",") _
                       (i - 1&), Chr(34&), ""), "=", ""), "{", ""), "}", ""))
    Next i
    With ValidationList_TargetRange.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(vArr, ",")
    End With

End Sub

Edit: Actually, just adding a non-array =TRANSPOSE(Cars) to a single cell in the hidden sheet should trigger the calculate event and do the job.

 

[kdw]

Jaafar's VBA "Usage" line assumed that you already had a defined Name with an array in it called "Products".

If you want to create the defined Name in code and then pass it in to his code, you would add something like this:

Public Sub CreateValidationListInActiveCell()
    ' This creates the defined Name "Cars" and adds the 3 values
    ActiveWorkbook.Names.Add "Cars", Array("BMW", "Audi", "VW")
   
    ' This calls Jaafar's code that pulls the values from "Cars"
    ' and adds them to a data validation list in the ActiveCell
    NameToValList Application.Names("Cars"), ActiveCell
End Sub

If you add this code along with Jaafar's code, you will create both the "Cars" defined name with the values you asked for, and then you will be using those same values in a data validation list for the current ActiveCell.

However... the data validation list doesn't actually run off of the defined name. What Jaafar is doing here only takes the values out of the name and uses them for the list. In other words, if you were to update the "Cars" name in the Name Manager and add another car ("Mercedes"), the data validation list would not be updated with the new value until you run Jaafar's code again, because the List is actually just pointing to an array of strings and not the Name.

I came across this because I want to be able to create a defined name and use it directly in a data validation list... and that way any updates made to the defined name would automatically update the list. Seems like you should be able to do it, but I have not found a way yet.

I too wanted to do exactly the same...

create a defined name and use it directly in a data validation list... and that way any updates made to the defined name would automatically update the list.

The previous solution needs a separate sheet/ array in vba to be created. But I wanted to define it directly in defined name.
Probably this is not yet possible in excel.

 

[kdw]

Jaafar's VBA "Usage" line assumed that you already had a defined Name with an array in it called "Products".

If you want to create the defined Name in code and then pass it in to his code, you would add something like this:

Public Sub CreateValidationListInActiveCell()
    ' This creates the defined Name "Cars" and adds the 3 values
    ActiveWorkbook.Names.Add "Cars", Array("BMW", "Audi", "VW")
   
    ' This calls Jaafar's code that pulls the values from "Cars"
    ' and adds them to a data validation list in the ActiveCell
    NameToValList Application.Names("Cars"), ActiveCell
End Sub

If you add this code along with Jaafar's code, you will create both the "Cars" defined name with the values you asked for, and then you will be using those same values in a data validation list for the current ActiveCell.

However... the data validation list doesn't actually run off of the defined name. What Jaafar is doing here only takes the values out of the name and uses them for the list. In other words, if you were to update the "Cars" name in the Name Manager and add another car ("Mercedes"), the data validation list would not be updated with the new value until you run Jaafar's code again, because the List is actually just pointing to an array of strings and not the Name.

I came across this because I want to be able to create a defined name and use it directly in a data validation list... and that way any updates made to the defined name would automatically update the list. Seems like you should be able to do it, but I have not found a way yet.

Hey I actually found a way out...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    NameToValList Application.Names("Products"), Range("B2")
End Sub

use it in this was... everytime there is selection change, the function triggers and updates the list.


Thanks a lot @Jaafar Tribak and @jputman !!
You guys are amazing

 

[Jaafar Tribak]

Hey I actually found a way out...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    NameToValList Application.Names("Products"), Range("B2")
End Sub

use it in this was... everytime there is selection change, the function triggers and updates the list.


Thanks a lot @Jaafar Tribak and @jputman !!
You guys are amazing

I would be wary to use the selection_change event as this would run everytime a cell is selected and you will lose the UNDO functionality all the time. This is an undesired side effect.

Doing this via the Worksheet_Calculate event in the hidden sheet as I did in my post#5 will only delete the excel UNDO list less often.

 

[jputman]

I have figured out a way to do this that is *almost* perfect. Seems like Microsoft is about a step away from allowing us to do this.

The only *gotcha* that I don't love is that you have to actually add the Name somewhere in an actual cell... so that is annoying, but if you are able to do so on a hidden sheet, this one could do the trick. This gotcha will prevent me from using this in the way that I was originally intending, but will allow me to still use it in interesting ways.

Best part is that there is no VBA/code required, which is important for my needs. As soon as you update your Defined Name with new values and they are instantly in your dropdown list.

Ok... here are the steps:

1. define your name with the array of values that you want and call it Cars.
2. add the name as a value for a cell somewhere in the Workbook (e.g. add it to cell Sheet1!$A$1 with the formula =Cars)
   Note: If you used commas as the separator for the cars list, the values will 'spread' to the right to neighboring cells. If you use a semicolon they will spread down. This doesn't actually matter when we get to the list in the next step.
3. (Optional step) To keep my data validation clean, I will add an additional step here of creating a second defined name for the cell I just used called CarsList (just set to =Sheet1!$A$1)
4. When you go to setup you cell that you want the Data Validation list in, point it either to the cell from step 2 or the name from step 3... but add a # at the end to tell it to get all values from the 'spread'. This would either look like =Sheet1!$A$1# or =CarsList#

With that done, you'll see the cars in the dropdown list. Change the defined name Cars to add/remove values and they are instantly updated in the dropdown list (as well as the cell that you added the name to... but that may be on a hidden sheet.

Your first thought will probably be... maybe I can just set the Data Validation list to directly access the Defined Name Cars by adding the # after it. It should work that way, but gives you that error when you try to enter it that way... not sure why they haven't added that.

Let me know what you find... I still think there may be some way to do this that we just haven't stumbled upon.