Problem with creating a named range

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 Insert>Name>Define
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

I couldn't make DV work with a literal array, either.

Dump the array into a worksheet range, and name the range "Months", and DV
should happily accept that.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Alex" <Alex@discussions.microsoft.com> wrote in message
news:6CC96B52-6033-45B7-8E85-711EAEEF4D4B@microsoft.com...
> 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
> Insert>Name>Define
> 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

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 Insert>Name>Define
> 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

Hi Tom,

You might want to mention that after selecting a sheetname from the list "as
listed", Excel will treat it as an actual date. If the user doesn't want that
but wants it to appear exactly "as listed", the cell(s) using this DV should
be formatted as TEXT.

Regards,
Garry

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 custom>mmm-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 Insert>Name>Define
> > 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

Hi Alex,

If you check my response to Tom's post you'll find an answer. My DV Source
box looks exactly like sMonths (just the sheetnames, separated by commas).

ie.: Jan 06, Feb 06, Mar 06, Apr 06, May 06

Regards,
Garry

I failed to mention that my DV drop list looks the same as the DV Source box
entry.

ie:
Jan 06
Feb 06
Mar 06
Apr 06
May 06

...and the data appears that way in the cell after selection. Formatting the
cell as TEXT will correct how it appears in the DV drop list, and prevent
Excel from interpreting it as a date.

HTH
Garry

GS

I am sure that I am missing the point here but I cannot make work what you
have suggested.

As I understand it you are saying that if I format the cell on the worksheet
where I am using DV (e.g. cell B9) as Text then this will solve the issue
that I am having.

I tried this but it doesn't work for me.

(A) The selected date from the list appears as 06-Jan, as an example.
(B) The drop down list still represents dates as 06-Jan, 06-Feb etc.
(c) The source in DV is still showing as 06-Jan, 06-Feb etc.

I tried Tom's option of using Chr(160) and this works fine. I am still
curious though about the solution that you are offering as I feel that I have
completely missed the point...

Regards


Alex

"GS" wrote:

> Hi Alex,
>
> If you check my response to Tom's post you'll find an answer. My DV Source
> box looks exactly like sMonths (just the sheetnames, separated by commas).
>
> ie.: Jan 06, Feb 06, Mar 06, Apr 06, May 06
>
> Regards,
> Garry

Hi Alex,

Sorry for any confusion! In rereading my post I see that I caused the
confusion!

Formatting the cell containing the DV to TEXT prevents Excel from treating
it like a date. (That's how I meant the line should have read) Formatting the
cell doesn't affect the DV list, only how its items appear in the cell.

As for the solution about the DV Source box contents, I (instinctively)
removed the line of code that strips out the leading ", " from Tom's 1st
example. The Source box is a RefEdit control and therefore behaves
accordingly. Entering Jan 06 and so on causes it to evaluate to data type
"date". I ignored the leading ", " when explaining how my list 'appeared' in
the DV Source box, as opposed to the way Excel altered it after treating the
entry as dates. I should have expanded on that right then, and I apologize
for not doing so.

So, my DV Source box shows this:

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

and my cell drop list shows this:

Jan 06
Feb 06
Mar 06
Apr 06
May 06

without any leading spaces.

Tom's 2nd solution handles the appearance in the DV Source box by adding a
leading space to each sheetname followed by a soluton for removing it if
necessary to use the contents of the cell for reference or value. This is
perfectly acceptable. I just prefer to not put the extra spaces there
initially because then I don't have to worry about that for things like
?LOOKUP() or MATCH(), etcedera.

I treat the leading ", " in the DV Source box as I would using an apostrophe
in front of a cell entry for numbers as text. It serves the same purpose in
concept. The fact that it's visible in the formula doesn't bother me.

HTH
Garry

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" <Alex@discussions.microsoft.com> wrote in message
news:4C6C7545-FA2A-4BA0-B4C0-6E6D0593EB0C@microsoft.com...
> 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 custom>mmm-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
Insert>Name>Define
> > > 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