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

[Guest]

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

 

[Guest]

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,

 

[Guest]

Absolutly Amazing.

Thanks so much.

It worked!!!

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?''.

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

 

[Guest]

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

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?''.

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