Renaming tab name-# of code lines..HELP

[Guest]

Tom provided the code below...the code works great. My next problem: I have
quite a few tabs to rename. I got to my limit I think. It won't work for
sheets past a certain number of code lines. My question is: Is there a limit
to the number
of iterations? If so, how do I get around this? Any help would be
appreciated...Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)

 

[Bob Phillips]

perhaps this

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("E9")) Is Nothing Then
Sh.Name = Target.Value
Sh.Range("C6") = Target.Value
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob....however, your code removed the if Target.Address = "$E$9"
then piece...how do I tell it what value to look at to use. It still does
not change the tab names that I need. It seems handle 67 of these code
sections but no more...is there a limit? Thanks.

 

[Bob Phillips]

I removed that because that is what this line does

If Not Intersect(Target, Range("E9")) Is Nothing Then

it was superfluous.

This code, if inserted where shown, will work on any worksheet in the
workbook, regardless of how many there are.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob! That worked! Just curious...with the other code I had...is
there a limit? Seems like 67. Thanks again!

 

[Bob Phillips]

Not that I am aware of. There may be such a limit, I just don't know of it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks again Bob...One more question: When I am on the entry sheet each time
you move to a cell the hourglass comes up and it runs the code...is there a
way to hide this "waiting" so it is transparent to the user? It takes about
5 seconds of waiting each time you move one cell on the entry sheet. Thanks.

 

[Bob Phillips]

Really? Have you stepped through the code to see what is happening?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)