Worksheet tab macro


E

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
 
Ad

Advertisements

G

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
 
E

ERA Mike

Thank you!
--
ERA Mike


Gary''s Student said:
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
 
E

ERA Mike

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

Advertisements

V

Vladimir

Gary''s Student said:
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.
 
G

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
 
Ad

Advertisements

V

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top