Error

[obiwann]

I have a Macro that will show an error at a specific point in the Macro.
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
If this error appears, I want a message box saying "You forgot to Copy Raw Prove Data" and then stop the Macro from running.

 

[obiwann]

It is still giving me the same error, see yellow test

Private Sub CommandButton1_Click()
On Error GoTo errHandler
ActiveSheet.Unprotect
Range("BJ87:BM110").ClearContents
Range("A14:F14").Select
Range("dump_table").ClearContents
Range("ch12").Select
[COLOR=rgb(250, 197, 28)]ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True[/COLOR]

Range("ch12").Select

If Range("A14") <> "" Then
If MsgBox("There is Data already present, do you want to overwrite", vbYesNo) = vbNo Then Exit Sub
End If

'Temperature Data
Range("BJ87").value = Range("db3")
Range("BJ88").value = Range("db4")
Range("BJ89").value = Range("db5")
Range("BJ90").value = Range("db6")


ActiveSheet.Protect
exitHere:
Exit Sub

errHandler:
If Err.Number = 1004 Then
  MsgBox "You Forgot to Copy Raw Proving Data"
Else
  MsgBox "Error " & Err.Number & ": " & Err.Description
End If
Resume exitHere

 

[Micron]

I see no yellow text. And the error number you get is 1004?

 

[obiwann]

I see no yellow text. And the error number you get is 1004?

For some reason when Highlighted the section of code that was highlighted in yellow generated some extra code around the error. And yes the error is 1004

 

[Micron]

Typically, you indicate like so

Range("BJ87").value = Range("db3") '<< error 1004 is raised at this line

Obviously you use the right line, not the one I used as an example. Note the preceding single quote ( " ) - allows you to have it in your code without messing it up.

 

[obiwann]

Typically, you indicate like so

Range("BJ87").value = Range("db3") '<< error 1004 is raised at this line

Obviously you use the right line, not the one I used as an example. Note the preceding single quote ( " ) - allows you to have it in your code without messing it up.

I am confused now. Can you copy all my code and insert your code?

 

[obiwann]

Ok, here is a simple macro where I get the same error. Can you please modify with your code so I can test it?

Sub Macro1()

    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True

End Sub

 

[GWteB]

Perhaps you're after something like the code below. See whether this helps.

Private Sub CommandButton1_Click()

    ActiveSheet.Unprotect
    Range("BJ87:BM110").ClearContents
    Range("A14:F14").Select
    Range("dump_table").ClearContents
    Range("ch12").Select
    
    On Error Resume Next
    Dim LastErr As Long
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    LastErr = VBA.Err.Number
    Err.Clear
    On Error GoTo 0
    
    If LastErr = 0 Then
        
        Range("ch12").Select
        If Range("A14") <> "" Then
            If MsgBox("There is Data already present, do you want to overwrite", vbYesNo) = vbNo Then Exit Sub
        End If
        
        'Temperature Data
        Range("BJ87").Value = Range("db3")
        Range("BJ88").Value = Range("db4")
        Range("BJ89").Value = Range("db5")
        Range("BJ90").Value = Range("db6")
        
        End With
        ActiveSheet.Protect
    
    Else
        MsgBox "You forgot to Copy Raw Prove Data", vbExclamation
    End If
End Sub

 

[obiwann]

Perhaps you're after something like the code below. See whether this helps.

Private Sub CommandButton1_Click()

    ActiveSheet.Unprotect
    Range("BJ87:BM110").ClearContents
    Range("A14:F14").Select
    Range("dump_table").ClearContents
    Range("ch12").Select
   
    On Error Resume Next
    Dim LastErr As Long
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    LastErr = VBA.Err.Number
    Err.Clear
    On Error GoTo 0
   
    If LastErr = 0 Then
       
        Range("ch12").Select
        If Range("A14") <> "" Then
            If MsgBox("There is Data already present, do you want to overwrite", vbYesNo) = vbNo Then Exit Sub
        End If
       
        'Temperature Data
        Range("BJ87").Value = Range("db3")
        Range("BJ88").Value = Range("db4")
        Range("BJ89").Value = Range("db5")
        Range("BJ90").Value = Range("db6")
       
        End With
        ActiveSheet.Protect
   
    Else
        MsgBox "You forgot to Copy Raw Prove Data", vbExclamation
    End If
End Sub

 

[obiwann]

It still gets stuck on error code 1004. BTW, I had to get rid of the End With

 

[GWteB]

It still gets stuck on error code 1004

If that's te case, on what line?

The error will continue to occur conditionally, but the suggested code ignores that error and produces a message as per your request:

If this error appears, I want a message box saying "You forgot to Copy Raw Prove Data" and then stop the Macro from running.

I had to get rid of the End With

... as per your original code from post #3.
I only amended the part of your code concerning your query.