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?