tab name

[des-sa]

please help
how do i give a tab of a worksheet the same name as a cell in the worksheet,
automatically

 

[Barb Reinhardt]

In Excel 2003, you'd need to use VBA. I'm guessing it's the same in Excel
2007. Is this the route you want to take?

 

[Rick Rothstein \(MVP - VB\)]

You will need to use worksheet event code to do that. Right-click the
worksheet's tab and select View Code from the popup menu that appears, then
copy/paste this code into the code window that appeared...

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

Change the reference to "$A$1" to the absolute address of the cell you
actually want to use. Then, whenever you change the text in that cell, the
worksheet tab will be changed to whatever you typed into it.

Rick

 

[Gary''s Student]

Say cell A1 is the cell in question. If A1 is filled by data entry, then use
this worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a1 = Range("A1")
If Intersect(t, a1) Is Nothing Then Exit Sub
ActiveSheet.Name = a1.Value
End Sub


If A1 is determine by a formula, then use this worksheet event macro:

Private Sub Worksheet_Calculate()
ActiveSheet.Name = Range("A1").Value
End Sub



Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[des-sa]

Hi,
i use excel 2003. to be honest, i have no idea what/how to do it. could you
please help. let me explain what i actually want to do, maybe you're in a
better position to advise then. i use a worksheet as a template for quotes i
give to clients - like 30 a day. so i want to reproduce the master /
template sheet everytime a new quote is given, than automatically number each
new quote - and then - name eithe the tab or new file the name of that
automatically generated quote number.
thank you immensely for your time!

 

[des-sa]

done that, nothing happens?

You will need to use worksheet event code to do that. Right-click the
worksheet's tab and select View Code from the popup menu that appears, then
copy/paste this code into the code window that appeared...

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

Change the reference to "$A$1" to the absolute address of the cell you
actually want to use. Then, whenever you change the text in that cell, the
worksheet tab will be changed to whatever you typed into it.

Rick

 

[des-sa]

got it!!!!!!!!!!!!
cant thank u enough!!
Disri

You will need to use worksheet event code to do that. Right-click the
worksheet's tab and select View Code from the popup menu that appears, then
copy/paste this code into the code window that appeared...

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

Change the reference to "$A$1" to the absolute address of the cell you
actually want to use. Then, whenever you change the text in that cell, the
worksheet tab will be changed to whatever you typed into it.

Rick

 

[Barb Reinhardt]

It will only work if you have macros enabled and if you change cell A1 on the
given worksheet. If you try to name a worksheet with a name that already
exists, it won't let you do it though.