How to make worksheet names dynamic?
 

In Excel 2003, is there any way to name a worksheet according to user input
in the sheet itself? So if I had a sheet with figures on it, and I specify a
cell to input say, the 'year', that the worksheet would then adopt it's name
from that input?

Take a look he

http://www.mcgimpsey.com/excel/event...efromcell.html

In article ,
Neil Mitchell-Goodson <Neil
wrote:

In Excel 2003, is there any way to name a worksheet according to user input
in the sheet itself? So if I had a sheet with figures on it, and I specify a
cell to input say, the 'year', that the worksheet would then adopt it's name
from that input?

Assuming your target cell for the sheet name is B1, right-click on the
worksheet tab, select View Code, and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[B1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA


"Neil Mitchell-Goodson" wrote:

In Excel 2003, is there any way to name a worksheet according to user input
in the sheet itself? So if I had a sheet with figures on it, and I specify a
cell to input say, the 'year', that the worksheet would then adopt it's name
from that input?

How to make worksheet names dynamic?
 

Jason,

Thanks, but i have a slight challenge. The formula works for words, but not
dates. In cell a1 I have the data January 1, 2008 and it shows as January
2008. That does not want to show up in the sheet name tab. If I put a word
in A1 it works great, any suggestions.
]
Thanks.

Jeff

"Jason Morin" wrote:

Assuming your target cell for the sheet name is B1, right-click on the
worksheet tab, select View Code, and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[B1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA


"Neil Mitchell-Goodson" wrote:

In Excel 2003, is there any way to name a worksheet according to user input
in the sheet itself? So if I had a sheet with figures on it, and I specify a
cell to input say, the 'year', that the worksheet would then adopt it's name
from that input?

How to make worksheet names dynamic?
 

Sub namesheetdate()
ActiveSheet.Name = Format(Range("c1"), "mmmm yyyy")
End Sub

--
Don Guillett
SalesAid Software

"Jeff Saunders" wrote in message
...
Jason,

Thanks, but i have a slight challenge. The formula works for words, but
not
dates. In cell a1 I have the data January 1, 2008 and it shows as January
2008. That does not want to show up in the sheet name tab. If I put a
word
in A1 it works great, any suggestions.
]
Thanks.

Jeff

"Jason Morin" wrote:

Assuming your target cell for the sheet name is B1, right-click on the
worksheet tab, select View Code, and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[B1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA


"Neil Mitchell-Goodson" wrote:

In Excel 2003, is there any way to name a worksheet according to user
input
in the sheet itself? So if I had a sheet with figures on it, and I
specify a
cell to input say, the 'year', that the worksheet would then adopt it's
name
from that input?

How to make worksheet names dynamic?
 

Don,

Thanks for the help. I know get the correct information in the worksheet
tab for the 1st worksheet, but not the other 11. I have added the code to
each of the other 11 months. I have set it up to automatically calculate the
other months in A1 of each worksheet dependent upon the A1 of the first
worksheet. I am also still getting the invalid sheet name. should I just
remove that line of code?

Jeff

"Don Guillett" wrote:

Sub namesheetdate()
ActiveSheet.Name = Format(Range("c1"), "mmmm yyyy")
End Sub

--
Don Guillett
SalesAid Software

"Jeff Saunders" wrote in message
...
Jason,

Thanks, but i have a slight challenge. The formula works for words, but
not
dates. In cell a1 I have the data January 1, 2008 and it shows as January
2008. That does not want to show up in the sheet name tab. If I put a
word
in A1 it works great, any suggestions.
]
Thanks.

Jeff

"Jason Morin" wrote:

Assuming your target cell for the sheet name is B1, right-click on the
worksheet tab, select View Code, and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[B1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA


"Neil Mitchell-Goodson" wrote:

In Excel 2003, is there any way to name a worksheet according to user
input
in the sheet itself? So if I had a sheet with figures on it, and I
specify a
cell to input say, the 'year', that the worksheet would then adopt it's
name
from that input?

How to make worksheet names dynamic?
 

Why not make your job easier by just putting 1/1/2008 in a1 of the 1st sheet
and use this to create each sheet name it and put the date in a1 (eliminate
that line if not needed). Then delete the un-needed sheets.
Sub namesheets()
With Sheet1
my = Year(.Range("a1"))
mm = 0
End With

For i = 1 To 12
Sheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet
.Name = Format(DateSerial(my, mm + i, 1), "mmmm yyyy")
' .Range("a1") = DateSerial(my, mm + i, 1)'un comment if desired
End With
Next i
End Sub

--
Don Guillett
SalesAid Software

"Jeff Saunders" wrote in message
...
Don,

Thanks for the help. I know get the correct information in the worksheet
tab for the 1st worksheet, but not the other 11. I have added the code to
each of the other 11 months. I have set it up to automatically calculate
the
other months in A1 of each worksheet dependent upon the A1 of the first
worksheet. I am also still getting the invalid sheet name. should I just
remove that line of code?

Jeff

"Don Guillett" wrote:

Sub namesheetdate()
ActiveSheet.Name = Format(Range("c1"), "mmmm yyyy")
End Sub

--
Don Guillett
SalesAid Software

"Jeff Saunders" wrote in message
...
Jason,

Thanks, but i have a slight challenge. The formula works for words,
but
not
dates. In cell a1 I have the data January 1, 2008 and it shows as
January
2008. That does not want to show up in the sheet name tab. If I put a
word
in A1 it works great, any suggestions.
]
Thanks.

Jeff

"Jason Morin" wrote:

Assuming your target cell for the sheet name is B1, right-click on the
worksheet tab, select View Code, and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[B1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA


"Neil Mitchell-Goodson" wrote:

In Excel 2003, is there any way to name a worksheet according to
user
input
in the sheet itself? So if I had a sheet with figures on it, and I
specify a
cell to input say, the 'year', that the worksheet would then adopt
it's
name
from that input?