Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that changes worksheet tab name
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that changes worksheet tab name
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" wrote in message oups.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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that changes worksheet tab name
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" wrote in message oups.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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to name worksheet tabs using a cell within the worksheet? | Excel Discussion (Misc queries) | |||
error with macro to name new worksheet with cell from old worksheet | Excel Programming | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming | |||
macro to apply worksheet event to active worksheet | Excel Programming | |||
Record Worksheet Content as Macro and Execute from another Worksheet | Excel Programming |