Excel Helper

Here is what I am trying to achieve.

I want a situation that when a workbook is opened a named range is created
that is the name of all the worksheet tabs in that workbook and that this
populates a drop down menu in the worksheets.

To be clear, I have set this up in Workbook_Open:

Private Sub Workbook_Open()
Dim Months()
Dim sht As Long

For sht = 0 To Worksheets.Count - 1
ReDim Preserve Months(0 To Worksheets.Count - 1)
Months(sht) = Worksheets(sht + 1).Name
Next sht

ThisWorkbook.Names.Add Name:="Months", RefersTo:=Months
End Sub

This works fine and when I go into a worksheet and select InsertNameDefine
I see that there is a name 'Months' which refers to the names of the
worksheets (in my workbook they are simple dates) and are shown in braces
i.e.:

={"Jan 06","Feb 06","Mar 06","Apr 06","May 06"}

The problem is that I now want to use Data Validation as a drop down menu
with those worksheet names. I tried:

Data Validation......

with Allow=List and Source=Months but I got an error.

I suppose my question is 'How can I get data validation to work with an
array formula?'.

I know that I could actually write the worksheet names to a worksheet range
e.g. Sheet1!A1:A5 and then set up data validation to reference that range.
This will work...but it seems a bit messy and not as 'elegant' as doing it
direct from the Workbook_Open event.

Any hints or tips welcome...

Regards


Alex Park