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 info@exceltip.com
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.
Hi, The below is not working any more. If you are able to do correct the code of change the code name of sheet. Please do.
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