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.

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." <(E-Mail Removed)> wrote in message
news:OC3hkn$(E-Mail Removed)...
>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, 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" <(E-Mail Removed)> wrote in message
news:Oel6iu$(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
> news:OC3hkn$(E-Mail Removed)...
>>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'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" <(E-Mail Removed)> wrote in message
> news:Oel6iu$(E-Mail Removed)...
> > 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." <(E-Mail Removed)> wrote in message
> > news:OC3hkn$(E-Mail Removed)...
> >>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.
> >>
> >>
> >>
> >>
> >
> >
>
>
>

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." <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>news:Oel6iu$(E-Mail Removed)...
>> 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." <(E-Mail Removed)> wrote in message
>> news:OC3hkn$(E-Mail Removed)...
>>>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.
>>>
>>>
>>>
>>>
>>
>>
>

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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:5701B23C-9EFE-489D-AC79-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:Oel6iu$(E-Mail Removed)...
>> > 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." <(E-Mail Removed)> wrote in message
>> > news:OC3hkn$(E-Mail Removed)...
>> >>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.
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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." <(E-Mail Removed)>
> 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" <(E-Mail Removed)> wrote in message
>>news:Oel6iu$(E-Mail Removed)...
>>> 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." <(E-Mail Removed)> wrote in message
>>> news:OC3hkn$(E-Mail Removed)...
>>>>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.
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>

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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
> news:5701B23C-9EFE-489D-AC79-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:Oel6iu$(E-Mail Removed)...
> >> > 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." <(E-Mail Removed)> wrote in message
> >> > news:OC3hkn$(E-Mail Removed)...
> >> >>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.
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Jim, That is perfect

Thanks

Jeff

"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:35B26C6C-9A14-43D0-8B7F-(E-Mail Removed)...
> 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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in
>> message
>> news:5701B23C-9EFE-489D-AC79-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:Oel6iu$(E-Mail Removed)...
>> >> > 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." <(E-Mail Removed)> wrote in message
>> >> > news:OC3hkn$(E-Mail Removed)...
>> >> >>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.
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>