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

Terry,

Put the following code in the worksheet's code module. It will
automatically rename the sheet when cell A1 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Me.Name = Target.Text
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"terry freedman" wrote in message
...
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

Hi
put the following code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents=false
On Error Resume Next
with target
If .Address = "$A$1" Then
Me.Name = .value
End If
end with
application.enableevents=true
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"terry freedman" schrieb im Newsbeitrag
...
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

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
me.name = Range("A1").Value
On Error goto 0
End Sub

Right click on the sheet tab and select view code. Put in code similar to
the above.

--
Regards,
Tom Ogilvy


"terry freedm
an" wrote in message
...
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

Thanks very much, Tom, that works a treat!

Thanks also to Chip and Frank

Best wishes

Terry

On Fri, 22 Oct 2004 09:59:46 -0400, "Tom Ogilvy"
wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
me.name = Range("A1").Value
On Error goto 0
End Sub

Right click on the sheet tab and select view code. Put in code similar to
the above.

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

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

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

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

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