|
|
| Next: Word Art |
| Author |
Message |
Don
External
Since: Jun 16, 2004
Posts: 301
|
 Posted: Fri May 01, 2009 11:56 am Post subject: Break out of a loop in Excel2007?
Archived from groups: microsoft>public>excel>misc (more info?) |
|
|
I created some code that gets Excel in a loop when I open the file and I need
to know how to break out? It is in the Sheet option using the
Application.EnableEvents in Office 2007 and will not let me break out of the
loop and I have to close doing a cntl+alt+delete and hard crashing Excel.
I have updated I think all the files, but if I have some not updated and
people move to Office 2007, I would like another way to get out?
Any help?
see code below from the sheet
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Range(Cells(6, 6), Cells(7, 7)).Select
Selection.ClearContents
Application.EnableEvents = False
End Sub |
|
| Back to top |
|
 |
Per Jessen
External
Since: Jan 11, 2009
Posts: 13
|
Posted: Fri May 01, 2009 12:34 pm Post subject: Re: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
Hi
You have to set EnableEvents = False before anything else, and set
=true again later:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range(Cells(6, 6), Cells(7, 7)).ClearContents
Application.EnableEvents = True
End Sub
Best regards,
Per
On 1 Maj, 20:56, Don <D....DeleteThis@discussions.microsoft.com> wrote:
> I created some code that gets Excel in a loop when I open the file and I need
> to know how to break out? It is in the Sheet option using the
> Application.EnableEvents in Office 2007 and will not let me break out of the
> loop and I have to close doing a cntl+alt+delete and hard crashing Excel.
>
> I have updated I think all the files, but if I have some not updated and
> people move to Office 2007, I would like another way to get out?
>
> Any help?
>
> see code below from the sheet
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Range(Cells(6, 6), Cells(7, 7)).Select
> Selection.ClearContents
> Application.EnableEvents = False
> End Sub |
|
| Back to top |
|
|
Don
External
Since: Jun 16, 2004
Posts: 301
|
Posted: Fri May 01, 2009 2:10 pm Post subject: Re: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
thanks,
but if I am in a loop, how can I get out??
I will change going forward.
"Per Jessen" wrote:
> Hi
>
> You have to set EnableEvents = False before anything else, and set
> =true again later:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents = False
> Range(Cells(6, 6), Cells(7, 7)).ClearContents
> Application.EnableEvents = True
> End Sub
>
> Best regards,
> Per
>
> On 1 Maj, 20:56, Don <D....DeleteThis@discussions.microsoft.com> wrote:
> > I created some code that gets Excel in a loop when I open the file and I need
> > to know how to break out? It is in the Sheet option using the
> > Application.EnableEvents in Office 2007 and will not let me break out of the
> > loop and I have to close doing a cntl+alt+delete and hard crashing Excel.
> >
> > I have updated I think all the files, but if I have some not updated and
> > people move to Office 2007, I would like another way to get out?
> >
> > Any help?
> >
> > see code below from the sheet
> >
> > Option Explicit
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Range(Cells(6, 6), Cells(7, 7)).Select
> > Selection.ClearContents
> > Application.EnableEvents = False
> > End Sub
>
> |
|
| Back to top |
|
|
Don Guillett
External
Since: Jan 04, 2006
Posts: 2988
|
Posted: Fri May 01, 2009 2:26 pm Post subject: Re: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
??
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1 DeleteThis @austin.rr.com
"Don" <Don DeleteThis @discussions.microsoft.com> wrote in message
news:3119C7CD-ED8F-49C8-8F8F-35759EE74870@microsoft.com...
>I created some code that gets Excel in a loop when I open the file and I
>need
> to know how to break out? It is in the Sheet option using the
> Application.EnableEvents in Office 2007 and will not let me break out of
> the
> loop and I have to close doing a cntl+alt+delete and hard crashing Excel.
>
> I have updated I think all the files, but if I have some not updated and
> people move to Office 2007, I would like another way to get out?
>
> Any help?
>
> see code below from the sheet
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Range(Cells(6, 6), Cells(7, 7)).Select
> Selection.ClearContents
> Application.EnableEvents = False
> End Sub
>
> |
|
| Back to top |
|
|
Bob Umlas
External
Since: Dec 08, 2004
Posts: 51
|
Posted: Fri May 01, 2009 3:32 pm Post subject: Re: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
Change to:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("F6:G7")ClearContents
Application.EnableEvents = True
End Sub
"Don" <Don.TakeThisOut@discussions.microsoft.com> wrote in message
news:3119C7CD-ED8F-49C8-8F8F-35759EE74870@microsoft.com...
>I created some code that gets Excel in a loop when I open the file and I
>need
> to know how to break out? It is in the Sheet option using the
> Application.EnableEvents in Office 2007 and will not let me break out of
> the
> loop and I have to close doing a cntl+alt+delete and hard crashing Excel.
>
> I have updated I think all the files, but if I have some not updated and
> people move to Office 2007, I would like another way to get out?
>
> Any help?
>
> see code below from the sheet
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Range(Cells(6, 6), Cells(7, 7)).Select
> Selection.ClearContents
> Application.EnableEvents = False
> End Sub
>
> |
|
| Back to top |
|
|
Don Guillett
External
Since: Jan 04, 2006
Posts: 2988
|
Posted: Fri May 01, 2009 5:02 pm Post subject: Re: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
That's why I put ??. Where is the loop?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.DeleteThis@austin.rr.com
"Don" <Don.DeleteThis@discussions.microsoft.com> wrote in message
news:487FA719-9A72-4D8E-82B6-94689978F64A@microsoft.com...
>
> thanks,
>
> but if I am in a loop, how can I get out??
> I will change going forward.
>
> "Per Jessen" wrote:
>
>> Hi
>>
>> You have to set EnableEvents = False before anything else, and set
>> =true again later:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Application.EnableEvents = False
>> Range(Cells(6, 6), Cells(7, 7)).ClearContents
>> Application.EnableEvents = True
>> End Sub
>>
>> Best regards,
>> Per
>>
>> On 1 Maj, 20:56, Don <D....DeleteThis@discussions.microsoft.com> wrote:
>> > I created some code that gets Excel in a loop when I open the file and
>> > I need
>> > to know how to break out? It is in the Sheet option using the
>> > Application.EnableEvents in Office 2007 and will not let me break out
>> > of the
>> > loop and I have to close doing a cntl+alt+delete and hard crashing
>> > Excel.
>> >
>> > I have updated I think all the files, but if I have some not updated
>> > and
>> > people move to Office 2007, I would like another way to get out?
>> >
>> > Any help?
>> >
>> > see code below from the sheet
>> >
>> > Option Explicit
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > Range(Cells(6, 6), Cells(7, 7)).Select
>> > Selection.ClearContents
>> > Application.EnableEvents = False
>> > End Sub
>>
>> |
|
| Back to top |
|
|
Jacob Skaria
External
Since: Mar 04, 2009
Posts: 441
|
Posted: Sat May 02, 2009 1:36 am Post subject: RE: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
Hi Don
You can use 'Ctrl'+'Pause/Break' buton to break a running code...
You can even give it a try......
If this post helps click Yes
---------------
Jacob Skaria
"Don" wrote:
> I created some code that gets Excel in a loop when I open the file and I need
> to know how to break out? It is in the Sheet option using the
> Application.EnableEvents in Office 2007 and will not let me break out of the
> loop and I have to close doing a cntl+alt+delete and hard crashing Excel.
>
> I have updated I think all the files, but if I have some not updated and
> people move to Office 2007, I would like another way to get out?
>
> Any help?
>
> see code below from the sheet
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Range(Cells(6, 6), Cells(7, 7)).Select
> Selection.ClearContents
> Application.EnableEvents = False
> End Sub
>
> |
|
| Back to top |
|
|
Don
External
Since: Jun 16, 2004
Posts: 301
|
Posted: Mon May 04, 2009 1:47 pm Post subject: Re: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
sorry, the loop is because enable events is turned on and if there is an
update on the page, it will start from the top again. Should be turning it
to off first so it will go through once but it gets stuck in 2007 and will
not let you break out
"Don Guillett" wrote:
> That's why I put ??. Where is the loop?
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1 RemoveThis @austin.rr.com
> "Don" <Don RemoveThis @discussions.microsoft.com> wrote in message
> news:487FA719-9A72-4D8E-82B6-94689978F64A@microsoft.com...
> >
> > thanks,
> >
> > but if I am in a loop, how can I get out??
> > I will change going forward.
> >
> > "Per Jessen" wrote:
> >
> >> Hi
> >>
> >> You have to set EnableEvents = False before anything else, and set
> >> =true again later:
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> Application.EnableEvents = False
> >> Range(Cells(6, 6), Cells(7, 7)).ClearContents
> >> Application.EnableEvents = True
> >> End Sub
> >>
> >> Best regards,
> >> Per
> >>
> >> On 1 Maj, 20:56, Don <D... RemoveThis @discussions.microsoft.com> wrote:
> >> > I created some code that gets Excel in a loop when I open the file and
> >> > I need
> >> > to know how to break out? It is in the Sheet option using the
> >> > Application.EnableEvents in Office 2007 and will not let me break out
> >> > of the
> >> > loop and I have to close doing a cntl+alt+delete and hard crashing
> >> > Excel.
> >> >
> >> > I have updated I think all the files, but if I have some not updated
> >> > and
> >> > people move to Office 2007, I would like another way to get out?
> >> >
> >> > Any help?
> >> >
> >> > see code below from the sheet
> >> >
> >> > Option Explicit
> >> >
> >> > Private Sub Worksheet_Change(ByVal Target As Range)
> >> > Range(Cells(6, 6), Cells(7, 7)).Select
> >> > Selection.ClearContents
> >> > Application.EnableEvents = False
> >> > End Sub
> >>
> >>
>
> |
|
| Back to top |
|
|
Don
External
Since: Jun 16, 2004
Posts: 301
|
Posted: Mon May 04, 2009 1:48 pm Post subject: RE: Break out of a loop in Excel2007? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
sorry, tried that an no luck.
"Jacob Skaria" wrote:
> Hi Don
>
> You can use 'Ctrl'+'Pause/Break' buton to break a running code...
> You can even give it a try......
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Don" wrote:
>
> > I created some code that gets Excel in a loop when I open the file and I need
> > to know how to break out? It is in the Sheet option using the
> > Application.EnableEvents in Office 2007 and will not let me break out of the
> > loop and I have to close doing a cntl+alt+delete and hard crashing Excel.
> >
> > I have updated I think all the files, but if I have some not updated and
> > people move to Office 2007, I would like another way to get out?
> >
> > Any help?
> >
> > see code below from the sheet
> >
> > Option Explicit
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Range(Cells(6, 6), Cells(7, 7)).Select
> > Selection.ClearContents
> > Application.EnableEvents = False
> > End Sub
> >
> > |
|
| Back to top |
|
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
| |
|
|
Microsoft Office