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.


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.



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

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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube