2

I want to write a macro, that in all sheets locks certain cells -- from A12 to last row of R. Thing is, that I get

error 1004: "Method 'Range' Of Object '_Worksheet' failed"

in line

LastRow = wSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).

Could anyone help me out? Thanks! 

Option Explicit

Sub ProtectAll()

Dim wSheet          As Worksheet
Dim Pwd             As String
Dim LastRow         As Integer

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
LastRow = wSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    wSheet.Range(Cells(12, 1), Cells(LastRow, 18)).Select
    wSheet.Protect Password:=Pwd, AllowFiltering:=True
Next wSheet

End Sub
Community
  • 1
  • 1
atomoutside
  • 189
  • 1
  • 3
  • 11
  • This isnt how you protect cells. Please see [this answer](http://stackoverflow.com/questions/16684297/hiding-formulas-in-formula-bar/16686868#16686868) –  Jul 03 '13 at 11:28
  • 1
    remove the `After:=[A1],` part or qualify the [A1] with wsheet – JosieP Jul 03 '13 at 11:31

1 Answers1

3

Your code will fail if the sheet is blank as it currently assumes that it finds at least one non blank cell when it sets LastRow.

Try using a range object instead, test that it is Not Nothing before using LastRow.

Updated: for completeness added a check to see if sheets were already protected, and if so skips and otes these 

Option Explicit

Sub ProtectAll()

Dim wSheet          As Worksheet
Dim Pwd             As String
Dim rng1 As Range
Dim strProt As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
Set rng1 = wSheet.Cells.Find(What:="*", After:=wSheet.[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rng1 Is Nothing Then
With wSheet
If .ProtectContents Then
strProt = strProt & .Name & vbNewLine
Else
    .Range(.Cells(12, 1), .Cells(rng1.Row, 18)).Locked = True
    .Protect Password:=Pwd, AllowFiltering:=True
End If
End With
End If
Next wSheet

If Len(strProt) > 0 Then MsgBox strProt, , "These sheet were already protected so were skipped"

End Sub
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • Still doesn't work (even after removing uncommenting `wSheet.Range(Cells(12, 1), Cells(rng1.Row, 18)).Select`) – atomoutside Jul 03 '13 at 11:39
  • Same line and same error (1004). When I run your code it locks sheet, but not cells. – atomoutside Jul 03 '13 at 11:53
  • @mehow thats certainly true on locking the cells. But it doesn't explain the run-time error. – brettdj Jul 03 '13 at 11:59
  • + 1 nicely done. If you are using `.FIND` then it is better if you add the error handling. I have already discussed it [HERE](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) – Siddharth Rout Jul 04 '13 at 19:06