Runtime Error '9': Subscript Out Of Range
 

Can someone please help and tell me how to correct error message: Runtime
Error '9': Subscript Out Of Range in the following code?
I have marked the line where Debug gives the error with 'ERROR ON THIS LINE
below.
I am trying to run the macro from one workbook and accessing another
workbook which is open in the same folder.
Thanks
Jack

Sub ProtectWkshtsFromSeperateFile()
'Counts worksheets and protects worksheets from passwords
'listed in column B on Passwords worksheet in seperate workbook
Dim ShPassword As String
Dim WkstName As String
Const wkb = "C:\Documents and Settings\Jack\My Documents\@excel password
test\psw source.xlsm"

For i = 1 To Workbooks(wkb).Worksheets.Count 'ERROR ON THIS LINE
WkstName = Worksheets(i).Name
'VLookup sheet password on Passwords worksheet
ShPassword = Application.WorksheetFunction.VLookup(WkstName,
Sheets("Passwords") _
..Range("A1:B" & Sheets("Passwords").Range("B" & Rows.Count).End(xlUp).Row),
2, False)
'Protect each worksheet with specified password on Passwords worksheet
Workbooks(wkb).Worksheets(WkstName).Protect Password:=ShPassword
Next i
End Sub

Runtime Error '9': Subscript Out Of Range
 

Hi Jack

Workbooks(wkb).Worksheets.Count ...........In this code wkb should be the
work book name alone without the full destination. For example
Workbooks("book1.xls") should work. Please try

JacobSkaria
(If this post is helpful please click Yes)

"jack" wrote:

Can someone please help and tell me how to correct error message: Runtime
Error '9': Subscript Out Of Range in the following code?
I have marked the line where Debug gives the error with 'ERROR ON THIS LINE
below.
I am trying to run the macro from one workbook and accessing another
workbook which is open in the same folder.
Thanks
Jack

Sub ProtectWkshtsFromSeperateFile()
'Counts worksheets and protects worksheets from passwords
'listed in column B on Passwords worksheet in seperate workbook
Dim ShPassword As String
Dim WkstName As String
Const wkb = "C:\Documents and Settings\Jack\My Documents\@excel password
test\psw source.xlsm"

For i = 1 To Workbooks(wkb).Worksheets.Count 'ERROR ON THIS LINE
WkstName = Worksheets(i).Name
'VLookup sheet password on Passwords worksheet
ShPassword = Application.WorksheetFunction.VLookup(WkstName,
Sheets("Passwords") _
..Range("A1:B" & Sheets("Passwords").Range("B" & Rows.Count).End(xlUp).Row),
2, False)
'Protect each worksheet with specified password on Passwords worksheet
Workbooks(wkb).Worksheets(WkstName).Protect Password:=ShPassword
Next i
End Sub

Runtime Error '9': Subscript Out Of Range
 

I would try

For i = 1 To wkb.Worksheets.Count 'ERROR ON THIS LINE

"jack" wrote in message
...
Can someone please help and tell me how to correct error message: Runtime
Error '9': Subscript Out Of Range in the following code?
I have marked the line where Debug gives the error with 'ERROR ON THIS
LINE
below.
I am trying to run the macro from one workbook and accessing another
workbook which is open in the same folder.
Thanks
Jack

Sub ProtectWkshtsFromSeperateFile()
'Counts worksheets and protects worksheets from passwords
'listed in column B on Passwords worksheet in seperate workbook
Dim ShPassword As String
Dim WkstName As String
Const wkb = "C:\Documents and Settings\Jack\My Documents\@excel password
test\psw source.xlsm"

For i = 1 To Workbooks(wkb).Worksheets.Count 'ERROR ON THIS LINE
WkstName = Worksheets(i).Name
'VLookup sheet password on Passwords worksheet
ShPassword = Application.WorksheetFunction.VLookup(WkstName,
Sheets("Passwords") _
.Range("A1:B" & Sheets("Passwords").Range("B" &
Rows.Count).End(xlUp).Row),
2, False)
'Protect each worksheet with specified password on Passwords worksheet
Workbooks(wkb).Worksheets(WkstName).Protect Password:=ShPassword
Next i
End Sub

Runtime Error '9': Subscript Out Of Range
 

Thanks Jacob,
Your suggestion worked.
By changing to: Const wkb = "pw source.xlsm"

Now I'm getting the same error message on this line:
Workbooks(wkb).Worksheets(WkstName).Protect Password:=ShPassword
(It's the third to last line of code)
Any suggestions for correcting the above line?
Jack


"Jacob Skaria" wrote in message
...
Hi Jack

Workbooks(wkb).Worksheets.Count ...........In this code wkb should be the
work book name alone without the full destination. For example
Workbooks("book1.xls") should work. Please try

JacobSkaria
(If this post is helpful please click Yes)

"jack" wrote:

Can someone please help and tell me how to correct error message: Runtime
Error '9': Subscript Out Of Range in the following code?
I have marked the line where Debug gives the error with 'ERROR ON THIS
LINE
below.
I am trying to run the macro from one workbook and accessing another
workbook which is open in the same folder.
Thanks
Jack

Sub ProtectWkshtsFromSeperateFile()
'Counts worksheets and protects worksheets from passwords
'listed in column B on Passwords worksheet in seperate workbook
Dim ShPassword As String
Dim WkstName As String
Const wkb = "C:\Documents and Settings\Jack\My Documents\@excel password
test\psw source.xlsm"

For i = 1 To Workbooks(wkb).Worksheets.Count 'ERROR ON THIS LINE
WkstName = Worksheets(i).Name
'VLookup sheet password on Passwords worksheet
ShPassword = Application.WorksheetFunction.VLookup(WkstName,
Sheets("Passwords") _
..Range("A1:B" & Sheets("Passwords").Range("B" &
Rows.Count).End(xlUp).Row),
2, False)
'Protect each worksheet with specified password on Passwords worksheet
Workbooks(wkb).Worksheets(WkstName).Protect Password:=ShPassword
Next i
End Sub

Runtime Error '9': Subscript Out Of Range
 

Hi Jack

I dont see any problem with that code; could you please check whether the
password you are retrieving is valid???

JacobSkaria
(If this post is helpful please click Yes)

Runtime Error '9': Subscript Out Of Range
 

Hi Jacob,
Thanks for responding.....
I re-ran the code without changes after posting message and the error on
that line went away!!!
Strange to me!!
Jack


"Jacob Skaria" wrote in message
...
Hi Jack

I dont see any problem with that code; could you please check whether the
password you are retrieving is valid???

JacobSkaria
(If this post is helpful please click Yes)

Runtime Error '9': Subscript Out Of Range
 

Jack, Good to here that!!!!!!!!

If this post is helpful click Yes
---------------
Jacob Skaria