Rename the worksheet module name using VBA in Microsoft Excel

 

In this article, we will create a macro for renaming worksheet module name.

Each worksheet has one module associated with it, known as worksheet module. Worksheet module contains VBA code for events related to worksheet and code for events linked with any Active X control inserted in the worksheet. Each worksheet module has a unique name.

In this example, we have a workbook with many worksheets and worksheet module names are like Sheet10, Sheet11, etc.

ArrowRawSheet

We want to rename the worksheet module name in a sequence like Sheet1, Sheet2, etc.

Logic explanation

We have written, “ChangeAllWorksheetCodenames” procedure for renaming the worksheet module.

As we want to rename the worksheet module name of all the worksheets present in the workbook, we have used FOR loop for looping through all the worksheet present inside the WORKSHEETS collection.

For renaming the worksheet module name of the worksheet, we have assigned new name to worksheet module by altering the CODENAME property of the VBCOMPONENT.

ArrowAfterRunningMacro

 

Please follow below for the code


Option Explicit

Sub ChangeAllWorksheetCodenames()
    
'Code for renaming worksheet codemodule names

Dim ws As Worksheet, i As Integer

i = 0

'Looping through all the worksheets in the active workbook
For Each ws In ActiveWorkbook.Worksheets

    i = i + 1
    
    'Putting a check over error occurence while renaming
    'If error occurs at runtime, code will skip the error and
    'Start executing from next line
    On Error Resume Next
    
    'Code for renaming codemodule name
    ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
        "Sheet" & i
        
    'Cancelling the effect error check statement we used earlier,
    'Code will not skip the code if any runtime error occurs
    On Error GoTo 0
    
Next ws

Set ws = Nothing

End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>