Tab Names

[JohnUK]

Hi, I am looking for a code that can change the sheet tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John

 

[Bob Phillips]

Hi John,

VBA event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JohnUK]

Many thanks Bob

-----Original Message-----
Hi John,

VBA event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)





.

 

[Jim May]

Here's a function approach:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

 

[Guest]

This also works:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))


The workbook has to be saved before this actualy works

Here's a function approach:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Hi, I am looking for a code that can change the sheet tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John

 

[JohnUK]

Thanks to Bob Phillips and Jim May for their answers to

 

[Bob Phillips]

John,

That last bit confused me.

Do you want to preserve the previous value to re-instate it at some time (if
so, what would trigger that re-instating)?

Or are you saying that if you change Sheet1 to say Bob, and then try to
change again to something else, it fails? If so, in what way?

Or is it something else?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JohnUK]

Sorry Bob, I must admit that I am not that good at
explaining, but here is another go-

Yes I would need the tabs to change back to there
previous names, which were 20, 21, 22, and so forth to 40
as the book closes down, ready to be used again the next
time it opens.

The workbook will be used for importing the data into
AutoRoute, and that is why I needed the tabs names to
change to make it easier to see what sheets / data are
being imported.
Once the workbook is complete, it would be used by a
dozen other people, and rather the risk of them saving
changes, it would automatically reset on close down, and
of course add a button in case any mistakes are made
whilst open.

John