Macro that changes worksheet tab name

[Magoo]

Is it possible to do the following:

I would like to create a macro that does one of the following two
things.

1. the macro would have a pick box to select between 12 months of the
year January-December. This would in turn change the name of the
worksheet to the selected month. Once the Macro is complete I would
like it to return the Worksheet Tab Name back to something Generic.

If the first way does not seem feasible;

2. the macro would have an input box so that the user could type the
name of the month to change the worksheet tab name.

I currently have a worksheet that has everything that is done in one
year on it. I used to separate the worksheets by month but found that
time consuming when searching.

Thanks in advance.

 

[Chip Pearson]

I would use a combination of Data Validation and the Worksheet_Change event
to do this. First, create a list of months in some range of cells, say
A1:A12. Then, select the cell in which you want to pick the month, say B1.
Go to the Data menu and choose Validation. Select List as the type of
validation and specify A1:A12 as the Source. Now when you select B1, you
will get a drop down box with the months.

Right-click the sheet tab and choose View Code. This will open that
worksheet's code module in the VBA Editor. Paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldName As String
OldName = Me.Name
If Target.Address = "$B$1" Then
On Error Resume Next
Application.EnableEvents = False
Me.Name = Target.Text
On Error GoTo 0
Application.EnableEvents = True
End If
'''''''''''''''''''''''''''''''
' Your code here
'''''''''''''''''''''''''''''''
Me.Name = OldName
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Magoo]

Hello Chip,
I currently have a macro that cycles through specific autofilters on
the worksheet.
What I would like to do is to merge something like this into the
current macro. So that when
the macro is invoked it will ask for the month and then run. Once the
macro has run it will return the worksheet Tab back to a Generic Name
e.g. "2006 ALL".

I am trying to make this as easy as possible for users who may not have
ever touched excel before.