Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Renaming tabs from particular cell contents
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range) on error resume next sh.Name = sh.Range("a2").Value if err.number < 0 then msgbox "Rename failed" err.clear end if on error goto 0 End Sub Let's Say you don't want it to give you an error if you have two tabs with same name but you want it to go ahead and rename the tab with the same name and insert (1),(2),..and so on after the tab name. How would you do this Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Renaming tabs from particular cell contents
Could you use some sort of looping code, like this:
Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if -- Best Regards, Luke M "scotty" wrote: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) on error resume next sh.Name = sh.Range("a2").Value if err.number < 0 then msgbox "Rename failed" err.clear end if on error goto 0 End Sub Let's Say you don't want it to give you an error if you have two tabs with same name but you want it to go ahead and rename the tab with the same name and insert (1),(2),..and so on after the tab name. How would you do this Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Renaming tabs from particular cell contents
Oops! Need to have
err.clear before the Goto Retest line. Otherwise an infinite loop is created. -- Best Regards, Luke M "Luke M" wrote: Could you use some sort of looping code, like this: Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if -- Best Regards, Luke M "scotty" wrote: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) on error resume next sh.Name = sh.Range("a2").Value if err.number < 0 then msgbox "Rename failed" err.clear end if on error goto 0 End Sub Let's Say you don't want it to give you an error if you have two tabs with same name but you want it to go ahead and rename the tab with the same name and insert (1),(2),..and so on after the tab name. How would you do this Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Renaming tabs from particular cell contents
ARGH! Brain fart today.
SHOULD be... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 err.clear sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if end sub -- Best Regards, Luke M "Luke M" wrote: Could you use some sort of looping code, like this: Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if -- Best Regards, Luke M "scotty" wrote: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) on error resume next sh.Name = sh.Range("a2").Value if err.number < 0 then msgbox "Rename failed" err.clear end if on error goto 0 End Sub Let's Say you don't want it to give you an error if you have two tabs with same name but you want it to go ahead and rename the tab with the same name and insert (1),(2),..and so on after the tab name. How would you do this Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Renaming tabs from particular cell contents
Thanks for your help....but I couldn't get that to work. When I put that
code in and changed the value of the cell, the sheet1 name changed to that, but when I went to sheet2 and changed the cell value to the same that was in sheet1, the hourglass came up and wouldn't go off and then excel stopped responding. "Luke M" wrote: ARGH! Brain fart today. SHOULD be... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 err.clear sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if end sub -- Best Regards, Luke M "Luke M" wrote: Could you use some sort of looping code, like this: Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if -- Best Regards, Luke M "scotty" wrote: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) on error resume next sh.Name = sh.Range("a2").Value if err.number < 0 then msgbox "Rename failed" err.clear end if on error goto 0 End Sub Let's Say you don't want it to give you an error if you have two tabs with same name but you want it to go ahead and rename the tab with the same name and insert (1),(2),..and so on after the tab name. How would you do this Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Renaming tabs from particular cell contents
sorry..it does work when I am using letters, but when I am using numbers in
the cell, that is when Excel stops responding, until I press ESC. How do I modify for numbers please Thanks!!! "scotty" wrote: Thanks for your help....but I couldn't get that to work. When I put that code in and changed the value of the cell, the sheet1 name changed to that, but when I went to sheet2 and changed the cell value to the same that was in sheet1, the hourglass came up and wouldn't go off and then excel stopped responding. "Luke M" wrote: ARGH! Brain fart today. SHOULD be... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 err.clear sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if end sub -- Best Regards, Luke M "Luke M" wrote: Could you use some sort of looping code, like this: Dim EndNumber as String On Error resume next EndNumber = 0 sh.Name = sh.Range("a2").Value Retest: if err.number < 0 then EndNumber=EndNumber + 1 sh.Name = sh.Range("a2").Value + "(" + EndNumber + ")" goto Retest end if -- Best Regards, Luke M "scotty" wrote: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) on error resume next sh.Name = sh.Range("a2").Value if err.number < 0 then msgbox "Rename failed" err.clear end if on error goto 0 End Sub Let's Say you don't want it to give you an error if you have two tabs with same name but you want it to go ahead and rename the tab with the same name and insert (1),(2),..and so on after the tab name. How would you do this Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming Tabs | Excel Discussion (Misc queries) | |||
renaming sheet tabs in a sequence | Excel Worksheet Functions | |||
renaming worksheet tabs | Excel Discussion (Misc queries) | |||
Hyperlinks and renaming tabs | Excel Discussion (Misc queries) | |||
Renaming Tabs | Excel Discussion (Misc queries) |