Automatic Sheet Rename When Validation List Changes

[rgs694]

Hello,
Just finished my first 3 day VBA class last week. I'm intermediate-advanced in Excel, but have been struggling with VBA.

I've been search throughout the web for some code that will dynamically rename the current worksheet when I change the entry in a certain validation list. I found some basic code, but it all requires the user to select another cell or hit enter after the validation box changes.

The validation list is always in cell J1 and holds a list of portfolio strategy names. When the user selects a different portfolio name from the validation list, I'd like the current sheet name to reflect this new name in J1.

Would this require 2 procedures? One to initiate an event based on the validation change, and the other to rename the worksheet?

Thank you,
-Rich
Excel 2013

 

[JoeMo]

Requires only a worksheet_change event macro. This is code for the worksheet (not a standard module).
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("J1"), Target) Is Nothing Then
    If Not SheetExists(Target.Value) Then Me.Name = Target.Value
End If
End Sub
Function SheetExists(shName As String) As Boolean
SheetExists = False
For Each sh In ActiveWorkbook.Sheets
    If sh.Name = shName Then
        SheetExists = True
        Exit For
    End If
 Next sh
End Function

 

[rgs694]

Perfect! Worked like a charm.
Thanks JoeMo
-R

 

[JoeMo]

Perfect! Worked like a charm.
Thanks JoeMo
-R

You are welcome - thanks for the reply.