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:


    Code
    for each ws in Worksheets
       msgbox ws.name
    next ws


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

    Code
    Worksheet(1).Name
  • 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:


    sheets(1).value
    worksheets(1).value
    worksheets("myfirstsheet").value


    whatsheet="myfirstsheet".value
    worksheets(whatsheet).value


    or


    sheet1.value


    but how would I say


    againwhatsheet = sheet1


    againwhatsheet.againwhatsheet.value

  • 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?


    Dave.

  • 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.


    HTH

  • 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
    '


    '
    Range("F16:M16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(Array("Denver 1", "Denver 2", "Denver 3", "Denver 4")).Select
    Sheets("Denver 1").Activate
    Range("E2").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("EDITOR").Select
    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.


    THANKS!
    JRSmith729

    Thanks!
    JRSmith729

Participate now!

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