Renaming tabs from particular cell contents

[scotty]

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
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

 

[Luke M]

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

 

[Luke M]

Oops! Need to have

err.clear

before the Goto Retest line. Otherwise an infinite loop is created.

 

[Luke M]

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

 

[scotty]

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.

 

[scotty]

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!!!