Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

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
0
mike637
Asked:
mike637
  • 2
1 Solution
 
redmondbCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now