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.
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
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 firstname.lastname@example.org