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
"allsheetname".

'''''''''''''''''''''''''
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
.Delete
.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??