VBAProject

[Daniel]

Hello, before I terminate the formating of my file, I want to change the
name of the vbaProject. using the following code:

'name the vbaProject
Application.VBE.ActiveVBProject.Name = "Consolidated_Monthly_FB"

I am not adding any module or reference to the file. I am getting Run-time
error 50289, can't perform operation since the project is protected.
Please, be assure that the project is not protected at this point. If this
can help, when I open the vba editor, I have no error, it terminates as it
should be.
Further I also added on the template (initial unformatted file) the VBA
Extensibility 5.3, just in case, but to no avail.
Is there a solution?

Daniel

 

[Guest]

worked fine for me

Sub altername()
Application.VBE.ActiveVBProject.Name = "Consolidated_Monthly_FB"
End Sub

Make sure that the code is in a standard module.
All I did was open a new workbook, add a module, add the sub name and copied
your line of code. No issues.

 

[DM Unseen]

Daniel,

if you protect your VBA code you cannot set any VBA project properties,
unless you first unlock the VBA project manually from the VBA editor
(this is what I suspect you are doing).

BTW your VBA projectname should always be *static*, since it is an
object reference as well!! What you want you should encode in a cell or
in the file name or in a custom document property, but not change the
VBA project name at runtime!

DM Unseen

 

[Norman Jones]

Hi DM,

if you protect your VBA code you cannot set any VBA project properties,
unless you first unlock the VBA project manually from the VBA editor
(this is what I suspect you are doing).

But Daniel said:

 

[DM Unseen]

It should not just be "not protected at this point"

To let this alway work it should *never* be protected (also just
unlocking your project for editing/debugging does not count as removing
the protection). This is why this cannot work (unless he doesn't want
to protect the workbook at all, but that would be a severe limitation
given it has code). It might also be that the code itsself gives him
trouble though. This is the correct code (which I do not condone in any
way I suspect he is using it

Sub altername()
Thisworkbook.VBProject .Name = "Consolidated_Monthly_FB"
End Sub


DM Unseen

 

[Guest]

Hi Daniel

I presume you're creating and formatting a workbook from code in another
workbook, and it is the workbook you're formatting that you want to change
the project of.

The reason the code you posted is not working is because the
VBE.ActiveVBProject is just the VBProject that's selected in the VBE. It is
not the VBProject of the Workbook selected in the Excel window, nor of the
project running the code. If the VBE hasn't been shown in a session, the user
hasn't had a chance to select a VBProject, so asking for the ActiveVBProject
fails.

To get from a workbook to it's project, you use the VBProject property of
the workbook, so if you have an object variable pointing to your workbook,
you can do it like the following:

Dim wkbNewWorkbook

Set wkbNewWorkbook = Workbooks.Add("c:\thetemplate.xlt")

'Do your formatting

'Name the VBProject:
wkbNewWorkbook.VBProject.Name = "TheProjectName"


If the workbook you want to set the project for is the active one in Excel,
you could use:
ActiveWorkbook.VBProject.Name = "TheProjectName"

--
Regards

Stephen Bullen
Take your Excel development to the highest levels
with "Professional Excel Development",
www.oaltd.co.uk/ProExcelDev