Worksheet tab macro

[ERA Mike]

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

 

[Don Guillett]

activesheet.name=range("a1")

Learn to keep it SHORT

 

[Gary''s Student]

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

 

[ERA Mike]

Thank you!
--
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

 

[ERA Mike]

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[ERA Mike]

Thanks
--
ERA Mike

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


Gord Dibben MS Excel MVP

 

[Vladimir]

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.

 

[Gary''s Student]

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

 

[Vladimir]

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?