Sheet name change needed
 

Hi, I am new to programming but I would like to rename sheets based on drop
down lists. For example: on sheet1 I have a drop down that contains 2
choices, 1A or 2A that you can select...based on which one you choose, I
would like the name of sheet2 to be updated to match your selection from
sheet1. So, if I pick 1A from the dropdown on sheet1, sheet2 will be renamed
1A. Is this feasible? Thanks for any help,

Sheet name change needed
 

One way:

Put this in your worksheet 1 code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = .Parent.Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
.Parent.Parent.Sheets(2).Name = sSheetName
On Error GoTo 0
If Not sSheetName = .Parent.Parent.Sheets(2).Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub



In article ,
Kevin wrote:

Hi, I am new to programming but I would like to rename sheets based on drop
down lists. For example: on sheet1 I have a drop down that contains 2
choices, 1A or 2A that you can select...based on which one you choose, I
would like the name of sheet2 to be updated to match your selection from
sheet1. So, if I pick 1A from the dropdown on sheet1, sheet2 will be renamed
1A. Is this feasible? Thanks for any help,

Sheet name change needed
 

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A1")
If .Value < "" Then
Sheets(2).Name = Me.Range("A1").Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub

Right-click on the tab of Sheet1. Select "View Code".

Copy/paste the above into the sheet module.

Alt + q to return to Excel.

Choose from the dropdown in A1


Gord Dibben MS Excel MVP

On Fri, 7 Dec 2007 07:47:01 -0800, Kevin
wrote:

Hi, I am new to programming but I would like to rename sheets based on drop
down lists. For example: on sheet1 I have a drop down that contains 2
choices, 1A or 2A that you can select...based on which one you choose, I
would like the name of sheet2 to be updated to match your selection from
sheet1. So, if I pick 1A from the dropdown on sheet1, sheet2 will be renamed
1A. Is this feasible? Thanks for any help,

Sheet name change needed
 

Thank you both for the fast responses...works perfect. The help is greatly
appreciated, -Kevin