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

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

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

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

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

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