Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for [Ctrl] + [Pause/Break]
Hello all,
I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] + [Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes. The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue? End? Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my progress through the selected range. Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way? Thanks for any help anyone can provide, Conan Kelly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for [Ctrl] + [Pause/Break]
Have a look at Application.EnableCancelKey for controlling ESC during a
macro. I don't believe you can detect a specific keystroke while a macro is running though. -- Jim "Conan Kelly" <CTBarbarin at msn dot com wrote in message ... | Hello all, | | I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm | working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] + | [Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes. | | The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue? End? | Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my progress | through the selected range. | | Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way? | | Thanks for any help anyone can provide, | | Conan Kelly | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for [Ctrl] + [Pause/Break]
Jim,
Thanks for the feed back. That looks like it is exactly what I'm looking for. Thanks again for the help, Conan "Jim Rech" wrote in message ... Have a look at Application.EnableCancelKey for controlling ESC during a macro. I don't believe you can detect a specific keystroke while a macro is running though. -- Jim "Conan Kelly" <CTBarbarin at msn dot com wrote in message ... | Hello all, | | I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm | working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] + | [Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes. | | The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue? End? | Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my progress | through the selected range. | | Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way? | | Thanks for any help anyone can provide, | | Conan Kelly | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning progress
Hello all,
First, below is my first post closely related to this question. Jim Rech already answered this question for me. Thanks Jim. I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] + [Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes. The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue? End? Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my progress through the selected range. Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way? What I would like to do is display a message box displaying my progress through the selection. I can easily figure out the total number of rows/cell in the selection, but is there any way to figure out what row/cell the code left off on when it was canceled? Now for the code (I don't like using line continuation characters in my code--hopefully this is readable): Sub PasteReplaceNulls() On Error GoTo PasteReplaceNulls_Err Application.EnableCancelKey = xlErrorHandler Dim pmbrResponse As VbMsgBoxResult Dim pmbrAnswer As VbMsgBoxResult Dim pstrPasteCell As String pstrPasteCell = "A2" If Application.Calculation = xlCalculationAutomatic Then ToggleAutoCalc pmbrResponse = MsgBox("Do you want to Paste?", vbYesNoCancel + vbDefaultButton2 + vbQuestion, "Paste?") If pmbrResponse = vbCancel Then Exit Sub ElseIf pmbrResponse = vbYes Then pmbrAnswer = MsgBox("Data will be pasted starting in cell A2" & vbCrLf & vbCrLf & "Is this correct?", vbYesNoCancel + vbQuestion, "Paste in A2") If pmbrAnswer = vbCancel Then Exit Sub ElseIf pmbrAnswer = vbNo Then pstrPasteCell = InputBox("Please enter the cell you would like pasting to begin:", "Paste Cell", pstrPasteCell) End If Range(pstrPasteCell).Select ActiveSheet.Paste End If ' Selection.Replace "NULL", Null, xlPart, xlByRows, False, False, False Selection.Replace "NULL", "", xlPart, xlByRows, False, False, False If Application.Calculation = xlCalculationManual Then ToggleAutoCalc PasteReplaceNulls_Exit: Exit Sub PasteReplaceNulls_Err: MsgBox "Need to enter progress info here." Exit Sub End Sub PS. If any one has a more efficient way of doing this, I'm all ears. Thanks for any help anyone can provide, Conan Kelly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Diable Ctrl+Break | Excel Programming | |||
CTRL +BREAK | Excel Programming | |||
Ctrl+Alt+Break | Excel Programming | |||
Can Not Get Macro to Break with CTRL-BREAK | Excel Programming | |||
How to disable the Ctrl+Break ? | Excel Programming |