worksheet name

  • I have learned from this site that there is the worksheet name as it appears on the tab and the index that is the tab number and finally the internal object name that is unchangable by the user. This is the name that appears in first in the Visual basic list of objects next to the parenthetical tab name.

    My questions are: what is this name called?

    How do I get this name from sheets(1).??? or worksheets(2).???

    how can I refer to this name in code without hard coding it? I know I can write sheet1.vaue = xxx but I want to write variablename.??? where the value of variable is

  • Names

    You can refer to the worksheet name by using the .Name property of the Sheet.

    for example:

    for each ws in Worksheets
    next ws

    In your case, you can get the name of Worksheet(1) by doing

  • Thank you but ...

    yes your example would allow reference to the worksheet by the name as it appears on the tab - a name that could be changed by the user. How would one refer to it using a variable = to the object name as it appears in VB editor.

    If you see in the editor under Microsoft Excel Objects

    sheet1 (myfirstsheet)
    sheet2 (mysecondsheet)

    you can refer to the first one as:





    but how would I say

    againwhatsheet = sheet1


  • Dim objExcelWBk As Workbook
    Dim objMySheet As Worksheet

    'set a reference to the loaded workbook
    Set objExcelWBk = ActiveWorkBook

    'set a reference to worksheet 1
    Set objMySheet = objExcelWBk.Worksheets(1)

    Is this what you meant?


  • Hi d,

    Just for reference, the property that you are referring to is the CodeName of the object. You can display it as follows:[vba]Sub Test()
    MsgBox ThisWorkbook.Worksheets("Sheet1").CodeName
    End Sub[/vba]I don't really see the problem with hardcoding using the CodeNames as the whole attraction of using them is that they are not easily changed by the average user. They can, of course, be changed in the VBE by accessing the objects properties in the Properties window. They can also be changed via code, but you'd need a good reason for doing this I think.


  • Using Edit-Replace to Reference CodeNames

    Hi there!

    Would you guys be able to help a humble rookie CodeName user for a moment?

    I understand CodeNames from a conceptual standpoint, and I recognize the benefit of using them so that as sheets are moved/renamed, the code which references them will still operate properly. The leap I am having trouble with is editing existing RECORDED macros, where MS-Excel has used the Tab Name(s) to reference sheet objects in the resulting code. David Hawley recommends using the Edit-Replace feature in the VBE to swap out traditional references, and that sounds logical enough, however, I don't have the working knowledge of the syntax to get these macros RUNNING again once I edit the text. They keep bugging out. Here's a simple example:

    Sub DENVER()
    ' DENVER Macro
    ' Macro recorded 9/8/2004 by JRSmith729

    Application.CutCopyMode = False
    Sheets(Array("Denver 1", "Denver 2", "Denver 3", "Denver 4")).Select
    Sheets("Denver 1").Activate
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    End Sub

    Let's say the VBE indicates that:

    Sheet1 (Denver 1)
    Sheet2 (Denver 2)
    Sheet3 (Denver 3)
    Sheet4 (Denver 4)

    When the Edit-Replace function is used PROPERLY to change these tab name references to CodeName references, what should the resulting code look like? I've tried several iterations and none delivered a functioning macro. Various run-time errors have come up, one said type mis-match, another said could not function in "break" mode. I'm lost.

    Your help is much appreciated.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!