Pulling tab name from a cell

Microsoft Microsoft excel 2007 full vers...
February 26, 2010 at 07:16:29
Specs: Windows XP
I am trying to get a tab name to reference a cell in the same worksheet. Is there a way to do that.

Tutter


See More: Pulling tab name from a cell

Report •


#1
February 26, 2010 at 08:02:34
Yes, with VBA:

Sub TabFromCell()
 Sheets(1).Name = Sheets(1).Range("A1")
End Sub

You can run this manually or automatically depending on when you want it to happen. Let us know.


Report •

#2
February 26, 2010 at 08:08:26
Hi,

You can use the change event to detect a change in your linked cell. This event will run code that will change the Worksheet name.

The following code is attached to the Worksheet - Right-click the worksheet tab name and select View Code.
In the Visual basic window that opens paste this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
    ActiveSheet.Name = Target.Text
End If
End Sub

From the Visual Basic menu select File - Save
Use Alt+f11 (Alt key and function key #11 pressed together) to return to the main Excel Window

Now, changing the text in cell A1 will change the Tab name.

Note that this is very basic - there are no checks for a duplicate name - which is not allowed, and there are no checks for other non-valid worksheet names such as names containing special characters such as / or \.

You could add additional code to make this more robust.

You can change the linked cell - but note that it must include the $ signs.

Regards


Report •

#3
March 11, 2010 at 12:53:16
The cell I am referencing is C17. I put this in the way you said but it still doesn't work. I think I am missing something.

Report •

Related Solutions

#4
March 11, 2010 at 13:16:47
Hi,

Please post the code you have used.

Copy it all and paste it between the <pre> and </pre> tags. Click the 'pre' icon that you can find above the reply box and paste the code.

Also what is highlighted in the Project Explorer window when you have selected your code - something like Sheet1 (Sheet1).

Regards


Report •

#5
March 11, 2010 at 13:24:27
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$17" Then
    ActiveSheet.Name = Target.Text
End If
End Sub

Sheet 2(code) is in the explorer window

Don't know who this site works. The original question was, how do I get a worksheet name to change names when I reference a cell. In this case cell C17


Report •

#6
March 11, 2010 at 14:38:32
Hi,

My mistake !

The code should be:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$17" Then
    ActiveSheet.Name = Target.Text
End If
End Sub

The first line is Worksheet_Change, not Worksheet_SelectionChange.

Let me know if it works now.

Regards


Report •


Ask Question