MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to add a dynamic array to in-cell drop down menu?

  Asked By: Carolina    Date: Feb 23    Category: MS Office    Views: 1137

I am trying to get in-cell drop down menu having a content of a
dynamic array (or Name).

The issue is that Pull down Menu - Data -> Validation is not helping
me to define a Name which is refered to a Dynamic Array.

Following is my code, where I am trying to get all available sheet in
a workbook and trying to store the name of each sheet in an array
named "sheetnamearray". After that I am assigning this array to a Name

Public Sub sheetname()

Dim sheetnamearray(1 To 10) As String
Dim myCount, NumShts As Integer

NumShts = ActiveWorkbook.Worksheets.Count

' ReDim sheetnamearray(1 To NumShts)

For myCount = 1 To NumShts
sheetnamearray(myCount) = ActiveWorkbook.Sheets(myCount).Name
Next myCount

Names.Add Name:="allsheetname", RefersTo:=sheetnamearray
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=allsheetname"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


Problem: If now I use this "allsheetname" to define a Pop down list,
Excel cribbs and does not allow me to do that...

I am not able to understand why, as normally any name should be OK for it.

Do I need to take specific step by which I can assign a name with
array to in-cell drop down menu??



4 Answers Found

Answer #1    Answered By: Betty Fischer     Answered On: Feb 23

My guess is that it is not a range but an array. That's why  xl doesn't allow
it. Maybe make a range on a hidden sheet?

Answer #2    Answered By: Mabel Davis     Answered On: Feb 23

Doesn't data  Validation require that the range is on the same sheet  as
the data validated cells? I haven't done this for a while, but I seem to
recall the workaround was to enter this as the validation:


Answer #3    Answered By: Ernesto Robinson     Answered On: Feb 23

I am able to resolve this problem by using comma separated string
instead of array.
But I think I have hit a bottleneck which is that the xlValidation
does not take anything more than 16 items in the drop  down menu..

Can I overcome this limitation by any means??

Answer #4    Answered By: Marion Hayes     Answered On: Feb 23

You may find this link helpful as well

Didn't find what you were looking for? Find more on How to add a dynamic array to in-cell drop down menu? Or get search suggestion and latest updates.