How do I create a macro that will rename the worksheet tab to reflect text in
a cell on that worksheet? And, can I change the number of characters that can
be placed on a tab? (these are 2 separate questions) Thanks
--
ERA Mike

activesheet.name=range("a1")

Learn to keep it SHORT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ERA Mike" wrote in message
...
How do I create a macro that will rename the worksheet tab to reflect text
in
a cell on that worksheet? And, can I change the number of characters that
can
be placed on a tab? (these are 2 separate questions) Thanks
--
ERA Mike

Let's assume that you enter text in cell A1 and want the tab to automatically
reflect that. In the worksheet code area, enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a1 = Range("A1")
Set t = Target
If Intersect(t, a1) Is Nothing Then Exit Sub
ActiveSheet.Name = a1.Value
End Sub
--
Gary''s Student - gsnu200811


"ERA Mike" wrote:

How do I create a macro that will rename the worksheet tab to reflect text in
a cell on that worksheet? And, can I change the number of characters that can
be placed on a tab? (these are 2 separate questions) Thanks
--
ERA Mike

Thank you!
--
ERA Mike


"Gary''s Student" wrote:

Let's assume that you enter text in cell A1 and want the tab to automatically
reflect that. In the worksheet code area, enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a1 = Range("A1")
Set t = Target
If Intersect(t, a1) Is Nothing Then Exit Sub
ActiveSheet.Name = a1.Value
End Sub
--
Gary''s Student - gsnu200811


"ERA Mike" wrote:

How do I create a macro that will rename the worksheet tab to reflect text in
a cell on that worksheet? And, can I change the number of characters that can
be placed on a tab? (these are 2 separate questions) Thanks
--
ERA Mike

Thanks! If it's possible to change the number of characters allowed on a
tab, can you tell me how? Thanks again
--
ERA Mike


"Don Guillett" wrote:

activesheet.name=range("a1")

Learn to keep it SHORT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ERA Mike" wrote in message
...
How do I create a macro that will rename the worksheet tab to reflect text
in
a cell on that worksheet? And, can I change the number of characters that
can
be placed on a tab? (these are 2 separate questions) Thanks
--
ERA Mike

Not possible..................31 is max.


Gord Dibben MS Excel MVP

On Mon, 3 Nov 2008 09:52:08 -0800, ERA Mike
wrote:

Thanks! If it's possible to change the number of characters allowed on a
tab, can you tell me how? Thanks again

Thanks
--
ERA Mike


"Gord Dibben" wrote:

Not possible..................31 is max.


Gord Dibben MS Excel MVP

On Mon, 3 Nov 2008 09:52:08 -0800, ERA Mike
wrote:

Thanks! If it's possible to change the number of characters allowed on a
tab, can you tell me how? Thanks again

"Gary''s Student" wrote:

Let's assume that you enter text in cell A1 and want the tab to automatically
reflect that. In the worksheet code area, enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a1 = Range("A1")
Set t = Target
If Intersect(t, a1) Is Nothing Then Exit Sub
ActiveSheet.Name = a1.Value
End Sub

Dear Gary''s Student,
Works perfectly, but in my case the tab name should be taken from a the
cell with formula (or from the textbox received text from same linked cell
with formula). And the code stops when I put a formula in the A1 cell.
Would you please suggest somthing.

If A1 contains a formula rather than typed input, we need a different kind of
Event macro:

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

--
Gary''s Student - gsnu200822


"Vladimir" wrote:



"Gary''s Student" wrote:

Let's assume that you enter text in cell A1 and want the tab to automatically
reflect that. In the worksheet code area, enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a1 = Range("A1")
Set t = Target
If Intersect(t, a1) Is Nothing Then Exit Sub
ActiveSheet.Name = a1.Value
End Sub

Dear Gary''s Student,
Works perfectly, but in my case the tab name should be taken from a the
cell with formula (or from the textbox received text from same linked cell
with formula). And the code stops when I put a formula in the A1 cell.
Would you please suggest somthing.

Thank you, Gary''s Student. It works.
Could you tell me, please, how can I rename say sheets 2,3,4 from combo box
1 & combo box 2 on sheet 1. Sheet 1 has no permanent name.
Cobobox 1 has options Jan-Feb-March, March-April-May, etc. and combobox 2
has 2009, 2010, 2011 etc. I need sheet 2 to be renamed as Jan 2009, sheet 3 -
Feb 2009, sheet 4 - March 2009 and so on depending on the data user selected
in boxes 1 and 2 in sheet 1.
Also even if sheet 1 is protected, the user still has possibility to delete
or to type new word in the combobox. How to restrict it to the drop down list
only without any other choice?



"Gary''s Student" wrote:

If A1 contains a formula rather than typed input, we need a different kind of
Event macro:

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

--
Gary''s Student - gsnu200822


"Vladimir" wrote:



"Gary''s Student" wrote:

Let's assume that you enter text in cell A1 and want the tab to automatically
reflect that. In the worksheet code area, enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a1 = Range("A1")
Set t = Target
If Intersect(t, a1) Is Nothing Then Exit Sub
ActiveSheet.Name = a1.Value
End Sub

Dear Gary''s Student,
Works perfectly, but in my case the tab name should be taken from a the
cell with formula (or from the textbox received text from same linked cell
with formula). And the code stops when I put a formula in the A1 cell.
Would you please suggest somthing.