Sheet name change needed

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

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,
 
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
 
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
 
Thank you both for the fast responses...works perfect. The help is greatly
appreciated, -Kevin
 
Back
Top