Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help! making a worksheet more automated? | Excel Discussion (Misc queries) | |||
can you make one worksheet update another | Excel Discussion (Misc queries) | |||
drop down menu containing worksheet names | Excel Worksheet Functions | |||
Dynamic Names can solve my problem? | Excel Worksheet Functions | |||
PivotChart: how to make it dynamic? | Charts and Charting in Excel |