Sheet Names Must be MMMM d (only if workday)

D

Don Guillett

Here is a formula that will give you the next day that is not Sat or Sun
=A7+CHOOSE(WEEKDAY(A7),1,1,1,1,1,3,2)
 
P

Peo Sjoblom

I strongly recommend against that, it is not good spreadsheet design to have
a workbook keep changing sheet names all the time and furthermore if you
don't enable macros it won't work at all..

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
D

Debra Farnham

Hi again All

WinXP Excel 97

I have a template where, when opened, I need each worksheet in the book to
be named according to the date using MMMM d format but ONLY if the days are
workdays. (i.e. Sheet1 would be renamed June 1, Sheet2 would be renamed June
2, Sheet3 would be renamed June 3 etc. but only if June 1, June 2 and June 3
are workdays.) At present, when the workbook opens, I have the users
manually entering the first workday of the month into an input box which is
then programatically placed in Cell A1 which I figured would be a good
starting reference.

Any suggestions?

Thank you

Debra
 
D

Debra Farnham

Thanks Don ... but how do I now get those dates on the names of my
worksheets?

Debra
 
D

Debra Farnham

Thanks for your input Peo but the sheets will only change their name once a
month (when a new book is created based on this template).

Macros are enabled on user's PC's that will be utilizing this template.

Any solutions anyone??

Thanks

Debra
 
D

Dick Kusleika

Debra

Try this in your Workbook's open event

Private Sub Workbook_Open()

Dim i As Long
Dim ws As Worksheet
Dim dFirstDay As Date
Dim dLastDay As Date

'Set this to the name of the first worksheet
'in your template. It insures that this sub
'won't be run twice
Const sDEFAULTSHEET As String = "Sheet1"

'Identify the first and last days
dFirstDay = DateSerial(Year(Now), Month(Now), 1)
dLastDay = DateSerial(Year(Now), Month(Now) + 1, 0)
Set ws = Me.Worksheets(1)

If Me.Sheets(1).Name = sDEFAULTSHEET Then
For i = dFirstDay To dLastDay
If Application.Weekday(i, 2) < 6 Then
If Not ws Is Nothing Then
ws.Name = Format(i, "MMMM d")
If ws.Index = Me.Worksheets.Count Then
Set ws = Nothing
Else
Set ws = ws.Next
End If
End If
End If
Next i
End If

End Sub
 
Top