Suppress Error 1004?

[EssKayKay]

First, I must state I am by no means a programmer, so bear with me if you please.

I’m not sure this is possible but is there a way suppress an error message? I know why this is happening but I can’t find where or how to revise my code. Any suggestions would be appreciated.

Thanks,
Steve K.

 

[Micron]

Always best to prevent an error rather than knowingly allow it to happen then suppress it. I'd say especially true for 1004 since it seems to have umpteen reasons for the same number. I'd suggest testing if the cell is locked and protected if possible and act accordingly but would have to see your code to know if that could work. If not, you can use an error handler and deal with 1004. The problem with that approach is that the error number must have 25 reasons that could raise it, and by suppressing the number, you'd suppress it for all reasons. Trying to suppress an error based on its Description property would be unreliable.

 

[Joe4]

So what exactly is the situation, and why is it happening?

Is it related to this other recent thread you have?

VBA to bypass protected cell (Error #1004)?

Hello, I’m trying to find a VBA routine that will move down a column of values until it reaches the first blank cell. I tried running Sheets("Amortize").Select ActiveSheet.Unprotect Range("M32").Select Selection.End(xlDown).Select ActiveCell.Offset(1).Select The problem is if a cell is...

www.mrexcel.com

 

[EssKayKay]

So what exactly is the situation, and why is it happening?

Is it related to this other recent thread you have?

VBA to bypass protected cell (Error #1004)?

Hello, I’m trying to find a VBA routine that will move down a column of values until it reaches the first blank cell. I tried running Sheets("Amortize").Select ActiveSheet.Unprotect Range("M32").Select Selection.End(xlDown).Select ActiveCell.Offset(1).Select The problem is if a cell is...

www.mrexcel.com

It's very similar to this Joe, but not exactly the same. I know it is being triggered from protected cells in a specific range but I can’t figure out exactly when it is being triggered. I could try just removing the protection but I’d rather not do that.

As Micron notes, I realize suppressing the 1004 error is a very bad idea. However, if there is some way to suppress all 1004 errors with just one command/routine, that’s probably the only solution that would work for me.

Sorry for being a pest on this. I just am so close with this thing it’s a shame to let it go. Right now I have a message box popup prior to the location where the problem appears to be occurring which states if the user sees a 1004 error to simply press OK and continue on as all appears to be working fine after that. Now I super realize this is very unacceptable but I have no other idea what to do.

Thanks again,
Steve

 

[EssKayKay]

Always best to prevent an error rather than knowingly allow it to happen then suppress it. I'd say especially true for 1004 since it seems to have umpteen reasons for the same number. I'd suggest testing if the cell is locked and protected if possible and act accordingly but would have to see your code to know if that could work. If not, you can use an error handler and deal with 1004. The problem with that approach is that the error number must have 25 reasons that could raise it, and by suppressing the number, you'd suppress it for all reasons. Trying to suppress an error based on its Description property would be unreliable.

Thank you Micron for getting back to me. I realize suppressing errors is a very bad idea. However, I cannot figure out how to resolve this.

Is it possible to suppress all 1004 errors with just one VBA routine throughout the entire worksheet. If this is possible, how do I go about such? Any suggestions would be appreciated.

Again, sorry for asking redundant and somewhat alarming question(s).
Steve K.

 

[Michael M]

You really have no option but to Unprotect / protect the sheet.
However, if you give us more detail on what you are actually trying to do with the sheet it couls probably be incorporated into simple code.eg,

Sub MM1
activesheet.unprotect
"your other stuff here
activesheet. protect
end sub

then you would only have to run the sub

 

[Joe4]

If you can identify which procedure is causing the error, you may be able to add error handling to it. You can tell it to ignore errors of that type (1004).
The procedure would be structured like this:

Sub MyProcedure()

    On Error GoTo errFix

'   Your code here

    On Error GoTo 0
    Exit Sub

errFix:
    If err.Number = 1004 Then
        err.Clear
        Resume Next
    Else
        MsgBox err.Number & ":" & err.Description
    End If

End Sub

 

[Micron]

My issue with that idea is that there are so many reasons why 1004 gets raised and I'd worry about the prospect of dealing with all of them in the same way.

 

[EssKayKay]

My issue with that idea is that there are so many reasons why 1004 gets raised and I'd worry about the prospect of dealing with all of them in the same way.

I totally understand your concern. But is it possible to do so? That is, suppress all Error 1004 throughout the entire worksheet. If yes, then I would like to at least try it and see if it does work for me. Just asking.

 

[Joe4]

I totally understand your concern. But is it possible to do so? That is, suppress all Error 1004 throughout the entire worksheet. If yes, then I would like to at least try it and see if it does work for me. Just asking.

Yes, did you see my reply/code?

BTW, I totally agree with Micron, this really should be a last resort, as you may accidentally suppress other causes of this error that you may want to know about. If you can narrow it down to exactly where in your code the error is happening, you can at least apply the error handling to just that section.

For example, let's say that you have two main sections of code in your procedure (I will call them "Code Block 1" and "Code Block 2"), and you know that the code in "Code Block 1" is where this error is happening. Then you want to surround just that section of code with your "On Error" statements, i.e. structure it like this:

Sub MyProcedure()

    On Error GoTo errFix

'   Code Block 1

    On Error GoTo 0

'   Code Block 2

    Exit Sub

errFix:
    If err.Number = 1004 Then
        err.Clear
        Resume Next
    Else
        MsgBox err.Number & ":" & err.Description
    End If

End Sub