Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run-time error '9': Subscript out of range - WHY?? | Excel Programming | |||
Run time error-subscript out of range | Excel Programming | |||
run-time error '9': Subscript out of range | Excel Programming | |||
run-time error '9': Subscript out of range | Excel Programming | |||
Run time error 9 : Subscript out of range | Excel Discussion (Misc queries) |