There was a typo
Sheets(1).Name = strName

When I changed that to Sheets(i).Name = strName it worked. There must
be at least 12 sheets in the workbook and the sheet with the date in L7 must
be the activesheet when you run it.

Sub AABBCC()
If Range("t7").Value < "" Then
If IsDate(Range("L7")) Then
Set dt = Range("L7")
For i = 1 To 12
strName = Format(DateSerial(Year(dt), Month(dt) + i - 1, 1), "mmmm")
Sheets(i).Name = strName
Next
ThisWorkbook.Save
Else
MsgBox " Invalid date in L7"
End If
Else
If MsgBox("You omitted your name. Do " _
& "you still want to exit?", vbYesNo) = vbNo _
Then
Sheets(1).Name = "Est 1"
Cancel = True
End If
End If
End Sub

The above worked as I expected. (In only tested the condition where there
is a date in L7 and T7 has the formula you talked about.

--
Regards,
Tom Ogilvy


"NWilcox" wrote in message
...
I put it in just as it was starting with the same statement. I saved it
and
closed it. I closed the workbook and reopened it. The page was named the
month prior, and still only the first sheet. I then tried to put it each
sheet 1 and sheet 2. I had the same results. What am I doing wrong? I
appreciate your help.



"Tom Ogilvy" wrote:

If Range("t7").Value < "" Then
if isdate(Range("L7")) then
set dt = Range("L7")
for i = 1 to 12
strName = format(DateSerial(year(dt),Month(dt)+i-1,1),"mmmm")
Sheets(1).Name = strName
Next
ThisWorkbook.Save
else
msgbox " Invalid date in L7"
end if
Else
If MsgBox("You omitted your name. Do " _
& "you still want to exit?", vbYesNo) = vbNo _
Then
Sheets(1).Name = "Est 1"
Cancel = True
End If
End If
End Sub

--
regards,
Tom Ogilvy


"NWilcox" wrote in message
...
Hi, I'm new to Excel VBA. I'm working on a workbook with 12 sheets.
On
Sheet 1 the user inputs the date which they are beginning on. It then
puts
the end of the month in the next cell. On Sheet 2 - 12, the first
date is
automatically changed to the next month and the end of the month is
added.
The user wants me to automatically name each sheet for the month is
represents. I've tried to use all the steps in this discussion and
can't
seem to get them to change automatically. I managed to get Sheet 1 to
change
to the proper month by making a cell T7 name the month using
=text(L7,"mmmm")
where L7 is the date input cell. I used the following code in the
ThisWorkbook section:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' This procedure changes the worksheet name to month name

Dim strName As String

If Range("t7").Value < "" Then
strName = Range("t7").Value
Sheets(1).Name = strName
Else
If MsgBox("You omitted your name. Do " _
& "you still want to exit?", vbYesNo) = vbNo _
Then
Sheets(1).Name = "Est 1"
Cancel = True
End If
End If
End Sub

Can anyone help me get this to name each sheet according to the proper
month?

TIA



"terry freedman" wrote:

Hi

is it possible to automatically name a sheet as the value in one of
the cels? I tried doing this in the View code section of a sheet,
but
nothing works. Also, it falls down if the value in the cell has a
space in it.

TIA

Terry
==
Terry Freedman
For articles on e-learning and ICT, and to subscribe to Computers in
Classrooms, visit http://www.ictineducation.org