Naming Worksheet Tabs?

[Scoober]

Can a cell be used to name a worksheet.

e.g In A1 Joe Bloggs is written, is there formula that can name the
worksheet's Tab Joe Bloggs?

 

[Eduardo]

Hi, take a look at Mcgimpsey web

http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html

if this helps please click yes, thanks

 

[Scoober]

dI'm not paticularly good with LX and did not understand a worg that that
link said.

I take it it is a little more than simply adding a foruula somewhere?

 

[Eduardo]

Hi,
in order to get the tab to be named according to a name entered in a cell
you need a VBA
Mc gimpsey tell you how to copy the code provided open the link from the
first paragraph tha says "Worksheet code module", it explain step by step
what to do

 

[Jacob Skaria]

Formula cannot name a sheet. You will have to use a macro..

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

If this post helps click Yes

 

[Scoober]

What is a VBA?

I have opened a tab by right clicking it and clicking view Tab and C&P'd

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

But when i hit enter nothing happens?

Scoober

 

[Scoober]

I right mouse click the tab I want named. Then I click view code. The I copy
and paste

Sub Macro()
ActiveSheet.Name = Range("B7")
End Sub

I push enter and nothing happens?

 

[Jacob Skaria]

Fine. If you are looking to change the sheet tab name as soon as you type in
a text in cell A1. try the below code. Set the Security level to low/medium
in (Tools|Macro|Security). Right click Sheettab>View code and paste the below
code.

Change the text in cell A1 and see

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$A$1" Then
If Trim(Target.Text) <> "" Then Me.Name = Trim(Range("A1"))
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Scoober]

Thank you Jacob. I have completed all your steps, but nothing happens. I hit
enter, and the curser travels down the page but does not enter anything. I
close the text box to leave the Tab and nothing happens.

I copied and pasted what you sent to me in the box that opens after i click
view code. The code is pasted but then nothing happens. There is no 'enter'
or 'save' button that i can see.

How do i enter or get the box to accept what i have copied and pasted in it.??

I am at a complete loss.

 

[Jacob Skaria]

Saving the workbook will save the code.

--Exit the excel application and re-open excel..open the saved workbook and
try.

--Make sure no other code exists in the code pane .. Sheet tab>View code

If this post helps click Yes

 

[Scoober]

In the MVB windoe I go to to file<save master template and the same window
appears with the code i copied and pasted. I close the windows using the red
X in the corner. When i reopen The work book nothing has changed.

I thank you for your help.

 

[Gord Dibben]

This is what Jacob posted...................

Fine. If you are looking to change the sheet tab name as soon as you type in
a text in cell A1. try the below code. Set the Security level to low/medium
in (Tools|Macro|Security). Right click Sheettab>View code and paste the
below
code.

Change the text in cell A1 and see

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$A$1" Then
If Trim(Target.Text) <> "" Then Me.Name = Trim(Range("A1"))
End If
Application.EnableEvents = True
End Sub

.........................................................

After copying and pasting the code into the sheet module, Alt + q to return
to the worksheet view.

Type something in A1 and see the sheet name change.


Gord Dibben MS Excel MVP

 

[Jacob Skaria]

Hi "Scoober"

Let us redo the whole thing again. Fine here we go.

--Close your existing excel application and all workbooks.
--Reopen Excel and currently we are in a new workbook.
--Check menu tools>Macros>Security and make sure the security level is set
to low
--Right click the Sheet tab>View code and on the right side you can see the
code pane with 2 drop downs . Paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$A$1" Then
If Trim(Target.Text) <> "" Then Me.Name = Trim(Range("A1"))
End If
Application.EnableEvents = True
End Sub

--Dont worry about saving the code. Get back to workbook.
--Try entering a text string in cell A1. (Make sure you dont enter
characters like \/ etc; which are not acceptable in sheet names..)
--Try and feedback..

If this post helps click Yes

 

[Scoober]

I did what you suggested and started from a completely blank worksheet and it
work!

But when i go back to the worksheet i am working on it does not work. Does
it matter that the cell i am trying to copy the name from (b5) is merged?

i am using this forumla

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$B$5" Then
If Trim(Target.Text) <> "" Then Me.Name = Trim(Range("B5"))
End If
Application.EnableEvents = True
End Sub

 

[Scoober]

Can i give you my email address so i can send you some screen shots of what i
am looking at?


Scoober

 

[Jacob Skaria]

Email the file to jacs_jay@y Replace y to the yahoodotcom

 

[Scoober]

Hi Jacob,

Did you receive my email ok?

 

[Scoober]

Hi Jacob,

How are things?

Can you confirm that you have received the emails i have sent you?

I have reports backing up and are looking forward to adding in the tab
naming feature as soon as possible.

Scoober

 

[Jacob Skaria]

Two things to be noted...in the excel file

--Range("B5") is a text and not a date. So you cannot format that to a date...
--Any changes made to Range("B5") will be reflected in the sheet name.
--Additionally the excel file contain a macro..which will do the same thing
for all sheets. To run the macro you can go to Tools>Macro>Macros>select
Macro1 and Run

If this post helps click Yes