Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
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." wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
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 "Bob Phillips" wrote in message ... 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." wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
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... -- HTH... Jim Thomlinson "Jeff W." wrote: 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 "Bob Phillips" wrote in message ... 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." wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
Did you replace the placeholder modulename with the actual name of the module
-- in quotes if it's a literal ?? On Wed, 31 Oct 2007 15:35:51 -0500, "Jeff W." wrote: 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 "Bob Phillips" wrote in message ... 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." wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
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 "Jim Thomlinson" wrote in message ... 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... -- HTH... Jim Thomlinson "Jeff W." wrote: 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 "Bob Phillips" wrote in message ... 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." wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
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 "Myrna Larson" wrote in message ... Did you replace the placeholder modulename with the actual name of the module -- in quotes if it's a literal ?? On Wed, 31 Oct 2007 15:35:51 -0500, "Jeff W." wrote: 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 "Bob Phillips" wrote in message ... 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." wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
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 -- HTH... Jim Thomlinson "Jeff W." wrote: 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 "Jim Thomlinson" wrote in message ... 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... -- HTH... Jim Thomlinson "Jeff W." wrote: 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 "Bob Phillips" wrote in message ... 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." wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Delete A Module On Exit?
Jim, That is perfect
Thanks Jeff "Jim Thomlinson" wrote in message ... 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 -- HTH... Jim Thomlinson "Jeff W." wrote: 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 "Jim Thomlinson" wrote in message ... 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... -- HTH... Jim Thomlinson "Jeff W." wrote: 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 "Bob Phillips" wrote in message ... 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." wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming |