Problem with creating a named range

[Alex]

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

 

[Jon Peltier]

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/
_______

 

[Tom Ogilvy]

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

 

[GS]

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

 

[Alex]

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

 

[GS]

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

 

[Tom Ogilvy]

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

 

[GS]

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

 

[Alex]

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]

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

 

[Alex]

Garry

Thanks for taking the time to reply.

I have managed to replicate your code and all works fine now.

Many thanks

Alex

 

[GS]

Alex,

You're welcome! -I'm glad to help.

As for the code, your thanks should go to Tom since it's his contribution.

Regards,
Garry