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