Solved: Sheet name in formula instead of caption

[anandbohra]

Hi friends
In programming sense each object has 2 identifier
1st one is name (accessible to programmer only)
2nd one is caption ( which user see)

say u put one command button mentioning click me
so command1 will be name & click me will be caption
same way in excel sheet name june
so we can use
[VBA]sheet1.select
or
sheets("June").select[/VBA]

the difference is if we use 2nd code our macro throws error if someone changes name of sheet june to july or whatever
but if we use first line then irrespective of sheet name our code will work

if u are clear than i start with my problem (the above explanation was to differenctiate name & caption property)

now can the same differentitaion can be applicable in vba formula

like my recorded macro line is
[VBA]
Sheets("Summ").Select
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Deliveries!C1:C17,10,0)"[/VBA]


now in above code I want modification as

[VBA]
' the below lines makes me tension free now user can change name
Sheet1.select' Sheets("Summ").Select
Range("R2").Select
' now pl tell me code for the below line as if user changes sheet name Deliveries than my macro gets stuck over that

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Deliveries!C1:C17,10,0)"[/VBA]


there is any way by which i can substitute Sheet Caption Deliveries with its programming code like sheet3

so that user change the name from deliveries to say XYZ but my code will run properly

[Bob Phillips]

same way in excel sheet name june
so we can use
[VBA]sheet1.select
or
sheets("June").select[/VBA]

the difference is if we use 2nd code our macro throws error if someone changes name of sheet june to july or whatever
but if we use first line then irrespective of sheet name our code will work

if u are clear than i start with my problem (the above explanation was to differenctiate name & caption property)

With worksheets, they are called name and codename, not caption. The codename is the VBA internal name.

now can the same differentitaion can be applicable in vba
formula

...

[VBA]
' the below lines makes me tension free now user can change name
Sheet1.select' Sheets("Summ").Select
Range("R2").Select
' now pl tell me code for the below line as if user changes sheet name Deliveries than my macro gets stuck over that

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Deliveries!C1:C17,10,0)"[/VBA]


there is any way by which i can substitute Sheet Caption Deliveries with its programming code like sheet3

so that user change the name from deliveries to say XYZ but my code will run properly

[vba]

Dim SheetName As String

With ActiveWorkbook.VBProject
SheetName = CStr(.VBComponents("Sheet3").Properties("Name"))
End With

Sheet1.Range("R2").FormulaR1C1 = "=VLOOKUP(RC[-17],'" & SheetName & "'!C1:C17,10,0)"
[/vba]

[f2e4]

Why don't you use something like this:

[vba]
'THIS STORES THE ORIGINAL ACTIVE SHEETS NAME

Dim wks As String
wks = ActiveSheet.Name

'THIS ADDS STORED SHEETNAME TO YOUR FUNCTION

ActiveCell.FormulaR1C1 = Replace("=VLOOKUP(RC[-17],XXX!C1:C17,10,0)", "XXX", wks)
[/vba]

[Bob Phillips]

and if its not the activesheet?

[f2e4]

then he can use your code

i just gave him the other option

[anandbohra]

[vba]

Dim SheetName As String

With ActiveWorkbook.VBProject
SheetName = CStr(.VBComponents("Sheet3").Properties("Name"))
End With

Sheet1.Range("R2").FormulaR1C1 = "=VLOOKUP(RC[-17],'" & SheetName & "'!C1:C17,10,0)"
[/vba]

Thank you sir

This is exactly what I wanted

[anandbohra]

Hi I made the changes in my project & it worked perfectly.

when I put the VBA Project password to hide coding execution gives me error

Run-Time error '50289'
Can't perform operation since the project is protected


error comes in this line

[VBA]Dim Shtnm21, shtnm26, shtnm25 As String
With ActiveWorkbook.VBProject
Shtnm21 = CStr(.VBComponents("Sheet21").Properties("Name")) 'Declared as Dividend
shtnm26 = CStr(.VBComponents("Sheet26").Properties("Name")) 'Delcared as PreBhav-Fut
shtnm25 = CStr(.VBComponents("Sheet25").Properties("Name")) 'Delcared as CurBhav-Fut

End With[/VBA]

if I am taking through XLD given code it works perfectly in open source code project. but the moment I lock the project it gives error

so there is any code which unlock my VBA project & then lock it after completion of code.

I am having code its my own working (means I am not asking for code breaking)


pl help

[anandbohra]

Hi while surfing for my above query I found this web page

http://www.pcreview.co.uk/forums/thread-989191.php

this will open the project as per given password
but it wont close the same.

means I put the given code in workbook open event
but after execution of project one can easily go & copy my coding


so I want code which open VBA project temporarily then execute macro then close the same afterwords like we do for unprotecting sheets

[Simon Lloyd]

You need to set an reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3.

Next, you need to enable access to the VBA Project, go the Tools menu (in Excel, not in the VBA editor), choose Macros and then the Security item. In that dialog, click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

[Bob Phillips]

You need to set an reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3.

I have never found a need to do that. Everyone says that you should, but I always find that it works fine without, probably because VBA has an implicit reference within itself.

[Simon Lloyd]

I have never found a need to do that. Everyone says that you should, but I always find that it works fine without, probably because VBA has an implicit reference within itself.

That may be Bob but i don't have the deep knowledge of the product so if i am ever messing with VB components (extremely rare!) i make sure these things are set as i had been told on other occasions where i had problems.

Doesn't hurt to "cross the T's and dot the I's"

[anandbohra]

Sir
I added the said reference & also check the Trust access to the Visual Basic Project setting but still in execution same error appears.

[Simon Lloyd]

Thats probably because the project you are manipulating must be unlocked!

[anandbohra]

Thats probably because the project you are manipulating must be unlocked!

I made the project.
then in vbaproject properties given it a password.
now as it is password protected I can not run my macro as in the begining it wants sheet name internally which XLD has given earlier in this thread.

the problem is if i do not protect the project with password it run smoothly but this will let others see my whole coding which I dont want to.

So I need some coding or refence of VBA to internally check for password (iam ready to provide if needed in coding) & execute my macro but it should not show coding to others

[Simon Lloyd]

Have your workbook create another without the VBA project in it!

[anandbohra]

sir pl refer the attached file

right now it has VBA password "test"

if u click generate result button it gives u error



then open the vba code with password test
then click the button it wont give error