run time error 9, subscript out of range
 

Someone please help me with this. When I run this script, it works
fine with some of the sheets but then gives the error for some. It
stops at the For loop and says "subscript out of range".
Sub lock_protect()
Dim pass
Dim sheet_name
pass = "hiacsc"
sheet_name = InputBox("Enter the sheet name")
Dim cell As Range
For Each cell In Sheets(sheet_name).UsedRange.Cells
If cell.HasFormula = True Then
If cell.MergeCells = True Then
With cell.MergeArea
.Locked = True
End With
Else
cell.Locked = True
End If
End If
Next cell
Sheets(sheet_name).Protect (pass)
MsgBox (sheet_name + " is protected now")
End Sub

run time error 9, subscript out of range
 

Hello Nasir,

If the worksheet name the user enters does not exist, you will get this
error. You can trap this error and alert the user that the worksheet
name is invalid.

Sub lock_protect()
Dim pass
Dim sheet_name
Dim cell As Range
Dim wks As Worksheet
pass = "hiacsc"
sheet_name = InputBox("Enter the sheet name")
If sheet_name = "" Then Exit Sub
On Error Resume Next
Set wks = Sheets(sheet_name)
If err.number < 0 Then
MsgBox "Error - invalid sheet name entered."
Exit Sub
End If
For Each cell In wks.UsedRange.Cells
If cell.HasFormula = True Then
If cell.MergeCells = True Then
With cell.MergeArea
..Locked = True
End With
Else
cell.Locked = True
End If
End If
Next cell
wks.Protect (pass)
MsgBox (sheet_name + " is protected now")
End Sub

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=559112