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
 

Hi Nasir,

You error would suggest that an incorrect sheetname is enterd in response to
the input box. Providing that a correctly spelled sheet name is entered , I
would not antipate your error.


---
Regards,
Norman



wrote in message
ups.com...
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
 

When it breaks, is "sheet_name" an existing sheet

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=55911

run time error 9, subscript out of range
 

Thanks Norman and colofnature !!!

I still have the same error message. Yes it is the existing sheet
colof. Norm I have tried with an array too with all the names(carefully
entered), but it is the same error message. Quite strangely it is
working fine with the first 10 sheets , gives me error for the next 3
sheets and works fine with the last one. Here is my second version of
code, for your review:(gives me error for ENG sheet names)

Sub Hyper2()

Dim name As Variant
name = Array("Colour Page", "CH149", "SLL", "FRI", "CMR", "COS &
Other", _
"DTS", "Maintenance Checks", "LUBRIF", "ENG insp", _
"ENGLCF Calculations", "ENG life", "SB", "MFS Update
Log")
Dim pass

pass = "hiacsc"

Dim cell As Range
For i = 0 To 13
For Each cell In Sheets(name(i)).UsedRange.Cells
Sheets(name(i)).Unprotect (pass)

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(name(i)).Protect (pass)
MsgBox (name(i) + " is protected now")
Next i
End Sub




colofnature wrote:
When it breaks, is "sheet_name" an existing sheet?


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=559113

run time error 9, subscript out of range
 

Hi Nasir,

I have tried with an array too with all the names(carefully entered),

Despite your care, I would examine the three contentious sheets and ensure
that their names were as expected and, particularly, that no initial,
trailing, or other, spaces were included in the sheet names themselves.

BTW your code lines:

For Each cell In Sheets(name(i)).UsedRange.Cells
Sheets(name(i)).Unprotect (pass)


would appear to repeat the unprotection of each sheet for every cell in the
used range rather than once if you were to invert these two lines. i.e.:

Sheets(name(i)).Unprotect (pass)
For Each cell In Sheets(name(i)).UsedRange.Cells

---
Regards,
Norman



wrote in message
oups.com...
Thanks Norman and colofnature !!!

I still have the same error message. Yes it is the existing sheet
colof. Norm I have tried with an array too with all the names(carefully
entered), but it is the same error message. Quite strangely it is
working fine with the first 10 sheets , gives me error for the next 3
sheets and works fine with the last one. Here is my second version of
code, for your review:(gives me error for ENG sheet names)

Sub Hyper2()

Dim name As Variant
name = Array("Colour Page", "CH149", "SLL", "FRI", "CMR", "COS &
Other", _
"DTS", "Maintenance Checks", "LUBRIF", "ENG insp", _
"ENGLCF Calculations", "ENG life", "SB", "MFS Update
Log")
Dim pass

pass = "hiacsc"

Dim cell As Range
For i = 0 To 13
For Each cell In Sheets(name(i)).UsedRange.Cells
Sheets(name(i)).Unprotect (pass)

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(name(i)).Protect (pass)
MsgBox (name(i) + " is protected now")
Next i
End Sub




colofnature wrote:
When it breaks, is "sheet_name" an existing sheet?


--
colofnature
------------------------------------------------------------------------
colofnature's Profile:
http://www.excelforum.com/member.php...o&userid=34356
View this thread:
http://www.excelforum.com/showthread...hreadid=559113

run time error 9, subscript out of range
 

Norman, thanks a million. Yes you were right, this time I copied and
pasted the name of the sheets and it is working perfect.
God bless you,
Nasir.


Norman Jones wrote:
Hi Nasir,

You error would suggest that an incorrect sheetname is enterd in response to
the input box. Providing that a correctly spelled sheet name is entered , I
would not antipate your error.


---
Regards,
Norman



wrote in message
ups.com...
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
 

Norman, I really appreciate your time and much needed help. You are
awesome !!!
Nasir.( I didnt include the trailing spaces :), its working now.

Norman Jones wrote:
Hi Nasir,

I have tried with an array too with all the names(carefully entered),

Despite your care, I would examine the three contentious sheets and ensure
that their names were as expected and, particularly, that no initial,
trailing, or other, spaces were included in the sheet names themselves.

BTW your code lines:

For Each cell In Sheets(name(i)).UsedRange.Cells
Sheets(name(i)).Unprotect (pass)


would appear to repeat the unprotection of each sheet for every cell in the
used range rather than once if you were to invert these two lines. i.e.:

Sheets(name(i)).Unprotect (pass)
For Each cell In Sheets(name(i)).UsedRange.Cells

---
Regards,
Norman



wrote in message
oups.com...
Thanks Norman and colofnature !!!

I still have the same error message. Yes it is the existing sheet
colof. Norm I have tried with an array too with all the names(carefully
entered), but it is the same error message. Quite strangely it is
working fine with the first 10 sheets , gives me error for the next 3
sheets and works fine with the last one. Here is my second version of
code, for your review:(gives me error for ENG sheet names)

Sub Hyper2()

Dim name As Variant
name = Array("Colour Page", "CH149", "SLL", "FRI", "CMR", "COS &
Other", _
"DTS", "Maintenance Checks", "LUBRIF", "ENG insp", _
"ENGLCF Calculations", "ENG life", "SB", "MFS Update
Log")
Dim pass

pass = "hiacsc"

Dim cell As Range
For i = 0 To 13
For Each cell In Sheets(name(i)).UsedRange.Cells
Sheets(name(i)).Unprotect (pass)

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(name(i)).Protect (pass)
MsgBox (name(i) + " is protected now")
Next i
End Sub




colofnature wrote:
When it breaks, is "sheet_name" an existing sheet?


--
colofnature
------------------------------------------------------------------------
colofnature's Profile:
http://www.excelforum.com/member.php...o&userid=34356
View this thread:
http://www.excelforum.com/showthread...hreadid=559113