Changing an object name of a worksheet

When you handle a worksheet, you can handle a worksheet using Index property and Name property like  Sheets(1).Select  or  Sheets("Sheet1").Select . But if users will change the name of worksheet or change the sorting of the worksheets, you cannot access to the appropriate worksheet with the name property or the index property.

Another way to access to the worksheet is using an Object Name. An object name can be seen in the Project window in Visual Basic Editor. In the picture below, the object name of Sheet3 is wks_main. In the main window of Excel (not in VBE), when you change the worksheet name of Sheet3 form Sheet3 to MySheet, in VBE, Sheet3 will be changed as MySheet. But wks_main as an Object Name will not be changed.

The way of changing the Object Name of worksheets is, changing it via Properties Window in VBE or you can change it from code line. with Properties("_CodeName").

an image of VBE


The following code would add a worksheet and change the Object Name. So here after, you can access to the appropreate worksheet using the Object Name. Place the following in a standard module.

Note: When you compile VBA project, VBE returns error if the object wks_main does not exist as yet.

Option Explicit

Sub ChageWksObjectName()
    Dim wks As Worksheet
    Dim sPrevCodeName As String
    Dim sNewCodeName As String

    'set new object name
    sNewCodeName = "wks_main"

    'add a worksheet
    Set wks = Sheets.Add

    'get an object name of the added worksheet
    sPrevCodeName = wks.CodeName

    'change an object name of the added worksheet
    ThisWorkbook.VBProject.VBComponents(sPrevCodeName). _
                        Properties("_CodeName") = sNewCodeName
End Sub

Sub Test()
    wks_main.Range("A1").Value = "This is it!"
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo