naming a sheet automatically

[terry freedman]

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

 

[Chip Pearson]

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

 

[Frank Kabel]

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

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

 

[Tom Ogilvy]

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

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 freedman]

Thanks very much, Tom, that works a treat!

Thanks also to Chip and Frank

Best wishes

Terry

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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.