Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
I have the following brief macro to change the tab name based on a change to
cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
Thanks Nigel
I get an error message for line: ws.Name = ws.Range("G1") I don't have duplicate names. Any ideas why it isn't working? Jack "Nigel" wrote in message ... Put this in the 13th Sheet code, it will change ALL other worksheets in the workbook. NOTE: if you have duplicate names the code will fail! Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim wS as worksheet For each wS in Worksheets ws.Name = ws.Range("G1") Next End Sub -- Regards, Nigel "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
Please give before/after examples. You can't have 2 worksheet names the
same. What are you trying to do? Merry Xmas -- Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
There are some strings that aren't valid.
Strings that include /, \, [, ], *, ? (and others??). Strings that are too long (max length for a worksheet name is 31 characters). And at least one reserved name that excel uses: History I'm guessing that you have a date (which may include slashes depending on your regional settings) that's causing the trouble. If you've formatted the cell nicely (using dashes instead of slashes), you could try: ws.Name = ws.Range("G1").Text jack wrote: Thanks Nigel I get an error message for line: ws.Name = ws.Range("G1") I don't have duplicate names. Any ideas why it isn't working? Jack "Nigel" wrote in message ... Put this in the 13th Sheet code, it will change ALL other worksheets in the workbook. NOTE: if you have duplicate names the code will fail! Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim wS as worksheet For each wS in Worksheets ws.Name = ws.Range("G1") Next End Sub -- Regards, Nigel "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
A broader explanation on what I am trying to accomplish:
I have a yearly calendar on one worksheet and I decided to setup printable monthly calendars on 12 separate worksheets. I've linked the 12 monthly sheets to the yearly calendar sheet so that when I change the yearly calendar worksheet, the monthly calendar sheets change according to the year. I am trying to change the monthly sheet names such as Jan 2009 to Jan 2010, etc. when changing the yearly calendar sheet. I want to change the sheet names on the monthly sheets only, not the yearly calendar sheet and one other sheet names. It appears that the coding that Nigel offered will change / update all of the worksheets and my fault that I didn't explain I want to change only the 12 worksheets names and leave the others as - is. Any help on how to do this naming / renaming of the 12 worksheet tabs would be greatly appreciated. I'm attempting to work / modify the code that Nigel offered, but I'm not getting the results desired. Merry Christmas, Jack "Don Guillett" wrote in message ... Please give before/after examples. You can't have 2 worksheet names the same. What are you trying to do? Merry Xmas Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
When making changes to text... the exact text that is being changed is kind
of important. When you say "monthly sheet names such as Jan 2009"... is that the exact format for the sheet names you want to change (3 letter abbreviated month name followed by a space followed by the year) or did you simplify the name for example purposes? If you simplified it, we need the real sheet name format that you are using. -- Rick (MVP - Excel) "jack" wrote in message ... A broader explanation on what I am trying to accomplish: I have a yearly calendar on one worksheet and I decided to setup printable monthly calendars on 12 separate worksheets. I've linked the 12 monthly sheets to the yearly calendar sheet so that when I change the yearly calendar worksheet, the monthly calendar sheets change according to the year. I am trying to change the monthly sheet names such as Jan 2009 to Jan 2010, etc. when changing the yearly calendar sheet. I want to change the sheet names on the monthly sheets only, not the yearly calendar sheet and one other sheet names. It appears that the coding that Nigel offered will change / update all of the worksheets and my fault that I didn't explain I want to change only the 12 worksheets names and leave the others as - is. Any help on how to do this naming / renaming of the 12 worksheet tabs would be greatly appreciated. I'm attempting to work / modify the code that Nigel offered, but I'm not getting the results desired. Merry Christmas, Jack "Don Guillett" wrote in message ... Please give before/after examples. You can't have 2 worksheet names the same. What are you trying to do? Merry Xmas Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
It may be easier to just send your wb to my addy below with details and
before/after examples -- Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... A broader explanation on what I am trying to accomplish: I have a yearly calendar on one worksheet and I decided to setup printable monthly calendars on 12 separate worksheets. I've linked the 12 monthly sheets to the yearly calendar sheet so that when I change the yearly calendar worksheet, the monthly calendar sheets change according to the year. I am trying to change the monthly sheet names such as Jan 2009 to Jan 2010, etc. when changing the yearly calendar sheet. I want to change the sheet names on the monthly sheets only, not the yearly calendar sheet and one other sheet names. It appears that the coding that Nigel offered will change / update all of the worksheets and my fault that I didn't explain I want to change only the 12 worksheets names and leave the others as - is. Any help on how to do this naming / renaming of the 12 worksheet tabs would be greatly appreciated. I'm attempting to work / modify the code that Nigel offered, but I'm not getting the results desired. Merry Christmas, Jack "Don Guillett" wrote in message ... Please give before/after examples. You can't have 2 worksheet names the same. What are you trying to do? Merry Xmas Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
The exact text format is January 2009, Feburary 2009, etc. It is the full
month name followed by a space followed by the year. That is what I have in the cell "G1" and cell "G1" is linked to the yearly calendar worksheet. Jack "Rick Rothstein" wrote in message ... When making changes to text... the exact text that is being changed is kind of important. When you say "monthly sheet names such as Jan 2009"... is that the exact format for the sheet names you want to change (3 letter abbreviated month name followed by a space followed by the year) or did you simplify the name for example purposes? If you simplified it, we need the real sheet name format that you are using. -- Rick (MVP - Excel) "jack" wrote in message ... A broader explanation on what I am trying to accomplish: I have a yearly calendar on one worksheet and I decided to setup printable monthly calendars on 12 separate worksheets. I've linked the 12 monthly sheets to the yearly calendar sheet so that when I change the yearly calendar worksheet, the monthly calendar sheets change according to the year. I am trying to change the monthly sheet names such as Jan 2009 to Jan 2010, etc. when changing the yearly calendar sheet. I want to change the sheet names on the monthly sheets only, not the yearly calendar sheet and one other sheet names. It appears that the coding that Nigel offered will change / update all of the worksheets and my fault that I didn't explain I want to change only the 12 worksheets names and leave the others as - is. Any help on how to do this naming / renaming of the 12 worksheet tabs would be greatly appreciated. I'm attempting to work / modify the code that Nigel offered, but I'm not getting the results desired. Merry Christmas, Jack "Don Guillett" wrote in message ... Please give before/after examples. You can't have 2 worksheet names the same. What are you trying to do? Merry Xmas Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
How about this to add to all sheets with a number as the last four in the
name Sub changesheetname() For i = 2 To Sheets.Count With Sheets(i) 'If .Name < "Sheet1" Then If IsNumeric(Right(.Name, 4)) Then ..Name = Left(.Name, Len(.Name) - 4) _ & Right(.Name, 4) + 1 End If End With Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... The exact text format is January 2009, Feburary 2009, etc. It is the full month name followed by a space followed by the year. That is what I have in the cell "G1" and cell "G1" is linked to the yearly calendar worksheet. Jack "Rick Rothstein" wrote in message ... When making changes to text... the exact text that is being changed is kind of important. When you say "monthly sheet names such as Jan 2009"... is that the exact format for the sheet names you want to change (3 letter abbreviated month name followed by a space followed by the year) or did you simplify the name for example purposes? If you simplified it, we need the real sheet name format that you are using. -- Rick (MVP - Excel) "jack" wrote in message ... A broader explanation on what I am trying to accomplish: I have a yearly calendar on one worksheet and I decided to setup printable monthly calendars on 12 separate worksheets. I've linked the 12 monthly sheets to the yearly calendar sheet so that when I change the yearly calendar worksheet, the monthly calendar sheets change according to the year. I am trying to change the monthly sheet names such as Jan 2009 to Jan 2010, etc. when changing the yearly calendar sheet. I want to change the sheet names on the monthly sheets only, not the yearly calendar sheet and one other sheet names. It appears that the coding that Nigel offered will change / update all of the worksheets and my fault that I didn't explain I want to change only the 12 worksheets names and leave the others as - is. Any help on how to do this naming / renaming of the 12 worksheet tabs would be greatly appreciated. I'm attempting to work / modify the code that Nigel offered, but I'm not getting the results desired. Merry Christmas, Jack "Don Guillett" wrote in message ... Please give before/after examples. You can't have 2 worksheet names the same. What are you trying to do? Merry Xmas Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change?
I am assuming that somewhere on the yearly calendar worksheet you have a
cell with just the year number in it which, for this example, I'll assume is cell A1 (located in two locations within my code). Right click the tab on your yearly calendar worksheet and copy/paste this code into the code window that opened up... Private Sub Worksheet_Change(ByVal Target As Range) Dim WS As Worksheet Dim Parts() As String Const Months As String = "JanFebMarAprMayJunJulAugSepOctNovDec" If Target.Address(0, 0) = "A1" Then For Each WS In Worksheets Parts = Split(WS.Name, " ") If UBound(Parts) = 1 Then If InStr(Months, Left(Parts(0), 3)) 0 _ And Parts(1) Like "####" Then WS.Name = Replace(WS.Name, Parts(1), Range("A1").Value) End If End If Next End If End Sub Now, go back to the yearly calendar worksheet and change A1 to a different year number and see if just those 12 worksheets' names change correctly. -- Rick (MVP - Excel) "jack" wrote in message ... The exact text format is January 2009, Feburary 2009, etc. It is the full month name followed by a space followed by the year. That is what I have in the cell "G1" and cell "G1" is linked to the yearly calendar worksheet. Jack "Rick Rothstein" wrote in message ... When making changes to text... the exact text that is being changed is kind of important. When you say "monthly sheet names such as Jan 2009"... is that the exact format for the sheet names you want to change (3 letter abbreviated month name followed by a space followed by the year) or did you simplify the name for example purposes? If you simplified it, we need the real sheet name format that you are using. -- Rick (MVP - Excel) "jack" wrote in message ... A broader explanation on what I am trying to accomplish: I have a yearly calendar on one worksheet and I decided to setup printable monthly calendars on 12 separate worksheets. I've linked the 12 monthly sheets to the yearly calendar sheet so that when I change the yearly calendar worksheet, the monthly calendar sheets change according to the year. I am trying to change the monthly sheet names such as Jan 2009 to Jan 2010, etc. when changing the yearly calendar sheet. I want to change the sheet names on the monthly sheets only, not the yearly calendar sheet and one other sheet names. It appears that the coding that Nigel offered will change / update all of the worksheets and my fault that I didn't explain I want to change only the 12 worksheets names and leave the others as - is. Any help on how to do this naming / renaming of the 12 worksheet tabs would be greatly appreciated. I'm attempting to work / modify the code that Nigel offered, but I'm not getting the results desired. Merry Christmas, Jack "Don Guillett" wrote in message ... Please give before/after examples. You can't have 2 worksheet names the same. What are you trying to do? Merry Xmas Don Guillett Microsoft MVP Excel SalesAid Software "jack" wrote in message ... I have the following brief macro to change the tab name based on a change to cell "G1": Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveSheet.Name = ActiveSheet.Range("G1") End Sub How do I change the tab name on a total of 12 worksheets (different names) when I make a change on the 13th worksheet? The 12 worksheets all are linked to the 13 worksheet so that when I change the 13th sheet, the others are updated and I would like to update (change) the tab names when the the sheets are updated. I've thought about placing the above macro in each worksheet but I can't even get the one to update (name change) when I modify the 13th sheet. Any guidance would be greatly appreciated. Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change the pivot chart automaticaly as values in the worksheet change | Excel Worksheet Functions | |||
change formula in a shared worksheet without losing change history | Excel Worksheet Functions | |||
Change in one Worksheet Activates Another Worksheet Change | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |