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]
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,802
Messages
5,833,751
Members
430,229
Latest member
TestableEmu263

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top