Magoo
12/13/2006 8:49:00 PM
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.
Chip Pearson wrote:
> 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" <Magoo_120@hotmail.com> wrote in message
> news:1166032912.099059.309420@t46g2000cwa.googlegroups.com...
> > 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.
> >