1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can not reference sheet , through Sheet's Code Name

Discussion in 'VBA Macros' started by Chirag R Raval, Aug 14, 2018.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    630
    Dear All,

    simply I want to reference sheet by its code name and not its sheet's name
    I face below problem when trying to reference sheet by sheet's code name.
    but if I reference through index number like sheets(1) its work.

    below its screen shots of process
    (1)
    1-SHEET1.png

    (2)
    2-activeworkbo-ok.png

    (3)
    3-METHOD.png

    (4)
    4-can not create object.png

    (5)
    5-sheet code name -METHOD FAIL.png


    Though I uncheck all extra references checked, , juts remaining (unchecked) 4 basic reference, but its not work.


    Also request to guide how to expand reference window in VBE to properly view and select / manage reference libraries. (how to make reference window expandable)

    Also I try as per below (as per attached screen shot no (6))

    Code (vb):

    Sub test()
    ActiveWorkbook.Sheets(Sheet1).Select
    End Sub
     
    But "Run Time Error 429", "Activex Component Can't Create Object" displayed every time.

    Hope there are some solution found.

    Regards,

    Chirag Raval
    Last edited: Aug 14, 2018
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,520
    Simply use Sheet1.Select

    In my example the Worksheet is Named Inputs
    But it's Code Module Name is Sheet1

    upload_2018-8-14_14-35-54.png
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,520
    or you can use

    Code (vb):
    Sub test()
      ActiveWorkbook.Worksheets(Sheet1.Name).Select
    End Sub
  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    630
    Dear Sir @Hui Thanks for response,

    I do as per your guide line...
    but
    no result, same error But "Run Time Error 429", "Activex Component Can't Create Object"

    same result.png

    Is there mistake in my office installation or require repair it?
    Excel 2016 64 bit, Win-7, 64 bit ,8 GB ram.

    please guide.

    regards,

    Chirag Raval
  5. Belleke

    Belleke Active Member

    Messages:
    434
    Try
    Code (vb):
    Sub test()
      ThisWorkbook.Worksheets("Sheet1").Select
    End Sub
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    630
    Dear @Belleke ,

    Thanks for your help... I already done sheet name approach.

    but I just want to reference sheet as its code name which by some reason can not reference in my system. there are seems some mistake in reference or installation in my system or some other reason.

    I also study below linked matters for search solutions.

    (1)https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename

    (2)http://www.cpearson.com/excel/RenameProblems.aspx

    (3) http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
    (4) https://www.wiseowl.co.uk/blog/s112/microsoft-excel-vba-worksheet-sheet-name-codename.htm

    (5) https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-codename-property-excel

    but can not success.

    Regards,

    Chirag Raval
  7. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !

    As a reminder, a direct CodeName like Sheet1 works only
    for the workbook where is located the procedure and
    not on any other workbook even if it is active !

    From your picture #5 :
    ActiveWorkbook.Sheet1 is not good
    as a CodeName is not referenced within any workbook object !
    (As it is referenced at the project level only …)

    ActiveWorkbook.Sheets(Sheet1) is not good either
    as Sheet1 is an object and the collection Sheets can be referenced
    only by a numeric Index or a Name …

    So if the active workbook is not the workbook containing the procedure
    Hui's code ActiveWorkbook.Worksheets(Sheet1.Name) may work
    if a worksheet of the active workbook has the same name
    of the worksheet object Sheet1 of the code workbook …
    Chirag R Raval likes this.
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    As a CodeName directly works only for a worksheet of the workbook
    where is located the procedure so for a different workbook
    its worksheets must be scanned for the desired CodeName :​
    Code (vb):
    Function GetSheetByCode(Wb As Workbook, CODE$) As Worksheet
             Dim Ws As Worksheet
        For Each Ws In Wb.Worksheets
              If Ws.CodeName = CODE Then Set GetSheetByCode = Ws: Exit For
        Next
    End Function
    Do you like it ? So thanks to click on bottom right Like !
    Chirag R Raval likes this.
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    630
    Dear Sir @Marc L,

    Really I feel Dizziness....
    reply before your valuable & full of precise knowledge tip.
    I just study the below link

    (1) http://www.vbaexpress.com/forum/sho...ror-429-ActiveX-component-can-t-create-object

    (2) http://www.fixyourerrors.com/how-can-i-get-rid-of-runtime-error-429/

    (3) https://stackoverflow.com/questions...rror-429-activex-component-cant-create-object

    (4) https://support.microsoft.com/en-in...ror-429-when-you-automate-office-applications

    when start study above links, I starting feeling why I take interest in VBA?

    but after your strait forward and never seen before answer I feel that why your wards (answers) not found anywhere on the web?

    yes you are right If I put Sir @Hui's code in workbook object , Run and its work

    you are right.png


    below as your final shot is great as learning point.

    As a reminder, a direct CodeName like Sheet1 works only
    for the workbook where is located the procedure and
    not on any other workbook even if it is active !

    In your post no 8, how to use your function in real world?
    (how to write in sheet or in sub to achieve sheet code name)

    May be I must be repeated study this thread many times to understand this concept that's look never described before. I wander that from where this found? in Vba inner help or on the web world?

    Regards,

    Chirag Raval
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    630
    Dear Sir @Marc L,

    I test your function as per below & its work...

    test.png

    but I have question in my mind that if I must have put name of the sheet "Sheet3" as string anywhere in the sub which use your function then what is the benefit over that i can direct reference by sheet name like "Activeworkbook.worksheet("Sheet3").select Or by index number as sheets(3).select?

    Or may be I take wrongly your function in my sub?

    please guide.

    Regards,

    Chirag Raval
  11. Nightrider

    Nightrider New Member

    Messages:
    3
    To activate a Sheet by it's Codename I'm using

    Code (vb):

    ThisWorkbook.VBProject.VBComponents(CODENAME).Activate
     
    Regards
    /Willy
  12. Debaser

    Debaser Active Member

    Messages:
    418
    Just FYI, you can use the codename of a sheet in another workbook as long as you have set a reference to that workbook's project (to do so you need to have renamed it as something other than VBAProject). You can then use the syntax projectname.codename to access a given sheet.
    Marc L likes this.
  13. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    As here it's not on ThisWorkbook but on another workbook !

    Using VBComponents should be my second point
    but as an Excel option as to be checked in the macro security
    options than often beginners do not found …
    I tested a bit in a hurry on my side before posting and it failed
    so maybe it's from my old Excel 2003 version
    but if you have time to share a code …
  14. Debaser

    Debaser Active Member

    Messages:
    418
    The code is the same except for adding the project name. So if you set a reference to a workbook whose project is named pjExample, you would use pjExample.Sheet1 instead of just Sheet1 in the code. It works in 2003 too.
  15. Nightrider

    Nightrider New Member

    Messages:
    3
  16. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    As the function GetSheetByCode does not work with a sheet
    name but with a CodeName as you can see in its code !
    As this function returns a worksheet object
    so just use a worksheet object variable :​
    Code (vb):
           Dim Sh As Worksheet
           Set Sh = GetSheetByCode(ActiveWorkbook, "Sheet3")
        If Not Sh Is Nothing Then

           Set Sh = Nothing
        End If
  17. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Debaser, I don't know why but it fails on my end at the project level
    and like I already met the same issue on more recent versions
    I prefer to go with the easy function way …

    Edit : in fact see post 22 …
  18. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    As you can see :
    From this link the same « pitfall » exists referencing a worksheet
    by its name if that worksheet was deleted or even just renamed … :rolleyes:
  19. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Chirag, a tip to access a worksheet of a different workbook
    than ThisWorkbook is if this worksheet has a named range
    via its Parent property like for example
    the active workbook has a range named "Headers" :

    ActiveWorkbook.Names("Headers").RefersToRange.Parent

    Or via the Worksheet property :

    ActiveWorkbook.Names("Headers").RefersToRange.Worksheet
  20. Nightrider

    Nightrider New Member

    Messages:
    3
    The Pitfall will always be there if that worksheet was deleted or even just renamed (external or internal)

    So why not to check if a worksheet exists in an external workbook by it's codename and - if so - access the worksheet by its name?

    So you check the worksheet exists and enum the corresponding name to access the sheet.

    Code (vb):

    Option Explicit

    Function GetSheetFromCodeName(oWB As Workbook, sCodename As String) As Object

    Dim oSht As Object

    For Each oSht In oWB.Sheets

    If oSht.CodeName = sCodename Then
    Set GetSheetFromCodeName = oSht
    Exit For
    End If

    Next oSht

    End Function

    Sub Test()

    Dim oSht As Object

    Set oSht = GetSheetFromCodeName(ActiveWorkbook, "Sheet3")

    If Not oSht Is Nothing Then
    '....
    End If

    End Sub
     

    just my 2ct.

    /Willy
  21. Marc L

    Marc L Excel Ninja

    Messages:
    4,253


    What I have already shown in post #16 …​
  22. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Ok it works now … :rolleyes:

    This method is the one in case of a permanent need
    to link another workbook to ThisWorkbook …

    But in case of consolidating a particular CodeName from
    workbooks in a folder to a main workbook, the function is the way …
    (Or just for people who don't understand how to rename and reference.)

    So now Chirag you have the choice between 3 ways !
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    630
    Dear Sirs & Madam's,

    First I want thank you to you all for your kind effort regarding help in this thread.

    Really I don't know how tedious to ref. Sheet by its code name.whole universe just guide simply
    Workbook.sheet1.doAnything but I realise now
    That this code must be reside in that wbk. You cannot get newly created unsaved workbook's sheet
    From it"'s code name through code from personnel.xlsb. or workbook must be saved & ref it's sheet by its
    Project name.

    On which limitation Microsoft stop? & why Microsoft miss which can be design simple as acess any object's child object?

    My aim to ref. Sheet (regardless any it''s sheet name.) By it's. Code name.

    I need to re-sttudy your all's effort to Lear. The things.

    Hope there are some another aspect regarding this.

    Regards,

    Chirag Raval
  24. Debaser

    Debaser Active Member

    Messages:
    418
    Marc already gave you a function that will allow you to find any sheet, anywhere, using its codename, but why would you need it for a newly created sheet?
  25. Chirag R Raval

    Chirag R Raval Member

    Messages:
    630
    Dear Sir @Debaser ,

    Yes point to be noted...you are right ,because by default newly created
    file's sheet's code name is "Sheet1", "Sheet2" we can reference those sheets
    by its sheet name or sheet's index no , but in case you want to access newly created file's sheet by its code name then?

    hope there are also some solution.
    Regards,

    Chirag Raval

Share This Page