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

  • 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

C

Chirag R Raval

Member
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: 
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:
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
 
or you can use

Code: 
Sub test()
  ActiveWorkbook.Worksheets(Sheet1.Name).Select
End Sub
 
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
 
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
 
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 …
​
 
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: 
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 !
​
 
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
 
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
 
To activate a Sheet by it's Codename I'm using

Code: 
ThisWorkbook.VBProject.VBComponents(CODENAME).Activate

Regards
/Willy
 
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.
 
Nightrider said:
To activate a Sheet by it's Codename I'm using

Code: 
ThisWorkbook.VBProject.VBComponents(CODENAME).Activate
Click to expand...
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 …
​
Debaser said:
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.
Click to expand...
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 …
​
 
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.
 
Chirag R Raval said:
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?
Click to expand...
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: 
       Dim Sh As Worksheet
       Set Sh = GetSheetByCode(ActiveWorkbook, "Sheet3")
    If Not Sh Is Nothing Then

       Set Sh = Nothing
    End If
 
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 …
​
 
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 …
​
 
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: 
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
 
Debaser said:
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.
Click to expand...
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 !
​
 
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
 
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?
 
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
 
You must log in or register to reply here.
Back
Top