Error handler will not work correctly after [Ctrl] + [Break]

[Conan Kelly]

Hello all,

Using Excel 2002 (10.6501.6626) SP 3 on Windows XP Pro (5.1.2600) SP2

I do a lot of copying from SQL Server Query Analyzer/pasting to Excel. After pasting, my data is full of "NULL's" that I need to
replace. I've already come up with some code that will replace all of the NULL's giving you the option to paste data first or
replace NULL's on existing data. This code works just fine. I let this code run for 10 seconds and then do a [Ctrl] + [Break] or
else problems start to arise (the data I run this on can be as large as 40,000 rows X 30 columns, and then some).

I'm trying to adapt this code so that when I do a [Ctrl] + [Break], I will get some feed back. Right now, if I do the [Ctrl] +
[Break], my error handler does not work correctly (code will be posted at the bottom). I get the following message:
http://home.att.net/~ctbarbarin/files/progress_error.jpg. If I put a line of code in the sub routine that will raise an error, the
error handler works just fine (I was using >>If mrngCurrRange = "Nothing" Then<< when mrngCurrRange is declared as a range
variable--this Error-causing code is the 2nd line of code in this macro after variable declarations and "On Error GoTo...").

How can I get this error handler to work correctly on a [Ctrl] + [Break]? It works correctly on a legitimate error!
--
Thanks for any help anyone can provide,

Conan Kelly







Sub PasteReplaceNulls()
Dim pmbrResponse As VbMsgBoxResult
Dim pmbrAnswer As VbMsgBoxResult
Dim pstrPasteCell As String
Dim ptimTimer As Date
Dim mrngCurrRange As Range

On Error GoTo PasteReplaceNulls_Err

Application.EnableCancelKey = xlErrorHandler


' If mrngCurrRange = "Nothing" Then
' do stuff...
' End If

pstrPasteCell = "A2"

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", "", xlPart, xlByRows, False, False, False

Exit Sub

PasteReplaceNulls_Err:
MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf & Selection.Address
Exit Sub

End Sub

 

[Conan Kelly]

MSweet,

Thanks for the valid attempt, but still no workie. The "if Err.Number..." caught it and sent it to "ExitHere:", but when it tried
to exectue "Application.EnableCancelKey...", this is the error message I recvd:
http://home.att.net/~ctbarbarin/files/enable_cancel_key_error.jpg

I will look into your suggestion about clearing out the NULL's in SQL Server.

Try this...

At the top of your Sub add...
Application.EnableCancelKey = xlErrorHandler 'Tells program to go to err
handler if user hits Esc key

At the exit of your Sub add...
ExitHere: '<<< You may already have this
Application.EnableCancelKey = xlInterrupt 'Returns cancel key back to
system setting
Exit Sub '<<< You may already have this

In your error handler add...
if Err.Number = 18 then goto ExitHere '<<< make sure you go to exit point
where xlInterrupt is set


Let me know if this helps.
Also, is it possible to modify the query to return blanks rather than NULL's
using an expression? That way you would not have any clean up.

Good Luck!!

--
Thx
MSweetG222

Hello all,

Using Excel 2002 (10.6501.6626) SP 3 on Windows XP Pro (5.1.2600) SP2

I do a lot of copying from SQL Server Query Analyzer/pasting to Excel. After pasting, my data is full of "NULL's" that I need to
replace. I've already come up with some code that will replace all of the NULL's giving you the option to paste data first or
replace NULL's on existing data. This code works just fine. I let this code run for 10 seconds and then do a [Ctrl] + [Break]
or
else problems start to arise (the data I run this on can be as large as 40,000 rows X 30 columns, and then some).

I'm trying to adapt this code so that when I do a [Ctrl] + [Break], I will get some feed back. Right now, if I do the [Ctrl] +
[Break], my error handler does not work correctly (code will be posted at the bottom). I get the following message:
http://home.att.net/~ctbarbarin/files/progress_error.jpg. If I put a line of code in the sub routine that will raise an error,
the
error handler works just fine (I was using >>If mrngCurrRange = "Nothing" Then<< when mrngCurrRange is declared as a range
variable--this Error-causing code is the 2nd line of code in this macro after variable declarations and "On Error GoTo...").

How can I get this error handler to work correctly on a [Ctrl] + [Break]? It works correctly on a legitimate error!
--
Thanks for any help anyone can provide,

Conan Kelly







Sub PasteReplaceNulls()
Dim pmbrResponse As VbMsgBoxResult
Dim pmbrAnswer As VbMsgBoxResult
Dim pstrPasteCell As String
Dim ptimTimer As Date
Dim mrngCurrRange As Range

On Error GoTo PasteReplaceNulls_Err

Application.EnableCancelKey = xlErrorHandler


' If mrngCurrRange = "Nothing" Then
' do stuff...
' End If

pstrPasteCell = "A2"

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", "", xlPart, xlByRows, False, False, False

Exit Sub

PasteReplaceNulls_Err:
MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf & Selection.Address
Exit Sub

End Sub

 

[Guest]

It stop'd on the Application.EnableCancelKey = xlInterrupt step?

Did you cut an paste or did you hand type the code line in?
Could you verify that you have it typed correctly?
Cut and paste above to confirm.

Also, if you hand type it, after you type the equal sign, you should receive
a drop down list of available xl choices, the xlInterrupt s/b in that list.