Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBAProject Sheet Names (XP)

    It would appear that Excel is indexing sheets within a workbook; left to right as they are displayed within the workbook. On the other hand, the VBAProject reflects a sheet number with a sheet name.

    I would like to change the sheet name based on the sheet number reflected in the VBAProject not the index as mentioned above. I thought that this would work but it does not rename the sheet if I move it from it's displayed position. Code: Sheets(2).Name = "Hans"

    Thanks for you assistance,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBAProject Sheet Names (XP)

    A sheet has two names: the name as displayed in the Sheet tab is the Name property in VBA, and the name displayed before the parentheses in the Project Explorer in the Visual Basic Editor is the CodeName property, and also the (Name) property in the Properties pane. You can set the CodeName interactively by editing the (Name) property, but you cannot set it using code.

    In the screenshot below, "John" is the Name property, and "Stevens" is the (Name) and CodeName property.

    Initially, the names of sheets are related to the order in which they are created, but since both can be changed, and since sheets can be moved around, in the end the names don't necessarily correlate with the position of the sheets within the workbook.

    But Sheets(2) is always the second sheet in the left-to-right order.
    Attached Images Attached Images
    • File Type: png x.png (1.4 KB, 0 views)

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBAProject Sheet Names (XP)

    Hans,

    Thank you for the explanation. I guess I'll just have to keep the sheet in the second position.

    Regards,
    John

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBAProject Sheet Names (XP)

    You don't really need to keep in in second position to be sure you are using the right sheet in your code.

    You can use the codename directly:

    Sheet1.Range("A1")

    will always point to cell A1 on the worksheet with codename Sheet1, even if your user (or you) renames this sheet to anything else. Moving the sheet also has no effect.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBAProject Sheet Names (XP)

    Thank you Jan Karel.

    Regards,
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •