Can I Delete A Module On Exit?

[Jeff W.]

I have a work book that gets copied and renamed by another application
and when it runs it automatically fills with data, and I would like to make
it so after you close and reopen, it doesnt try to auto run again.

Is this possible? I guess I want to delete all autorun macros on exit?


Thanks

Jeff W.

 

[Bob Phillips]

Dim vbMod As Object

Set vbMod = ActiveWorkbook.VBProject.VBComponents(moduleName)
ThisWorkbook.VBProject.VBComponents.Remove vbMod


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jeff W.]

Bob, I'm not sure I have put this in correctly,
I get a compile error in hidden module

I have an auto run macro that calls another sub to run and
I have put this at the bottom of the second sub


not sure if this is right, but it doesnt want to work.

the debugger hanfs on the SET part of the additional code

<Jeff>

 

[Guest]

Bob's code is correct but perhaps this will help a bit.

http://www.cpearson.com/excel/vbe.aspx

Also note that you are probably using before_close as your event. That event
fires When you hit the close button. If you cancel the close at that time
then the code has already executed and the spread sheet will remain open but
the module will be removed...

 

[Myrna Larson]

Did you replace the placeholder modulename with the actual name of the module
-- in quotes if it's a literal ??

 

[Jeff W.]

Jim, I guess the trouble is I'm not an experienced vba programmer
so I still have troubles with this even when ?I cut and past the code
from the link.

Maybe, I am over thinking this and there may really be a better solution
this is what I have that will run my macros automatically on open

How would it be if we could make this conditional on if anything is in
cell A11 as far as whether the macro run or not?

---------------------------------------
Private Sub auto1()
With ActiveWorkbook
.RunAutoMacros xlAutoOpen
.Open
End With
End Sub

'Create the Tool list Sheet
Sub auto_open()
---------------------------------------

Because once this has run there will be something in there, and no need to
run again..

Does this sound like a better solution

Regards,

<Jeff>

 

[Jeff W.]

Even if I put thisw in Module2 and run it from the macro menu
it stops on the first line and says; (Compile Error)

Sub DeleteAllVBACode()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub



Maybe this would be easier if I could just have something like;

if (A11) doesnt equal blank then run sub
else dont run sub

I know this is all wrong on syntax but its what should happen

Does this make more sence?

<Jeff>

 

[Guest]

The code you posted is a little too abreviated so I will have to be kind of
general but you could try something like this...

Sub auto_open()
if sheets("Sheet1").Range("A11").Value = "" then
msgbox "do your stuff"
else
msgbox "Stuff was done"
endif
end sub

 

[Jeff W.]

Jim, That is perfect

Thanks

Jeff

The code you posted is a little too abreviated so I will have to be kind
of
general but you could try something like this...

Sub auto_open()
if sheets("Sheet1").Range("A11").Value = "" then
msgbox "do your stuff"
else
msgbox "Stuff was done"
endif
end sub