Run-time error when using Worksheets("Sheet1").Activate

[Ottsel]

I'm using the following code before running my script to help cover any errors, as people sometimes add spaces at the end of titles causing my macro to crash

so, before running I was attempting to make sure the sheet - which the name never changes - would be selected before executing to avoid it running on the wrong sheet.

Sub MrExcelExample()
    Dim r As Range: Set r = Rows("8:9")

    Sheets("Sheet1").Select
    ActiveSheet.Unprotect
    r.Value = Application.Trim(r)

End Sub

If I remove the "Sheets("Sheet1").select" code it'll run fine, and if I use that code on its own macro it also runs fine.

Here's the error pop-up I receive when its added before the application.Trim(r)

 

[rlv01]

I cannot reproduce the error. Your posted code when run on a locked sheet does not produce any errors for me.

 

[Ottsel]

I cannot reproduce the error. Your posted code when run on a locked sheet does not produce any errors for me.

if i'm already on that sheet, then i encounter no error, but if i happen on sheet 2, then the error occurs. Workbook is not producted, nor is the sheet.

 

[Ottsel]

Seems

Dim r As Range: Set r = Sheets("Sheet1").Rows("8:9")

was all that I needed to add. As to why I'm not certain, but appears to be the ticket.

 

[rlv01]

I don't see how that is possible. The marked statement bloiw

Seems

Dim r As Range: Set r = Sheets("Sheet1").Rows("8:9")

was all that I needed to add. As to why I'm not certain, but appears to be the ticket.

Because if you start on Sheet2, then the range r is for Sheet2 cells, and if Sheet2 is protected you are going to get that error when you try to trim the range r cells.

This should also work.

Sub MrExcelExample()
    Dim r As Range

    With Sheets("Sheet1")
         Set r = .Rows("8:9")
        .Unprotect
    End With
    r.Value = Application.Trim(r.Value)
End Sub

 

[Ottsel]

I don't see how that is possible. The marked statement bloiw


Because if you start on Sheet2, then the range r is for Sheet2 cells, and if Sheet2 is protected you are going to get that error when you try to trim the range r cells.

This should also work.

Sub MrExcelExample()
    Dim r As Range

    With Sheets("Sheet1")
         Set r = .Rows("8:9")
        .Unprotect
    End With
    r.Value = Application.Trim(r.Value)
End Sub

Makes since. Didn't think of it like that. Appreciate it!