Change to Month Sheet name

[mdmackillop]

I'm using a routine from Zack for a data validation sheet list. Is there a way to prevent it adding the dash into the validation list? Why does it do this? I can do a workaround, so no big issue, just a frustration!

[hardlife]

Sub pokus()

MsgBox IsDate("1/" & "Jan 2009") 'True
MsgBox IsDate("1" & "Jan 2009") 'False

End Sub

http://www.oraxcel.com/projects/litlib/help/isdate.html

Syntax:
=IsDate(expression)

Description:
Returns TRUE is the expression is a date and FALSE otherwise.

Example:
=IsDate("01/01/2006")
=IsDate("01/Jan")
=IsDate("First of January, 2006")

=IsDate("01/January")
=IsDate("1/January 2006")

[mdmackillop]

Thanks for the reply. IsDate is just a check to avoid Sheet1 & Sheet 2 being added to the list. The issue is "Jan-2009" instead of "Jan 2009"

[hardlife]

Sub MonthList()
Dim wb As Workbook, i As Long, sList As String, rDV As Range
Dim tgt As String
Set wb = ThisWorkbook
For i = 1 To wb.Worksheets.Count
If IsDate("1/ " & wb.Worksheets(i).Name) Then
sList = sList & wb.Worksheets(i).Name & ","
End If
Next i
sList = Left(sList, Len(sList) - 1)
Set rDV = Range("C7")
Application.EnableEvents = False
rDV.Validation.Delete
rDV.Validation.Add Type:=xlValidateList, Formula1:=sList
rDV.Validation.InCellDropdown = True
rDV.Validation.IgnoreBlank = False

End Sub

[Bob Phillips]

I can't see what you can do about it Malcolm, Excel inserts it even if you enter it manually. It seems to object to a date like string with a space in. Very odd.

[GTO]

Hi Fellas,

For about a minute I was all sorts of thrilled w/myself for making a possible "discovery", but alas, the best it turned out to be was a hopefully acceptable workaround.

When I ran Malcom's code excepting:
[vba]
'...
sList = sList & " " 'Left(sList, Len(sList) - 1)
Set rDV = Range("C7")
' Application.EnableEvents = False
...
[/vba]

Well, leastwise in 2000 (albeit the sickly laptop), the Validation List came up with the three months, but did not add a blank entry as I expected! (This was of course the, "Hey, look what I just did!" moment.)

Short-lived nirvana, as four or more month sheets results in the blank entry being added to the end. But hey, at least the stupid hyphens are gone :-)

Anyways, my thinking was that if we could make 'one thing not like the others' in the list, Excel might become less presuming... Was this the workaround you had already thought of Malcom?

Mark

[mdmackillop]

Hi Mark,
Not quite following you there.
Using this tweak shows it's in the change to the list that the problem arises
[VBA]
Range("C1") = sList
rDV.Validation.Add xlValidateList, Formula1:=Range("C1")
[/VBA]
Regards the workaround, I just reformat the item selected the same as the sheet name to create a variable.

[GTO]

Sorry Malcom, saw you on, started to answer, IE6 said Adios for a bit(Damn laptop)... Anyways, when I said "...excepting:", I didn't mean excepting as in deleting, I meant that I changed sList.

As noted, this (discluding quote marks included to show blank space):
"Jan 2009,Feb 2009,Mar 2009"
gets converted to:
"Jan-2009,Feb-2009,Mar-2009"
but this (least for me) does not get converted:
"Jan 2009,Feb 2009,Mar 2009, "
...it just leaves a blank row at the bottom of the drop-down list.

This is exactly what I tried with your attached WB at #1:
[vba]
Sub MonthList()
Dim wb As Workbook, i As Long, sList As String, rDV As Range
Dim tgt As String

Set wb = ThisWorkbook
For i = 1 To wb.Worksheets.Count
If IsDate("1 " & wb.Worksheets(i).Name) Then
sList = sList & wb.Worksheets(i).Name & ","
End If
Next i
sList = sList & " " '<---Leave the comma and add a space
Set rDV = Range("C7")
' Application.EnableEvents = False '<---"remmed" (In addition to speaking "Marklish"
' or "Markeese", I occassionally
' make up words...sorry)
rDV.Validation.Delete
rDV.Validation.Add Type:=xlValidateList, Formula1:=sList
rDV.Validation.InCellDropdown = True
rDV.Validation.IgnoreBlank = False
End Sub
[/vba]

MY RESULTS (xl2000):
When run, what I get (with the orig sheets) in the drop down are:

Jan 2009
Feb 2009
Mar 2009

The drop-down has just those three entries in it, no hyphens, and (surprisingly) there is not a blank ending row.

Now if I add a sheet named "Apr 2009", I get a drop-down with:

Jan 2009
Feb 2009
Mar 2009
Apr 2009
<---Sorry, no pic, but there is a blank ending row now...

The drop-down now has the four months, still no hyphens (yea!), but does include a blank row at the bottom.

Further testing indicates that if there are exactly three months (four entries including the blank row), the drop-down shows no blank row. But if there are less than, or more than three months, the ending blank row is visible. For my own curiosity if nothing else, I'll try and remember to test this in 2003 tomorrow (It's Sun night here).

Well, hopefully that was a bit better. Do you get same results?

Thank you,

Mark

[mdmackillop]

Hi Mark,
In 2007 that works perfectly. No blank rows in the drop-down with any number of sheets (and I don't have Ignore Blanks ticked).
A very interesting technique!
Regards
Malcolm

Ps
Thanks Hardlife,
Some things were simpler in Excel 97 when the programme was less "intelligent". Now we end up with processes taken out of our hands.

[Zack Barresse]

Or you can take the space out and put a dash (inside the IsDate() function).

[mdmackillop]

Hi Zack,
The problem was using the dropdown to use in sh = sheets(Range("C6")). The dash got in the way.

[Zack Barresse]

Ah, gotcha!

[GTO]

I am very glad that worked for ya Malcom.


Just info as I said I'd check... 2003 appears the same as 2000, but this would seem harmless unless the user picks the blank row I suppose...

Mark