I have a value in cell B7 which I would like to use as the name for the sheet
tab.

This cell value may change and I would like the tab to change accordingly.

I have found several answers involving VBA code but cannot get any to work.

FYI the value in cell B7 is the result of a link to another sheet. The
value is in the format 222-222, where there are always 3 numbers-3numbers.

I have right-clicked on the sheet tab, view code and pasted in the answers I
found - nothing works...

I would be extremely grateful for any suggestions, as I really have tried!

D

Hi D,

Try something like this - paste this into the sheet's module:

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("B7").Value < shname Then
shname = [b7].Value
End If
Me.name = shname
End Sub

Hope that helps you,

Gary

Hi D,

Try something like this - paste this into the sheet's module:

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("B7").Value < shname Then
shname = [b7].Value
End If
Me.name = shname
End Sub

Hope that helps you,

Gary

Thank you so much! This works!!! You are a star and I am deeply impressed
and very grateful.

It is a relief to get it working, and here are a few comments for anyone
else that might want to do this - perhaps a little basic, but hopefully
helpful for relative newbies like me:

Put the code in the sheet first, as the name only changes when the cell is
changed.
Make sure your cell contents don't have unacceptable characters - I changed
the ref. number I'm using from 123/001 to 123-001.
If instructions contain stuff you don't know how to do, like 'add code to
the sheet's module', search for that - I found the answer quite quickly.

D

"GaryDK" wrote:

Hi D,

Try something like this - paste this into the sheet's module:

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("B7").Value < shname Then
shname = [b7].Value
End If
Me.name = shname
End Sub

Hope that helps you,

Gary

Another question! I have just tried to use the same code on another sheet.
The cell value in this case is in A4, so I changed the code as follows, but
it doesn't appear to work. HOw can I amend the code to use for other sheets?

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("A4").Value < shname Then
shname = [a4].Value
End If
Me.Name = shname
End Sub


Thanks
Denise


"D" wrote:

Thank you so much! This works!!! You are a star and I am deeply impressed
and very grateful.

It is a relief to get it working, and here are a few comments for anyone
else that might want to do this - perhaps a little basic, but hopefully
helpful for relative newbies like me:

Put the code in the sheet first, as the name only changes when the cell is
changed.
Make sure your cell contents don't have unacceptable characters - I changed
the ref. number I'm using from 123/001 to 123-001.
If instructions contain stuff you don't know how to do, like 'add code to
the sheet's module', search for that - I found the answer quite quickly.

D

"GaryDK" wrote:

Hi D,

Try something like this - paste this into the sheet's module:

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("B7").Value < shname Then
shname = [b7].Value
End If
Me.name = shname
End Sub

Hope that helps you,

Gary

Hi Denise,

The simplest way to handle that, and a better way, is to make your name
cells on each sheet named ranges (Insert | Name | Define). For example,
if the cell is named "DataSheet" on the sheet, you could enter the
following in its module:

Option Explicit

Private Sub Worksheet_Calculate()
On Error GoTo ErrorTrap
If Range("DataSheet").Value < Me.Name Then
Me.Name = Range("DataSheet").Value
End If
Exit Sub
ErrorTrap:
MsgBox "New sheet name in range DataSheet " & _
"contains invalid characters."
End Sub

Then you can enter the same code in another sheet's module, replacing
"DataSheet" with whatever the defined name is for that particular
sheet's "name cell". Great catch on the illegal characters, so it now
traps errors. There could be other errors, like the range name not
existing, or misspelling the name in the code, but I assume you'd catch
that in testing.

Gary

Thanks Gary, have run into a further problem -
The contents of cell B7 on all 125 sheets provides the name for the sheet
tab. Cell B7 gets its information from a separate master sheet i.e.
=Master!C6

When I sort the master sheet (because new entries have been added at the
bottom and need to be arranged according to a ref. code) I get an error
message: Runtime error 1004 Cannot rename a sheet to the same name as
another sheet, a referenced object library or a workbook referenced by Visual
Basic.

I do want the tab names to change as a result of the master sheet sort,
because the individual sheets take their info from the master sheet too...can
I email you the file as an attachment?

Any ideas? Am now off to bed as nearly midnight. Thank you so much for
your support - I am learning so much and really appreciate the time you are
taking to help me resolve this!


"GaryDK" wrote:

Hi Denise,

The simplest way to handle that, and a better way, is to make your name
cells on each sheet named ranges (Insert | Name | Define). For example,
if the cell is named "DataSheet" on the sheet, you could enter the
following in its module:

Option Explicit

Private Sub Worksheet_Calculate()
On Error GoTo ErrorTrap
If Range("DataSheet").Value < Me.Name Then
Me.Name = Range("DataSheet").Value
End If
Exit Sub
ErrorTrap:
MsgBox "New sheet name in range DataSheet " & _
"contains invalid characters."
End Sub

Then you can enter the same code in another sheet's module, replacing
"DataSheet" with whatever the defined name is for that particular
sheet's "name cell". Great catch on the illegal characters, so it now
traps errors. There could be other errors, like the range name not
existing, or misspelling the name in the code, but I assume you'd catch
that in testing.

Gary