How to change a tab name with cell reference and formula?

I would like to automatically change a tab's name from cell A1 but the
problem starts because cell A1 is in reference from a different sheet so it
automatically changes and i don't input it manually. All tries i made worked
only manually using the VBA.

Cell A1 inputs: =""&Intro!A3&" 7"
How can i change Cell "A3" in Sheet "Intro" So the tab's name will change
accordingly?

Thanks, Jared

You're going to have to put some VBA code in the worksheet who's name you
want to keep changing.

Because you will be changing the name of the worksheet, you obviously can't
refer to the worksheet name in your code. BUT, besides the name that you see
on the tab, when you go into the Visual Basic Editor (VBE)
[Tools>Macro>Visual Basic Editor] and look in the Project Window on the left
side of the screen, you will see something like...
Sheet2(Info). 'Sheet2' is the REAL name of the worksheet.

Now, take a look at the REAL sheet name of the worksheet who's name you want
to keep changing. For this example, let's assume the REAL name is 'Sheet1'
and the tab name is 'Shmoe'.

1) Highlight and double-click on the worksheet Sheet1(Shmoe) in the Project
Window.
2) In the code window to the right of the Project Window, you will see two
drop-down boxes. The one on the left will say (General) and the one on the
right will say (Declarations).
3) Pull down the (General) drop-down box and choose 'Worksheet'.
4) Pull down the (Declarations) drop-down box and choose 'Calculate'. Your
cursor will now be blinking inside a procedure called 'Private Sub
Worksheet_Calculate()'.
5) Type the 2 lines...
On error resume next
Sheet1.Name = Range("A1").Value

Now, whenever the sheet is calculated, either automatically or using 'F9',
'Shmoe' will look in A1 and change it's name to that value.
Note: It won't work if another worksheet already has the name that you put
in A1.

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jared" wrote:

> I would like to automatically change a tab's name from cell A1 but the
> problem starts because cell A1 is in reference from a different sheet so it
> automatically changes and i don't input it manually. All tries i made worked
> only manually using the VBA.
>
> Cell A1 inputs: =""&Intro!A3&" 7"
> How can i change Cell "A3" in Sheet "Intro" So the tab's name will change
> accordingly?
>
> Thanks, Jared

Absolutly Amazing.

Thanks so much.

It worked!!!


"Gary L Brown" wrote:

> You're going to have to put some VBA code in the worksheet who's name you
> want to keep changing.
>
> Because you will be changing the name of the worksheet, you obviously can't
> refer to the worksheet name in your code. BUT, besides the name that you see
> on the tab, when you go into the Visual Basic Editor (VBE)
> [Tools>Macro>Visual Basic Editor] and look in the Project Window on the left
> side of the screen, you will see something like...
> Sheet2(Info). 'Sheet2' is the REAL name of the worksheet.
>
> Now, take a look at the REAL sheet name of the worksheet who's name you want
> to keep changing. For this example, let's assume the REAL name is 'Sheet1'
> and the tab name is 'Shmoe'.
>
> 1) Highlight and double-click on the worksheet Sheet1(Shmoe) in the Project
> Window.
> 2) In the code window to the right of the Project Window, you will see two
> drop-down boxes. The one on the left will say (General) and the one on the
> right will say (Declarations).
> 3) Pull down the (General) drop-down box and choose 'Worksheet'.
> 4) Pull down the (Declarations) drop-down box and choose 'Calculate'. Your
> cursor will now be blinking inside a procedure called 'Private Sub
> Worksheet_Calculate()'.
> 5) Type the 2 lines...
> On error resume next
> Sheet1.Name = Range("A1").Value
>
> Now, whenever the sheet is calculated, either automatically or using 'F9',
> 'Shmoe' will look in A1 and change it's name to that value.
> Note: It won't work if another worksheet already has the name that you put
> in A1.
>
> HTH,
> --
> Gary Brown
> gary_brown@ge_NOSPAM.com
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "Jared" wrote:
>
> > I would like to automatically change a tab's name from cell A1 but the
> > problem starts because cell A1 is in reference from a different sheet so it
> > automatically changes and i don't input it manually. All tries i made worked
> > only manually using the VBA.
> >
> > Cell A1 inputs: =""&Intro!A3&" 7"
> > How can i change Cell "A3" in Sheet "Intro" So the tab's name will change
> > accordingly?
> >
> > Thanks, Jared

After trying this out it seemed to me now that every little change that
happens in the sheet, it starts to caluclate. The problem is that it is too
much calculation power for the machine. Sometimes the systems crashes of
overload or it takes more than a while for it to accept input.

Do you have another way of doing this with a more specific method?
or actually creating a macro for doing it?

Thanks again
Jared

"Gary L Brown" wrote:

> You're going to have to put some VBA code in the worksheet who's name you
> want to keep changing.
>
> Because you will be changing the name of the worksheet, you obviously can't
> refer to the worksheet name in your code. BUT, besides the name that you see
> on the tab, when you go into the Visual Basic Editor (VBE)
> [Tools>Macro>Visual Basic Editor] and look in the Project Window on the left
> side of the screen, you will see something like...
> Sheet2(Info). 'Sheet2' is the REAL name of the worksheet.
>
> Now, take a look at the REAL sheet name of the worksheet who's name you want
> to keep changing. For this example, let's assume the REAL name is 'Sheet1'
> and the tab name is 'Shmoe'.
>
> 1) Highlight and double-click on the worksheet Sheet1(Shmoe) in the Project
> Window.
> 2) In the code window to the right of the Project Window, you will see two
> drop-down boxes. The one on the left will say (General) and the one on the
> right will say (Declarations).
> 3) Pull down the (General) drop-down box and choose 'Worksheet'.
> 4) Pull down the (Declarations) drop-down box and choose 'Calculate'. Your
> cursor will now be blinking inside a procedure called 'Private Sub
> Worksheet_Calculate()'.
> 5) Type the 2 lines...
> On error resume next
> Sheet1.Name = Range("A1").Value
>
> Now, whenever the sheet is calculated, either automatically or using 'F9',
> 'Shmoe' will look in A1 and change it's name to that value.
> Note: It won't work if another worksheet already has the name that you put
> in A1.
>
> HTH,
> --
> Gary Brown
> gary_brown@ge_NOSPAM.com
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "Jared" wrote:
>
> > I would like to automatically change a tab's name from cell A1 but the
> > problem starts because cell A1 is in reference from a different sheet so it
> > automatically changes and i don't input it manually. All tries i made worked
> > only manually using the VBA.
> >
> > Cell A1 inputs: =""&Intro!A3&" 7"
> > How can i change Cell "A3" in Sheet "Intro" So the tab's name will change
> > accordingly?
> >
> > Thanks, Jared

8 years later and this post is still solving the same queries.
Thanks for having put in the effort here, this worked so simply and quickly.

Originally Posted by Gary L Brown

You're going to have to put some VBA code in the worksheet who's name you
want to keep changing.

Because you will be changing the name of the worksheet, you obviously can't
refer to the worksheet name in your code. BUT, besides the name that you see
on the tab, when you go into the Visual Basic Editor (VBE)
[Tools>Macro>Visual Basic Editor] and look in the Project Window on the left
side of the screen, you will see something like...
Sheet2(Info). 'Sheet2' is the REAL name of the worksheet.

Now, take a look at the REAL sheet name of the worksheet who's name you want
to keep changing. For this example, let's assume the REAL name is 'Sheet1'
and the tab name is 'Shmoe'.

1) Highlight and double-click on the worksheet Sheet1(Shmoe) in the Project
Window.
2) In the code window to the right of the Project Window, you will see two
drop-down boxes. The one on the left will say (General) and the one on the
right will say (Declarations).
3) Pull down the (General) drop-down box and choose 'Worksheet'.
4) Pull down the (Declarations) drop-down box and choose 'Calculate'. Your
cursor will now be blinking inside a procedure called 'Private Sub
Worksheet_Calculate()'.
5) Type the 2 lines...
On error resume next
Sheet1.Name = Range("A1").Value

Now, whenever the sheet is calculated, either automatically or using 'F9',
'Shmoe' will look in A1 and change it's name to that value.
Note: It won't work if another worksheet already has the name that you put
in A1.

HTH,
--
Gary Brown
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jared" wrote:

> I would like to automatically change a tab's name from cell A1 but the
> problem starts because cell A1 is in reference from a different sheet so it
> automatically changes and i don't input it manually. All tries i made worked
> only manually using the VBA.
>
> Cell A1 inputs: =""&Intro!A3&" 7"
> How can i change Cell "A3" in Sheet "Intro" So the tab's name will change
> accordingly?
>
> Thanks, Jared