Excel VBA set sheet name at auto_open routine

Hello Experts.

I have my auto_open routine set - but I want to add additional code to ensure that my sheet names were not changed by an enduser.  If they were changed - then I want to reset them back to their original name on open.  I do not have the workbook protected, just the individual sheets.

I have 5 worksheets where the name should stay the same.  (The code.names are no longer sheet1 sheet 2) The code.names are set as follows:

week1, week2, week3, week4 & week5

In my open routine - I want to rename any worksheet that may have been changed by referencing it by it's codename. Listed below are the code.name with the respective worksheet names.

week1 = "week 1"
week2 = "week 2"
etc. etc.

Is this possible to do this?
Thank you,
Michael
mike637Asked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Hi, Michael.

Please see attached. The code is...
Option Explicit

Sub Auto_Open()
Dim xSheet As Worksheet

On Error Resume Next

    For Each xSheet In Worksheets
        Select Case xSheet.CodeName
            Case "week1": xSheet.Name = "week 1"
            Case "week2": xSheet.Name = "week 2"
            Case "week3": xSheet.Name = "week 3"
            Case "week4": xSheet.Name = "week 4"
            Case "week5": xSheet.Name = "week 5"
        End Select
    Next

On Error GoTo 0
    
End Sub

Open in new window

Regards,
Brian.
Five-Weeks.xls
0
 
mike637Author Commented:
AWESOME!!

Thank you Brian!
0
 
redmondbCommented:
Thanks, Michael!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.