Naming Tabs

Is there any way to force the tab name to be a cell in the worksheet?

Right click on the tab and paste this code in. Change cell reference as
needed. I would probably name the cell then change Range("A1") to
Range("TabName") where "TabName" is the name of the cell. Invalid sheet
names are ignored.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Me.Name = Range("A1").Value
End Sub

"ebro" wrote:

> Is there any way to force the tab name to be a cell in the worksheet?

So I need to right click and select "View Code"? Then paste the new code?

"JMB" wrote:

> Right click on the tab and paste this code in. Change cell reference as
> needed. I would probably name the cell then change Range("A1") to
> Range("TabName") where "TabName" is the name of the cell. Invalid sheet
> names are ignored.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error Resume Next
> Me.Name = Range("A1").Value
> End Sub
>
> "ebro" wrote:
>
> > Is there any way to force the tab name to be a cell in the worksheet?

Yes, I forgot that step. It is an event macro that will fire when the sheet
changes. You won't be able to change the sheet name w/o using VBA.

The reason for naming the cell (post back if you're unfamiliar w/naming
cells in Excel) is so that if it moves (by inserting/deleting rows or
columns, cutting/pasting, etc), VBA will still be able to find it. If you
use a cell reference in VBA (such as A1), it will always refer to A1, even if
the contents of A1 is moved to another cell.


"ebro" wrote:

> So I need to right click and select "View Code"? Then paste the new code?
>
> "JMB" wrote:
>
> > Right click on the tab and paste this code in. Change cell reference as
> > needed. I would probably name the cell then change Range("A1") to
> > Range("TabName") where "TabName" is the name of the cell. Invalid sheet
> > names are ignored.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > On Error Resume Next
> > Me.Name = Range("A1").Value
> > End Sub
> >
> > "ebro" wrote:
> >
> > > Is there any way to force the tab name to be a cell in the worksheet?