button gives run time error 1004

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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:

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]
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
How about adding error handling to your macro?

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

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Where do I get this "On Error" Help. Excel help doesn't have anything.

Michael
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916

ADVERTISEMENT

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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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

Well-known Member
Joined
May 19, 2002
Messages
2,916

ADVERTISEMENT

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.

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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Very new to coding!!!

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

Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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!!

Code:
On Error GoTo CopyFirst
    Application.ScreenUpdating = False

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

Well-known Member
Joined
May 19, 2002
Messages
2,916
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,670
Members
412,481
Latest member
nhantam
Top