+ Reply to Thread
Results 1 to 5 of 5

VBA and Sheet Names

Hybrid View

mike_302 VBA and Sheet Names 12-31-2018, 08:01 AM
Richard Buttrey Re: VBA and Sheet Names 12-31-2018, 08:11 AM
Richard Buttrey Re: VBA and Sheet Names 12-31-2018, 08:20 AM
mike_302 Re: VBA and Sheet Names 12-31-2018, 08:25 AM
Richard Buttrey Re: VBA and Sheet Names 12-31-2018, 08:41 AM
  1. #1
    Registered User
    Join Date
    08-28-2017
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    40

    VBA and Sheet Names

    When referencing a sheet in VBA, I was wondering if one way or another is more resilient to workbook development -- i.e. a worksheet always has two references, I think, visible in VBAProject browser: "Sheet# (Sheet name)". I also suspect that the Sheet# is more resilient to people adding and removing other sheets in the Workbook, or the possibility that the sheet name is changed. Therefore, my theory is that the more resilient potion is to refer to the Sheet# in VBA code -- correct me if I'm wrong.

    I've noticed, referring to the block of code below, the code only runs if I use the sheet name, not if I use "Sheet6", which is the corresponding Sheet# to my Sheet name. Does anyone have any insight on this topic?

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        On Error Resume Next
        If Sh.Name = "Sheet6" Then
            MsgBox "Do not edit. Data refreshes from linked dataset."
        End If
    End Sub

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA and Sheet Names

    Hi,

    Yes you should always use the VBA Sheet code name. The tab name is too easily changed by the user and will screw up any macro which uses it.

    You should use Sheet6 NOT "Sheet6"
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA and Sheet Names

    ...I should have added that it seems the only time you have to use the tab sheet names is if you are working with two open workbooks and addressing them (not activating them) with VBA code to do stuff.

  4. #4
    Registered User
    Join Date
    08-28-2017
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    40

    Re: VBA and Sheet Names

    Okay, I persisted with that general advice and changed "Sh.Name" to "Sh.CodeName"; but after the equals, I still had to have "Sheet6" in quotes.

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        On Error Resume Next
        If Sh.CodeName = "Sheet6" Then
            MsgBox "Do not edit. Data refreshes from linked dataset."
        End If
        
    End Sub
    Your reference to "sheet code name" gave me everything I needed to find the rest on Google

    Edit: I'd give you more reputation, but the forum says I give it to you too much and have to give it to others first... Sorry!

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA and Sheet Names

    Hi Mike,

    Perhaps some misunderstanding.

    You are correct. Using Sh.CodeName means you are getting VBA to return the literal text of the sheet's tab name, so as you identify that needs to be compared with a string of text.

    In other situations where you know which sheet is being used you would just use stuff like

    Sheet6.Range("A1").Copy
    where Sheet6 is the Code Name

    Somewhat confusing I agree.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-06-2014, 09:44 AM
  2. [SOLVED] Split contents of cell into single names then search for names on different sheet.
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 12-18-2013, 10:53 AM
  3. [SOLVED] Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACRO
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-29-2013, 03:24 PM
  4. Replies: 11
    Last Post: 10-21-2012, 01:40 AM
  5. Getting a list of Sheet Names by excluding certain Sheet Names instantenously..
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-09-2012, 12:16 PM
  6. Changing Sheet Names Based on # Values in Names
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2010, 03:34 PM
  7. Create array of file names/sheet names
    By BVHis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-06-2008, 11:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1