Formula for naming tabs

[SharonJo]

I saw a previous question for this, but I need help understanding the formula.
I tried coping this formula, into A1, but--what do I need to change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Jim Cone]

The workbook must be saved.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"SharonJo"
wrote in message
I saw a previous question for this, but I need help understanding the formula.
I tried coping this formula, into A1, but--what do I need to change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Alan]

The formula looks like an answer to a specific question, what the original
question was is hard to say. It's series of text fomulas doing something,
not sure what!
What are you trying to achieve? You mention naming tabs, if you mean sheet
tabs, you can only do that manually or with VB code, a formula can't do
that.
Post back explaining what you want to do, I'm sure you'll get answers,
Regards,
Alan.

 

[Alan]

Good one Jim!
Alan.

 

[SharonJo]

Yes, I was trying to find a formula to name a sheet tab so that whatever I
type in A1 would name the tab. I even tried =A1 in the tab itself
(desperation).
Macros scare me, because I have not idea where to start unless I am
recording a macro.

The formula looks like an answer to a specific question, what the original
question was is hard to say. It's series of text fomulas doing something,
not sure what!
What are you trying to achieve? You mention naming tabs, if you mean sheet
tabs, you can only do that manually or with VB code, a formula can't do
that.
Post back explaining what you want to do, I'm sure you'll get answers,
Regards,
Alan.

I saw a previous question for this, but I need help understanding the
formula.
I tried coping this formula, into A1, but--what do I need to change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Alan]

One crude way to get you started, (do this in a new workbook),

Hit Alt and F11 to open the VB editor,
On the top toolbar hit 'Insert'
In the dropdown list hit 'Module', (not 'Class Module')
Copy and paste this into the empty window,

Sub NameSheet()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Hit Alt and F11 together to close the VB editor, thats the Macro done.

Now type something into A1, click 'Tools' on the toolbar at the top, select
'Macro', to the left of the dropdown select 'Macros', in the box that
appears you'll see only one Macro called 'NameSheet'
Hit 'Run' and the sheet tab will change,

There are other way to do this with buttons, it can do it for you when you
open the sheet, or when you type something into A1, post back with what you
want it to do,

Regards,
Alan.

Yes, I was trying to find a formula to name a sheet tab so that whatever I
type in A1 would name the tab. I even tried =A1 in the tab itself
(desperation).
Macros scare me, because I have not idea where to start unless I am
recording a macro.

The formula looks like an answer to a specific question, what the
original
question was is hard to say. It's series of text fomulas doing something,
not sure what!
What are you trying to achieve? You mention naming tabs, if you mean
sheet
tabs, you can only do that manually or with VB code, a formula can't do
that.
Post back explaining what you want to do, I'm sure you'll get answers,
Regards,
Alan.

I saw a previous question for this, but I need help understanding the
formula.
I tried coping this formula, into A1, but--what do I need to change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Alan]

Forgot to mention, when you get to the dialogue box with the Macros in it
and hit run, at the bottom is a button marked 'Options'
Click that and it asks what key you want as a shortcut, so if you enter 'z'
for instance, hitting Crtl and z together will run it for you without having
to use any toolbars etc,
Regards,
Alan.

One crude way to get you started, (do this in a new workbook),

Hit Alt and F11 to open the VB editor,
On the top toolbar hit 'Insert'
In the dropdown list hit 'Module', (not 'Class Module')
Copy and paste this into the empty window,

Sub NameSheet()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Hit Alt and F11 together to close the VB editor, thats the Macro done.

Now type something into A1, click 'Tools' on the toolbar at the top,
select 'Macro', to the left of the dropdown select 'Macros', in the box
that appears you'll see only one Macro called 'NameSheet'
Hit 'Run' and the sheet tab will change,

There are other way to do this with buttons, it can do it for you when you
open the sheet, or when you type something into A1, post back with what
you want it to do,

Regards,
Alan.

Yes, I was trying to find a formula to name a sheet tab so that whatever
I
type in A1 would name the tab. I even tried =A1 in the tab itself
(desperation).
Macros scare me, because I have not idea where to start unless I am
recording a macro.

The formula looks like an answer to a specific question, what the
original
question was is hard to say. It's series of text fomulas doing
something,
not sure what!
What are you trying to achieve? You mention naming tabs, if you mean
sheet
tabs, you can only do that manually or with VB code, a formula can't do
that.
Post back explaining what you want to do, I'm sure you'll get answers,
Regards,
Alan.
I saw a previous question for this, but I need help understanding the
formula.
I tried coping this formula, into A1, but--what do I need to change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Gord Dibben]

That formula will not re-name a sheet tab.

All it does is place the sheet tab name into a cell.

If you stuck with your first question you would see a reply from Jim that sends
you to Ron de Bruin's site for where to place the code you posted earlier.

That was the correct code.


Gord Dibben MS Excel MVP

 

[SharonJo]

WooHoo! Thank you, it works perfectly!
The only thing that I have not figured out is if I need to create this macro
in each of my workbooks or if there is some way to make it available in any
workbook!

Forgot to mention, when you get to the dialogue box with the Macros in it
and hit run, at the bottom is a button marked 'Options'
Click that and it asks what key you want as a shortcut, so if you enter 'z'
for instance, hitting Crtl and z together will run it for you without having
to use any toolbars etc,
Regards,
Alan.

One crude way to get you started, (do this in a new workbook),

Hit Alt and F11 to open the VB editor,
On the top toolbar hit 'Insert'
In the dropdown list hit 'Module', (not 'Class Module')
Copy and paste this into the empty window,

Sub NameSheet()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Hit Alt and F11 together to close the VB editor, thats the Macro done.

Now type something into A1, click 'Tools' on the toolbar at the top,
select 'Macro', to the left of the dropdown select 'Macros', in the box
that appears you'll see only one Macro called 'NameSheet'
Hit 'Run' and the sheet tab will change,

There are other way to do this with buttons, it can do it for you when you
open the sheet, or when you type something into A1, post back with what
you want it to do,

Regards,
Alan.

Yes, I was trying to find a formula to name a sheet tab so that whatever
I
type in A1 would name the tab. I even tried =A1 in the tab itself
(desperation).
Macros scare me, because I have not idea where to start unless I am
recording a macro.

:

The formula looks like an answer to a specific question, what the
original
question was is hard to say. It's series of text fomulas doing
something,
not sure what!
What are you trying to achieve? You mention naming tabs, if you mean
sheet
tabs, you can only do that manually or with VB code, a formula can't do
that.
Post back explaining what you want to do, I'm sure you'll get answers,
Regards,
Alan.
I saw a previous question for this, but I need help understanding the
formula.
I tried coping this formula, into A1, but--what do I need to change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Gord Dibben]

Place it in your Personal.xls so's it is available for all open workbooks.

See help on Personal Macro Workbook


Gord Dibben MS Excel MVP

WooHoo! Thank you, it works perfectly!
The only thing that I have not figured out is if I need to create this macro
in each of my workbooks or if there is some way to make it available in any
workbook!

Forgot to mention, when you get to the dialogue box with the Macros in it
and hit run, at the bottom is a button marked 'Options'
Click that and it asks what key you want as a shortcut, so if you enter 'z'
for instance, hitting Crtl and z together will run it for you without having
to use any toolbars etc,
Regards,
Alan.

One crude way to get you started, (do this in a new workbook),

Hit Alt and F11 to open the VB editor,
On the top toolbar hit 'Insert'
In the dropdown list hit 'Module', (not 'Class Module')
Copy and paste this into the empty window,

Sub NameSheet()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Hit Alt and F11 together to close the VB editor, thats the Macro done.

Now type something into A1, click 'Tools' on the toolbar at the top,
select 'Macro', to the left of the dropdown select 'Macros', in the box
that appears you'll see only one Macro called 'NameSheet'
Hit 'Run' and the sheet tab will change,

There are other way to do this with buttons, it can do it for you when you
open the sheet, or when you type something into A1, post back with what
you want it to do,

Regards,
Alan.
Yes, I was trying to find a formula to name a sheet tab so that whatever
I
type in A1 would name the tab. I even tried =A1 in the tab itself
(desperation).
Macros scare me, because I have not idea where to start unless I am
recording a macro.

:

The formula looks like an answer to a specific question, what the
original
question was is hard to say. It's series of text fomulas doing
something,
not sure what!
What are you trying to achieve? You mention naming tabs, if you mean
sheet
tabs, you can only do that manually or with VB code, a formula can't do
that.
Post back explaining what you want to do, I'm sure you'll get answers,
Regards,
Alan.
I saw a previous question for this, but I need help understanding the
formula.
I tried coping this formula, into A1, but--what do I need to change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Alan]

Glad you got it working,

Yes you can make it available in any workbook, you need to put it into your
Personal.xls file.

An easy way to do this is to record a new macro from 'Tools' > 'Macro' >
Record New Macro'
The dialogue box will appear telling you it's Macro1, just below that is
another drop down box marked 'Store macro in : '
This will be saying 'This Woorkbook'. Change that to 'Personal Macro
Workbook' > OK
Now do something, doesn't matter what, enter BBB into B1 for example, then
stop recording the macro.

Hit Alt and F11 to open the VB Editor,
On the top left you'll see an icon labeled 'VBAProject(Personal.XLS), click
that,
Below that will be a folder icon marked 'Modules'
Open that, (if its not already open) and you'll see 'Module1'
Double click that and you should see the code you just recorded,
Delete all of that so the window is empty and copy and paste this into it :-

Sub NameSheet()
If ActiveSheet.Range("A1") = "" Then Exit Sub
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

(The extra line's there to avoid an error message if you run the macro with
A1 empty)

Close the VB Editor, save the file and that macro will be available in all
workbooks. There's no need to keep that file, you can delete it once saved.

To put the shortcut back, do as you did before, hit the Options button in
the Macro dialogue box and select the letter you want to use,

Regards,
Alan.

WooHoo! Thank you, it works perfectly!
The only thing that I have not figured out is if I need to create this
macro
in each of my workbooks or if there is some way to make it available in
any
workbook!

Forgot to mention, when you get to the dialogue box with the Macros in it
and hit run, at the bottom is a button marked 'Options'
Click that and it asks what key you want as a shortcut, so if you enter
'z'
for instance, hitting Crtl and z together will run it for you without
having
to use any toolbars etc,
Regards,
Alan.

One crude way to get you started, (do this in a new workbook),

Hit Alt and F11 to open the VB editor,
On the top toolbar hit 'Insert'
In the dropdown list hit 'Module', (not 'Class Module')
Copy and paste this into the empty window,

Sub NameSheet()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Hit Alt and F11 together to close the VB editor, thats the Macro done.

Now type something into A1, click 'Tools' on the toolbar at the top,
select 'Macro', to the left of the dropdown select 'Macros', in the box
that appears you'll see only one Macro called 'NameSheet'
Hit 'Run' and the sheet tab will change,

There are other way to do this with buttons, it can do it for you when
you
open the sheet, or when you type something into A1, post back with what
you want it to do,

Regards,
Alan.
Yes, I was trying to find a formula to name a sheet tab so that
whatever
I
type in A1 would name the tab. I even tried =A1 in the tab itself
(desperation).
Macros scare me, because I have not idea where to start unless I am
recording a macro.

:

The formula looks like an answer to a specific question, what the
original
question was is hard to say. It's series of text fomulas doing
something,
not sure what!
What are you trying to achieve? You mention naming tabs, if you mean
sheet
tabs, you can only do that manually or with VB code, a formula can't
do
that.
Post back explaining what you want to do, I'm sure you'll get
answers,
Regards,
Alan.
I saw a previous question for this, but I need help understanding
the
formula.
I tried coping this formula, into A1, but--what do I need to
change?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))