My list looks correct, both in the dropdown and of course in the comma
separated list argument in the data validation dialog.

As GS said, when selected it is interpreted as a date. And as he further
stated, formatting the cell as Text prevented the interpretation as a date.
Just added these statement to reinforce that those solutions work for me
(xl97)

I can add a non-breaking space at the front (chr(160)) and that fixes it as
well - but I assume you want to use it in your formulas. In that case, you
would need to strip out the character 160 with Right(b9,6)

For sht = 1 To Worksheets.Count
sMonths = sMonths & ", " & Chr(160) & Worksheets(sht).Name
Next sht
sMonths = Right(sMonths, Len(sMonths) - 2)


--
Regards,
Tom Ogilvy





"Alex" wrote in message
...
Tom

Thanks for the reponse.

What you have offered is better than what I had...but not quite correct.

If I have five worksheets named: Jan 06 Feb 06 Mar 06 Apr 06 May 06 and I
run your code then then the 'source' in data validation appears as:

6-Jan, 6-Feb, 6-Mar, 6-Apr, 6-May

If I format the actual cell B9 to custommmm-yy then this diaplys
correctly
i.e. Jan 06, Feb 06 etc. but the list in the drop down is still of the
format
6-Jan, 6-Feb etc.

I checked your code and tried debug.print sMonths and that is correct i.e.
it gives:

Jan 06, Feb 06, Mar 06, Apr 06, May 06

...so I assume there is somne issue with how Excel is interpreting that
string within data validation.

Can you think of how we might change this so that the drop down list shows
Jan 06, Feb 06 etc.

Many thanks again...


Alex

"Tom Ogilvy" wrote:

This works for me.

If you still need the Name months, then put that code back in as well:

Private Sub Workbook_Open()
Dim sht As Long
Dim sMonths As String

For sht = 1 To Worksheets.Count
sMonths = sMonths & ", " & Worksheets(sht).Name
Next sht
sMonths = Right(sMonths, Len(sMonths) - 2)
With ThisWorkbook.Worksheets(1).Range("B9").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=sMonths
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

--
Regards,
Tom Ogilvy


"Alex" wrote:

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