programming to the vbe question

[Gary Keramidas]

i used chip pearson's method to add, remove and test for a modules existence.
was working fine 2 days ago, now it doesn't work.
http://www.cpearson.com/excel/vbe.htm

keep getting method vbproject of object _ workbook failed, even if i just try to
add a module
i still have the "Microsoft Visual Basic For Applications Extensibility" option
in references checked.


Sub AddModule()
Dim VBComp As VBComponent

Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "Aopen"
Application.Visible = True
End Sub


was wondering if anyone had any ideas.

 

[Tom Ogilvy]

do you still have a reference to the Extensibility Library within scope?

 

[Gary Keramidas]

sorry, tom, not sure what you mean by scope?

 

[Tom Ogilvy]

Does that particular workbook/project (the one containing the code) have a
reference to the extensibility library. When looking at Tools=>References in
the VBE, what you see is specific to the active project at the time you do
it.

 

[Gary Keramidas]

yes it does. that's why i don't understand why it stopped working.

 

[Tom Ogilvy]

If it worked before and you have changed nothing (no duplicate module name or
anything else with the name you are trying to use for this module), then it
might be time to use Rob Bovey's code cleaner utility

http://www.appspro.com go to free downloads.


(a hard coded module name in code is suspect).

 

[Gary Keramidas]

i had code that added the module, tested for the existence of the module and
deleted the module. they all worked and now all fail on the set vbcomp line.

even tried it on another pc, in a blank workbook and still didn't work.

i don't know what happened. i can try the download and see what happens.

thanks

 

[Gary Keramidas]

found out the problem, don't know what unchecked the option, but
"Trust Access To Visual Basic Project" was not enabled
under macro security.

i know it was because the code was working.

thanks and sorry for the bother.

 

[Gary Keramidas]

i just saved my office settings again in case anything like that happens, i can
just restore my settings and not worry about it,

anyway, the reason i used the vbe code was to use a checkbox on the main menu
user form. it it's checked, it creates a module and inserts auto open code so
the menu opens when the workbook does.

when the user unchecks it, it deletes the module with the auto open code.

just thought i'd share that.

 

[Tom Ogilvy]

That will also prevent you from protecting your project if that is a
consideration.

It might be easier just to have the code and have its first statement be a
check of whether it should continue running or not. Just a thought.

 

[Gary Keramidas]

that might be a consideration, thanks for pointing that out

 

[Gary Keramidas]

i changed this behavior, tom.

i have a hidden sheet i use for behind the scenes stuff. i used a cell for a
true or false entry depending on the state of the checkbox.
i also changed the label value, click to disable auto load and click to disable
auto load depending on the state of the checkbox

then in the auto_open sub, i look at the value of that cell and if it's true,
the menu loads and if it's false, it doesn't.

is this basically what you were saying?

thanks again

 

[Tom Ogilvy]

Yes.

--
Regards,
Tom Ogilvy

i changed this behavior, tom.

i have a hidden sheet i use for behind the scenes stuff. i used a cell for
a true or false entry depending on the state of the checkbox.
i also changed the label value, click to disable auto load and click to
disable auto load depending on the state of the checkbox

then in the auto_open sub, i look at the value of that cell and if it's
true, the menu loads and if it's false, it doesn't.

is this basically what you were saying?

thanks again