button gives run time error 1004

[daniels012]

I copy data from Outlook.

I have a button on a spreadsheet that pastes the data from outlook into the proper cells on the spreadsheet.

The Problem!!! If the user forgets to copy the data from outlook it gives a Runtime Error 1004. Can I put in my code something to give a message to the user to "copy the data from Oulook. vbcrtl then hit the button again." and take the user back to cell B2 on the spreadsheet. Not sure where to put the message as well?
Below is the code:

Sub PasteContactInfo()
'
' PasteContactInfo Macro
' Copies Data from Outlook
'

'
    Application.ScreenUpdating = False
    ActiveWindow.SmallScroll Down:=48
    Range("B61").Select
    ActiveSheet.Paste
    Range("B62").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=51
    Range("B63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-54
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=51
    Range("C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-48
    Range("D10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=45
    Range("D63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=48
    Range("E63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D7").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=48
    Range("F63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D8").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=48
    Range("G63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-45
    Range("D9").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=45
    Range("H63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-9
    Application.ScreenUpdating = True
    Range("B1").Select
End Sub

Thank You,
Michael[/code]

 

[daniels012]

Anyone? Is there a way to add this message?

Michael

 

[Seti]

How about adding error handling to your macro?

Check out the help for On Error. There is a great example.

 

[daniels012]

Where do I get this "On Error" Help. Excel help doesn't have anything.

Michael

 

[Seti]

Open the VB editor and use the VBA help. (F1 from within VBE) Maybe it wasn't installed on your machine. If not, post back and I'll C&P the example.

 

[daniels012]

I tried to install the help. I locked up in the middle of the process. So, if you don't mind, send me a C & P of the help file, that would be great!

Michael

 

[Seti]

Here you go:

On Error Statement Example
This example first uses the On Error GoTo statement to specify the location of an error-handling routine within a procedure. In the example, an attempt to delete an open file generates error number 55. The error is handled in the error-handling routine, and control is then returned to the statement that caused the error. The On Error GoTo 0 statement turns off error trapping. Then the On Error Resume Next statement is used to defer error trapping so that the context for the error generated by the next statement can be known for certain. Note that Err.Clear is used to clear the Err object's properties after the error is handled.

Sub OnErrorStatementDemo()
    On Error GoTo ErrorHandler    ' Enable error-handling routine.
    Open "TESTFILE" For Output As #1    ' Open file for output.
    Kill "TESTFILE"    ' Attempt to delete open 
                ' file.
    On Error Goto 0    ' Turn off error trapping.
    On Error Resume Next    ' Defer error trapping.
    ObjectRef = GetObject("MyWord.Basic")    ' Try to start nonexistent
                ' object, then test for 
'Check for likely Automation errors.
    If Err.Number = 440 Or Err.Number = 432 Then
        ' Tell user what happened. Then clear the Err object.
        Msg = "There was an error attempting to open the Automation object!"
        MsgBox Msg, , "Deferred Error Test"
        Err.Clear    ' Clear Err object fields 
    End If    
Exit Sub        ' Exit to avoid handler.
ErrorHandler:    ' Error-handling routine.
    Select Case Err.Number    ' Evaluate error number.
        Case 55    ' "File already open" error.
            Close #1    ' Close open file.
        Case Else
            ' Handle other situations here... 
    End Select
    Resume    ' Resume execution at same line
                ' that caused the error.
End Sub

 

[daniels012]

Very new to coding!!!

Not real sure how I can add this to my code that I have posted.

Michael

 

[daniels012]

Here is the code that I entered and it seems to work great! If anyone sees somthing I missed or I can do better, let me know!!

On Error GoTo CopyFirst
    Application.ScreenUpdating = False

CopyFirst:
    Range("B61:K63").Select
    Selection.Clear
    MsgBox "You must copy data from Outlook before you can paste with this button."
    Application.ScreenUpdating = True
    Range("B1").Activate
End Sub

Thank You,
Michael

 

[Seti]

Add an Exit Sub just before the CopyFirst line label. Otherwise, if you have no errors, the code will process and then give the msgbox.