Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Sheet Name References in VBA

    It seems that in the Project Explorer window, each worksheet in a file has two names: One which the system assigns when a new sheet is created and one that user can specify as a tab name. For example, Project Explorer will display: Sheet 1 (user-defined sheet name). I would like to refer to Sheet 1, instead of the user-defined name, so that the code will work even if the sheet name is changed later. Is there a way to refer to the system-assigned name instead of the user-specified name?

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    You can refer to it by it's number as in 1 = Sheet 1.
    This should demonstrate it:

    Sub test()
    For n = 1 To 3
    Worksheets(n).Activate
    Next
    End Sub

    If you want to refer to the tab name itself, enter it within quotes:

    Worksheets("Sheet1").Activate

    Post modified after reading Legare's more accurate reply below...

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    Thanks for the replies and the info.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    One small correction. Index number 1 only refers to the original Sheet1 if that sheet is still on tab1. In Sheet1 has been dragged to another position, or deleted, then index 1 will be whatever sheet is currently on tab 1.
    Legare Coleman

  5. #5
    DaveHawley
    Guest

    Re: Sheet Name References in VBA

    Actually you can reference Worksheets by the name Excels assigns to them (which can be seen in the Project Explorer). This is known as the CodeName and is a property of all Excel Worksheets. You would refer to a sheets code name like:
    Sheet1.Activate

    This is the prefered method when coding in Excel as the Code Name will not change, unlike the Index number and Tab name.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    Even though the CodeName seems to be read only as far as VBA is concerned, it can be changed in the properties window, (its the first name in brackets), in which case Sheet1.Activate will fail. But it is independent of the name that appears on the sheet tab.

    Andrew C

  7. #7
    DaveHawley
    Guest

    Re: Sheet Name References in VBA

    Yes it can! When I said It would not change I meant via the User Interface. Where as the the Index number and tab name can be.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    Dave: Thanks!!!! I now have a LOT of code to modify. I had seen Sheet1, Sheet2, etc. in the Object Browser numerous times but just skipped over it without making the connection.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    <P ID="edit"><FONT SIZE=-1>Edited by LegareColeman on 01/03/19 09:11.</FONT></P>Edited to say look at Dave Hawlety's reply to this this post that shows how this can be done.

    No, unfortunately you can not reference the sheet by the name Execl initially assigns to it. I think that would be a good enhancement, but it can't be done today. The only choices you have are the name on the tab, or the sheet's index number which also changes if the sheet's position on the tabs changes. If you can't depend on the sheet name not being changed, then my best suggestion would be to put something in a cell in the sheet to identify it, protect that cell, and then loop through the sheets looking for that cell.
    Legare Coleman

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    There is just one (at least) caveat to be aware of. If you delete Sheet1, which is also CodeName Sheet1, and add a new sheet later it will be given the CodeName Sheet1.

    Andrew

  11. #11
    DaveHawley
    Guest

    Re: Sheet Name References in VBA

    Legare, don't forget you can use Edit>Replace for this!

    Andrew, that is true if the Workbook is saved after deleting the sheet and before adding a new one. But when all is said and done it is by far the safest way to reference Sheets in VBA.

  12. #12
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    From Australia to Newfoundland, we live and learn. Thank you.

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    Dave , I agree 100% about method of referencing sheets in VBA.

    With XL2000, I can (and just checked) delete say sheet1, Insert a new sheet and it is given CodeName Sheet1, in an unsaved workbook. I just think it would probably be better if Excel did not reuse the CodeName, once applied. Perhaps the most foolproof way of keeping track of your sheets is to give them all a new CodeName at design time. But of course we can't do that for any sheets created by VBA.

    Andrew

  14. #14
    DaveHawley
    Guest

    Re: Sheet Name References in VBA

    I too am using Excel 2000. If I delete a sheet code named "Sheet1" (Or any code name) and then insert a new sheet it Excel assigns a code name of "Sheet4". It is only if I save after deletion and before inserting the new sheet that it re-uses the code name "Sheet1".

    Are you saying this in not the case on your PC ? Strange if it isn't as without saving and clearing memory Excels should assume it still has a Sheet1.

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet Name References in VBA

    Dave, I have checked it again and it behaves as I outlined earlier. However I did some experiments and found that when I inserted a new sheet before deleting sheet1 then everything behaved as you say. Now I normally open with just 3 sheets, so I thought that may have some bearing. So I removed my usual template and used the defaults, and guess what, your scenario prevailed. It is very strange, so I am attaching a copy of the file that behaves weirdly, if you would like to track down the cause. Though it will probably behave itself on your PC. There are no macros attached.

    Let me know how it behaves on your PC.

    Andrew
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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