Checking for [Ctrl] + [Pause/Break]

[Conan Kelly]

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

 

[Jim Rech]

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
|
|

 

[Conan Kelly]

Jim,

Thanks for the feed back. That looks like it is exactly what I'm looking for.

Thanks again for the help,

Conan

 

[Conan Kelly]

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