escape from vba-loop

hi group,

I've got an excel-sheet containing some vba-code. There's one sub that
does some calculations in a loop which can run for some time. My users
now want to be able to escape from this calculation by pressing
<escape> or clicking a button on the sheet. But actually excel is
frozen while calculating.

What is the most common way to achieve this? I guess it must be
possible to solve this with excel-events, but I'm not very experienced
in using them.

thanks for any hints,
stephan
0
stephan0h (11)
4/28/2009 12:33:09 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1521 Views

Similar Articles

[PageSpeed] 42

You can add a "DoEvents" statement inside the loop.
Excel checks for any instructions coming from Windows when it sees that instruction.  Best not to use that on every loop as it is 
just another straw on the camels back.

Since the loop takes some time, you need some sort of progress indicator
to let users know something's happening and how long they have to wait.
The DoEvents statement can then be executed when progress is displayed.

You also need error handling code in your procedure to nicely exit the
procedure if escape is pressed.

You have some more work to do. <g>
-- 
Jim Cone
Portland, Oregon  USA



"steph" <stephan0h@yahoo.de>
wrote in message
hi group,
I've got an excel-sheet containing some vba-code. There's one sub that
does some calculations in a loop which can run for some time. My users
now want to be able to escape from this calculation by pressing
<escape> or clicking a button on the sheet. But actually excel is
frozen while calculating.
What is the most common way to achieve this? I guess it must be
possible to solve this with excel-events, but I'm not very experienced
in using them.
thanks for any hints,
stephan 

0
4/28/2009 1:50:38 PM
Hi Stephan

See CheckKey.zip at http://www.oaltd.co.uk/Excel/Default.htm

HTH. Best wishes Harald

"steph" <stephan0h@yahoo.de> wrote in message 
news:fbc0438b-8778-44ad-ac64-6c7566a838b6@n7g2000prc.googlegroups.com...
> hi group,
>
> I've got an excel-sheet containing some vba-code. There's one sub that
> does some calculations in a loop which can run for some time. My users
> now want to be able to escape from this calculation by pressing
> <escape> or clicking a button on the sheet. But actually excel is
> frozen while calculating.
>
> What is the most common way to achieve this? I guess it must be
> possible to solve this with excel-events, but I'm not very experienced
> in using them.
>
> thanks for any hints,
> stephan 

0
nospam4155 (73)
4/29/2009 4:11:51 PM
Reply:

Similar Artilces:

Who can help me with a VBA
Hello everybody, Who can make a VBA code for me??? I have an sheet called "insertsheet", in cell B8 people can fill in 1 till 10 with validation. In a other sheet called "Dataprocessing" I want row 23 is visible when B8 is 1 I want row 23 + 24 visible when B8 is 2 etc. At the same time I want the same in a sheet called "List" but then start at row 27 Who can help me. Thanks in advanced. Regards, BL One way: Put this in your insertsheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const nMAX As Long = 10 Dim n...

pop up reminder vba
Dear all, I have a query for expiry dates that gives me today expiry, that includes birthdates passport and visa renewal how can I set a vba that allows me to get a pop up once I open the access file. I'm new in vba and I don't know how to do it please help Have a look at: http://www.datastrat.com/Download/Birthday2K.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Tia" <tia.abdelkarim@gmail.com> wrote in message news:381669bf-e35a-4596-bfcc-50a118f9a5f0@m16g2000yqc.googlegroups.com... >...

List of XPath Escape Characters
In my previous post, I asked about a routine which prepares a string for an XPath query by taking care of escape characters. Unable to find a list, I'm now wondering assumign I enclose the attribute value in quotes in my XPath query, what other escape characters need to be handled aside from a quotation mark? As I understand it, an apostrophe wouldn't be a problem since I'm enclosing the string in quotes. thanks! Hi Matthew, For a list of these special characters, you may refer to: HOW TO: Locate and Replace Special Characters in an XML File with Visual Basic .NET http:/...

Accessing Query Description thru VBA
Hi, I'm working on a procedure to 'package' a query to export it to a file, email it and allow the peson on the other end to import it. I want to be able to package the query description as well - and to write it on the other end - but for the life of me, I don't see how I can read/write the description (in the properties) of the query - can anyone put me on the right track? TIA If the query has a description, you can get at it like this: CurrentDb.QueryDefs("MyQuery").Properties("Description") If there is no description, this *should* yield e...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

How to incorporate Loop feature
The below code works perfectly for my workbook containing 103 worksheets; At present it lists and numbers all my worksheets down Col A and B only. I'd like to tweek it to output to only rows 1 - 20, meaning after the first 20 A1:B20 being filled -- transfer output to Column C:D, then after 20 down (C1:D20) to E:F and so on.. Loops at this point totally confuse me,, can some one assist? TIA, Sub ListWSNames() Dim ws As Integer Worksheets("Index").Activate Cells.ClearContents Range("B1").Select For ws = 2 To Worksheets.Count Worksheets("Index").Cells(ws - 1,...

VBA
First let me send my thanks out to Pieter who has helped me a great deal on something else but that help has allowed me to get to this point, and Wolfgang who directed me to an MSDN site where I found the rest of what I needed for this one. Thanks to you all.. I have written the code below to run as a toggle from a command button and everything seems to work fine except displaying the changes to the properties of the commandbutton. Specifically the transparent property. The button should be transparent when the StartUp Options are set to secure the DB, and visible when the DB is unse...

Sharing VBA Code between Word and Access 2007
I want to share code (e.g., functions and programming notes to myself) between my Access and Word. How is that done? Not sure I'm following what you're after ... If you have code you've written in Access, referring to Access' features and functions, why would it work in Word, which has different features and functions? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. ...

Merging worksheets with VBA Code -- HELP!
Hello I need help with using a VBA code to merge data from a range of worksheets in a workbook. I have several worksheets that are formatted the same except they may have a different amount of rows of data on each spreadsheet and they are named for each of my employees. When I run the Macro, it is giving me all of the details, columns and rows from the 1st sheet only, Sonia G then it only gives me the information in colums A, B, C, E, F, G, I from the other work sheets. However, all of the worksheets have information in columns A thru AA. I used the following code for a Summary She...

Adding Description to Table in vba
Not sure which forum this belongs in: I am creating a table in order to pass the contents to Word in a mail merge. I do this because the data to be merged depends on a selection from a form. The query is: SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName, qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg, qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName, qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown, qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone, qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName, qr...

Xcellery and VBA/Macros
I am interested in collaboration with Excel files. More specifically, I need to share an Excel file with other people. Data could be entered back and forth from a number of people. Instead of email files, etc., an online collaboration tool would be useful. I stumbled upon an Excel collaboration program called Xcellery (www.xcellery.com). This program seems to fit the bill. The only problem is that my current Excel file utilizes VBA code/macros. I noticed that the Xcellery protocol is to use one of their templates or import files from a local computer. The VBA editor is password protect...

A forward loop was detected by the categorizer.
All, I'm seeing the following errors in the Event Log of my Exchange 2003 SP2 server. *********************** Type: Error Source: MSExchangeTransport Category: NDR Event ID: 3020 A non-delivery report with a status code of 5.4.6 was generated for recipient rfc822;cstockley@frametech.com (Message-ID <8787346389.810755843932@zoders.com>). Cause: A forward loop was detected by the categorizer. This is a common hosting configuration problem caused when someone uses the provisioning tool to create a contact in one organization unit and creates a user in a different organization...

Macro Loop Throws 1004 error
I wrote a loop to skip through some data (with the idea of selecting and copying it). For some reason, when i=44 and the ActiveCell = HM2, a 1004 error is thrown on the .Offset(0,i).Select method. Dim i i = 0 Do While IsEmpty(ActiveCell.Offset(0, 1)) = False ActiveCell.Offset(0, i).Select i = i + 4 Loop There is no difference in the data on worksheet in the columns before and after the HM column. It is not at the edge of the worksheet. This is the error: "Run-time error '1004' Application-defined or object-defined error" What...

Range Problem in VBA
I currently have the following VBA code so that a calendar appears in cell HG: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$H$6" Then Cancel = True Call OpenCalendar End If End Sub _____________________ This code works great and inserts the relevant date into the cell etc... Nonetheless, I want to ammend this code so that I can double click on any cell within the range H6:H1000 (as opposed to only Cell H6) and the calendar will pop up and the user can select a date and it will be entered into th...

How could I optimize this VBA project ?
Hi ! I have a little issue with a workbook : it takes wayyyy too long to update... So I have a sheet called "Map", where I must update comments on certain cells... the problem comes from the 2 For... Next statements that take a lot of time to process, because I have to activate 4 different sheets each time... I have about 150 system, and 12 planets for each, that's 1800 possibilities !!! Can I get the same result with another method ? I tought about create another sheet where I could retrieve all infos I need, but I can't figure out how to do this... Currently, I ha...

VBA & Outlook
I thought there used to be newsgroups dedicated to using VBA with Outlook. Where have they gone? What is now the recommended newsgroup for VBA/Outlook questions? MS is currently switching off the newsgroup server and moving MS groups to; http://social.answers.microsoft.com/Forums/en-US/categories "Rod" <rodrodrodrod@hotmail.com> wrote in message news:#gl7rdYBLHA.420@TK2MSFTNGP02.phx.gbl... > I thought there used to be newsgroups dedicated to using VBA with Outlook. > Where have they gone? > > What is now the recommended newsgroup for VBA/Outloo...

VBA code using if then and msgbox
I wanted to use a macro that does the following. Currently I have created a userform inventory list. In the userform I entered the item sold and the quantity sold. I wanted to somehow squeeze a macro in this userform that would do the following. If for instance there is one item remaining in inventory and I accidentally put 2 items were sold, I want the macro to check my inventory list and have a msgbox pop up saying something like "Items sold exceeds inventory remaining. Please check inventory!" However, I am not the best at userforms and I dont know if I can use an If stateme...

VBA Help on Loop
Hi All- I am pulling my hair out on this problem that I am having. I have a list of employees in a table connected to the form and when u click on the form where the employee is it will take u to a different website with that employees information. How can I get it to loop through each one vs clicking each employee to see the data. How can I get it to loop through each employee in the tracking list Here is the code so far I need this to loop through each employee in the tracking list and wait about 10 seconds while I copy whats on the website for that employee. Private Sub T...

Retrieving linked label formula with VBA
Dear group, I linked datalabels of a chart with cells (by selecting a single label, typing "=", clicking the cell to link with and then enter). Now I want to get the label's formula with VBA, but I only get the value with objPoint.DataLabel.Text. I am missing something like objPoint.DataLabel.Formula. Currently I use XL 2003. Any good ideas? Thanks in advance, Holger. hi, Holger ! > I linked datalabels of a chart with cells (by selecting a single label > typing "=", clicking the cell to link with and then enter). > Now I want to get the label's for...

ListBox/ComboBox creation/deletion using VBA.
I wish to offer users the facility to select data from a list that is populated using the results of a search facility - a ListBox or a ComboBox is the solution of choice. I know how to use VBA to populate the list and to determine the response and process accordingly. I have already done this with pre-existing boxes that have been created on a spreadsheet. But, search as I may, I cannot find out how to Create and Delete list/combo boxes using VBA. Can sks please point me in the right direction. TIA Chris ...

Looping through Pivottables
How do you loop all pivottables in a workbook to refresh them when source data in changed? TIA johnb Hi without looping try ActiveWorkbook.RefreshAll -- Regards Frank Kabel Frankfurt, Germany johnb wrote: > How do you loop all pivottables in a workbook to refresh > them when source data in changed? > > TIA > johnb ...

simple little loop
I'm stuck on a simple little loop late on a Friday night: Dim redRng As Range Dim longrow As Long longrow = Cells(Rows.Count, "I").End(xlUp).Row Set redRng = Range("I4", Range("I" & longrow).End(xlUp)) For Each cell In redRng If cell.Value <> "" Then ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=RC[-6]*RC[-7]" ActiveCell.Offset(1, -1).Select End If Next cell My loop seems to run four times and then quits. I guess it comes from the I4, ...

Running a VBA Macro
Hello, It is possible to run a VBA macro automatically when I open a workbook instead of opening the workbook and then run the VBA macro from the menu "Tools" "Macro?" Thanks, Jeff, In the Code window of "ThisWorkbook" in the VBA Editor (Click on "ThisWorkbook" and press F7), paste the following (for example) Private Sub Workbook_Open() 'MsgBox ("Good Morning - I'm Open!") CreateObject("WScript.Shell").Popup "Hello!", 1, " Hello for 1 second! " End Sub To run a macro before the workbook cl...

select a specific ActiveCell by VBA
In any cell in column "F", a user can select a code number from a drop down list. These numbers go up to over 100, so, if the user isn't sure of which number to select, cell F6 can be rightclicked and VBA will take the user to a different sheet where all the code numbers (and their associated meanings) are listed. A command button on that sheet will take the user back to the ActiveCell on the first sheet. Even if the user was working in cell F2000, the active cell is now always F6 To get around this, how can I set the active row to the bottom most row with data i...

Digitally signing VBA Project
I want to digitally sign my macro, so that all our offices can use it (without prompts). I know I can use SelfCert.exe, but I would like one which is recognised by windows as a trusted source (so all my users don't have to add my self cert on every machine). I have spoken to Verisign - they charge $499, I looked at GoDaddy, and they don't make them. Is there a more affordable source of one of these certificates? M You probably will not find any cheap sources for a digital certificate. Most users of digital certificates are commercial entities who can recover the cost of the...