Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"SAM SEBAIHI" wrote in message ...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

SAM SEBAIHI

The following came from this disussion group. I have had it for a couple of
years and can not remember to whom I should give credit. It works well but
it is not mine!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then 'Change range to
your range & Sheet to your sheet
If ActiveSheet.Name = Sheet2.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub

This needs to be placed in the worksheet code. Select the tab you want to
use, on the tab right click and select "view Code" place it in the window on
the right. Make the necessary changes and it should work for you.

Does this meet your needs?

Mike Rogers

"SAM SEBAIHI" wrote:


How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Bob

How do I get this to work with a Calculated value in A1?

Like A1 has formula of =E1*F1 with F1 being changed.

Private Sub Worksheet_Calculate() doesn't do it for me.

Could be I don't understand what triggers the Calculate event<g


Gord

On Sat, 2 Dec 2006 19:35:10 -0000, "Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click

Gord Dibben MS Excel MVP

Please fix your system date. You're confusing the way the newsgroups are shown.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"SAM SEBAIHI" wrote in message ...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Sorry about that, my kids use my computer and it seems they have changed
the date.

--------------------------------------------------------------------------------
Sam Sebaihi Faculty Western International University Associate Programs
"Niek Otten" wrote in message
...
Please fix your system date. You're confusing the way the newsgroups are
shown.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"SAM SEBAIHI" wrote in message
...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Sam

With your names in A2:A10 of Sheet1

Sub Add_Sheets()
Dim rCell As Range
For Each rCell In Sheets("Sheet1").Range("A1:A10")
With Worksheets.Add(After:=Worksheets(Worksheets.Count) )
.Name = rCell.Value
End With
Next rCell
End Sub


Gord Dibben MS Excel MVP

On Wed, 2 Jan 2008 12:31:16 -0800, "SAM SEBAIHI"
wrote:

Bob, thank you so much, you saved my life. I am trying to modify this code to do the following:


data in sheet (1)

A
1 name
2 Brian
3 John
4 kim
5 Sam
6 ...
7 ...

I wanted to create new sheets with name tabs based on A2, A3, A4..etc... Can you please help?



--------------------------------------------------------------------------------

Sam Sebaihi
Faculty
Western International University
Associate Programs

(alternate email)
Phone #: (405)315-8223 Time zone: Pacific Time
"Bob Phillips" wrote in message ...
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"SAM SEBAIHI" wrote in message ...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Typo patrol.....

Change the range A1:A10 to A2:A10 in the code.


Gord

On Sat, 02 Dec 2006 15:17:07 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Sam

With your names in A2:A10 of Sheet1

Sub Add_Sheets()
Dim rCell As Range
For Each rCell In Sheets("Sheet1").Range("A1:A10")
With Worksheets.Add(After:=Worksheets(Worksheets.Count) )
.Name = rCell.Value
End With
Next rCell
End Sub


Gord Dibben MS Excel MVP

On Wed, 2 Jan 2008 12:31:16 -0800, "SAM SEBAIHI"
wrote:

Bob, thank you so much, you saved my life. I am trying to modify this code to do the following:


data in sheet (1)

A
1 name
2 Brian
3 John
4 kim
5 Sam
6 ...
7 ...

I wanted to create new sheets with name tabs based on A2, A3, A4..etc... Can you please help?



--------------------------------------------------------------------------------

Sam Sebaihi
Faculty
Western International University
Associate Programs

(alternate email)
Phone #: (405)315-8223 Time zone: Pacific Time
"Bob Phillips" wrote in message ...
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"SAM SEBAIHI" wrote in message ...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Gord Dibben MS Excel MVP

WOW!!!
You really are a genius, Thanks a bunch Gord :-)

================================================== ======================================

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Typo patrol.....

Change the range A1:A10 to A2:A10 in the code.


Gord

On Sat, 02 Dec 2006 15:17:07 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

Sam

With your names in A2:A10 of Sheet1

Sub Add_Sheets()
Dim rCell As Range
For Each rCell In Sheets("Sheet1").Range("A1:A10")
With Worksheets.Add(After:=Worksheets(Worksheets.Count) )
.Name = rCell.Value
End With
Next rCell
End Sub


Gord Dibben MS Excel MVP

On Wed, 2 Jan 2008 12:31:16 -0800, "SAM SEBAIHI"

wrote:

Bob, thank you so much, you saved my life. I am trying to modify this
code to do the following:


data in sheet (1)

A
1 name
2 Brian
3 John
4 kim
5 Sam
6 ...
7 ...

I wanted to create new sheets with name tabs based on A2, A3, A4..etc...
Can you please help?



--------------------------------------------------------------------------------

Sam Sebaihi
Faculty
Western International University
Associate Programs

(alternate email)
Phone #: (405)315-8223 Time zone: Pacific Time
"Bob Phillips" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"SAM SEBAIHI" wrote in message
...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to
john.

thank you,

Gord Dibben MS Excel MVP

Hi Gord,

Just do it in Calculate.

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

Of course it does mean it happens for every formula/value change that will
trigger a calculate.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Bob

How do I get this to work with a Calculated value in A1?

Like A1 has formula of =E1*F1 with F1 being changed.

Private Sub Worksheet_Calculate() doesn't do it for me.

Could be I don't understand what triggers the Calculate event<g


Gord

On Sat, 2 Dec 2006 19:35:10 -0000, "Bob Phillips"
wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click

Gord Dibben MS Excel MVP

You really need to do something about your system date. Now I have to see your message top posted for the next 13 months! <g

Biff
"SAM SEBAIHI" wrote in message ...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Yes.....that works but this doesn't

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

If I REM out the On Error GoTo line, I throw an error on

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Something here I just don't understand with my limited knowledge of events.


Gord

On Sun, 3 Dec 2006 01:06:48 -0000, "Bob Phillips" wrote:

Hi Gord,

Just do it in Calculate.

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

Of course it does mean it happens for every formula/value change that will
trigger a calculate.

Gord Dibben MS Excel MVP

Gord,

You can only test the Target object if it is an argument of the event. It is
an argument to Change, and to SelectionChange, but Calculate has no
arguments at all (when a cell is changed Calculate works out which cells to
re-evaluate based upon a complex internal Excel algorithm, using precedents,
descendants, volatile functions etc.; so it doesn't need to be told what to
work on, which is effectively what the argument(s) is(are)).

In the code you show, there is no Target, you could specify it, but it would
be meaningless. That is what I meant by 'Just do it ... ', as you have no
idea what caused Calculate to be triggered, you have to Just do it, each
time, every time.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Yes.....that works but this doesn't

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

If I REM out the On Error GoTo line, I throw an error on

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Something here I just don't understand with my limited knowledge of
events.


Gord

On Sun, 3 Dec 2006 01:06:48 -0000, "Bob Phillips"
wrote:

Hi Gord,

Just do it in Calculate.

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

Of course it does mean it happens for every formula/value change that
will
trigger a calculate.

Gord Dibben MS Excel MVP

If you are using Outlook Express you can delete it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"T. Valko" wrote in message
...
You really need to do something about your system date. Now I have to see
your message top posted for the next 13 months! <g

Biff
"SAM SEBAIHI" wrote in message
...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Yeah, I was being "cute".

Biff

"Bob Phillips" wrote in message
...
If you are using Outlook Express you can delete it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"T. Valko" wrote in message
...
You really need to do something about your system date. Now I have to see
your message top posted for the next 13 months! <g

Biff
"SAM SEBAIHI" wrote in message
...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Thanks Bob.

I will ruminate on this a while.

So A1 has a formula that updates when its precendent cells are changed but A1
updating does not trigger any event.

I realize now why some of the Calculate events I have tried don't trigger
anything for me.


Gord

On Sun, 3 Dec 2006 17:49:10 -0000, "Bob Phillips" wrote:

Gord,

You can only test the Target object if it is an argument of the event. It is
an argument to Change, and to SelectionChange, but Calculate has no
arguments at all (when a cell is changed Calculate works out which cells to
re-evaluate based upon a complex internal Excel algorithm, using precedents,
descendants, volatile functions etc.; so it doesn't need to be told what to
work on, which is effectively what the argument(s) is(are)).

In the code you show, there is no Target, you could specify it, but it would
be meaningless. That is what I meant by 'Just do it ... ', as you have no
idea what caused Calculate to be triggered, you have to Just do it, each
time, every time.

Gord Dibben MS Excel MVP

I have created a questionnaire in excel for customer product reviews. I want the customer to select the product they wish to review and based upon the selection, a new worksheet with the questionnaire should open up and the worksheet should be renamed with the product name.

Currently I have created a macro in excel which allows you to select the product and this automatically opens a new worksheet. I am however unable to rename the worksheet based upon the drop-down selection.

Any ideas?

Thanks a lot!

--Sadia

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

I don't understand what you mean by ... a new worksheet with the
questionnaire should open up ..., but you can rename the sheet like so

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== DV cell change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<Sadia wrote in message ...
I have created a questionnaire in excel for customer product reviews. I
want the customer to select the product they wish to review and based upon
the selection, a new worksheet with the questionnaire should open up and the
worksheet should be renamed with the product name.

Currently I have created a macro in excel which allows you to select the
product and this automatically opens a new worksheet. I am however unable to
rename the worksheet based upon the drop-down selection.

Any ideas?

Thanks a lot!

--Sadia

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,

Bob, thank you so much, you saved my life. I am trying to modify this code to do the following:


data in sheet (1)

A
1 name
2 Brian
3 John
4 kim
5 Sam
6 ...
7 ...

I wanted to create new sheets with name tabs based on A2, A3, A4..etc... Can you please help?



--------------------------------------------------------------------------------

Sam Sebaihi
Faculty
Western International University
Associate Programs

(alternate email)
Phone #: (405)315-8223 Time zone: Pacific Time
"Bob Phillips" wrote in message ...
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"SAM SEBAIHI" wrote in message ...

How can you rename a tab based on a cell? example,

if cell A1 contains John then the tab of sheet (1) renamed to john.

thank you,