Runtime error 9

G

Guest

Hi,

When opening my Excel WB and enable macros I get the following message:

Runtime error 9
Subscript Out Of Range

I do not get any possibility to debug, only to END the message window.
Previously I changed the tab names, but I changed them in the macros as well
and after having checked them several time, I cannot find any error.
Otherwise, I cannot explain the message window, also because once I have
clicked END, all the macros seem to work perfectly.

Any idea?

Thanks
Alex
 
G

Guest

I am guessing that you have an on_open macro in the workbook somewhere? Have
you tried stepping through that code one line at a time? I am guessing that
the problem is somewhere in there...

HTH
 
M

Mark

Alex,

First - it's possibly macro refers to another workbook,
Second - macro create by another user and project have
password - disable debug
Regards
Mark
 
G

Guest

Hi,

This is the code, I cannot find anything strange in it.
Please, have a look yourself.

QUOTE
Option Explicit

Dim arySheets

Private Sub Workbook_Open()

'''Enable Outlining navigation and protect everything on the sheet with
UserInterfaceOnly.
Sheets("2003").EnableOutlining = True
Sheets("2003").Protect Password:="psw", UserInterfaceOnly:=True
Sheets("Reduction Target 2004_05").EnableOutlining = True
Sheets("Reduction Target 2004_05").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("2005 Target").EnableOutlining = True
Sheets("2005 Target").Protect Password:="psw", UserInterfaceOnly:=True
Sheets("2005 Act").EnableOutlining = True
Sheets("2005 Act").Protect Password:="psw", UserInterfaceOnly:=True
Sheets("2005 Comp to 2003").EnableOutlining = True
Sheets("2005 Comp to 2003").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("2005 Comp to 2003_ Volume Only").EnableOutlining = True
Sheets("2005 Comp to 2003_ Volume Only").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff of 2005 Comp, to 2003").EnableOutlining = True
Sheets("Diff of 2005 Comp, to 2003").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff of 2005 Comp, to 2005 Tgt ").EnableOutlining = True
Sheets("Diff of 2005 Comp, to 2005 Tgt ").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff of 2005 Comp_VO, to 2003").EnableOutlining = True
Sheets("Diff of 2005 Comp_VO, to 2003").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff 2005 Comp_VO, to 2005 Tgt").EnableOutlining = True
Sheets("Diff 2005 Comp_VO, to 2005 Tgt").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Macros").Protect Password:="psw"
Sheets("Glossary").Protect Password:="psw"
Sheets("DB").Protect Password:="psw"
Sheets("DB_VO").Protect Password:="psw"
Charts("Chart3").Protect Password:="psw"
Charts("Chart4").Protect Password:="psw"

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet

On Error GoTo ws_exit:
arySheets = Array("2003", "Reduction Target 2004_05", "2005 Target",
"2005 Act", "2005 Comp to 2003", "2005 Comp to 2003_ Volume Only", "Diff of
2005 Comp, to 2003", "Diff of 2005 Comp, to 2005 Tgt ", "Diff of 2005
Comp_VO, to 2003", "Diff 2005 Comp_VO, to 2005 Tgt", "DB", "DB_VO")
Application.EnableEvents = False
If SheetInArray(Sh.Name) Then
If Target.Address = "$B$5" Then
With Target
If .Value >= 1 And .Value <= 12 Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name And
SheetInArray(oSheet.Name) Then
If oSheet.ProtectContents Then
oSheet.Unprotect Password:="psw"
oSheet.Range("B5").Value = .Value
oSheet.Protect Password:="psw"
Else
oSheet.Range("B5").Value = .Value
End If
End If
Next oSheet
Else
MsgBox .Value & " is an invalid value"
.Value = ""
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

Private Function SheetInArray(Name As String)
Dim fSheet As Boolean
Dim i As Long
fSheet = False
For i = LBound(arySheets, 1) To UBound(arySheets, 1)
If arySheets(i) = Name Then
fSheet = True
Exit For
End If
Next i
SheetInArray = fSheet

End Function

/QUOTE

Thanks
Alex
 
G

Guest

Hi,

Can anybody help?

Thank you
ALex

Metallo said:
Hi,

This is the code, I cannot find anything strange in it.
Please, have a look yourself.

QUOTE
Option Explicit

Dim arySheets

Private Sub Workbook_Open()

'''Enable Outlining navigation and protect everything on the sheet with
UserInterfaceOnly.
Sheets("2003").EnableOutlining = True
Sheets("2003").Protect Password:="psw", UserInterfaceOnly:=True
Sheets("Reduction Target 2004_05").EnableOutlining = True
Sheets("Reduction Target 2004_05").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("2005 Target").EnableOutlining = True
Sheets("2005 Target").Protect Password:="psw", UserInterfaceOnly:=True
Sheets("2005 Act").EnableOutlining = True
Sheets("2005 Act").Protect Password:="psw", UserInterfaceOnly:=True
Sheets("2005 Comp to 2003").EnableOutlining = True
Sheets("2005 Comp to 2003").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("2005 Comp to 2003_ Volume Only").EnableOutlining = True
Sheets("2005 Comp to 2003_ Volume Only").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff of 2005 Comp, to 2003").EnableOutlining = True
Sheets("Diff of 2005 Comp, to 2003").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff of 2005 Comp, to 2005 Tgt ").EnableOutlining = True
Sheets("Diff of 2005 Comp, to 2005 Tgt ").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff of 2005 Comp_VO, to 2003").EnableOutlining = True
Sheets("Diff of 2005 Comp_VO, to 2003").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Diff 2005 Comp_VO, to 2005 Tgt").EnableOutlining = True
Sheets("Diff 2005 Comp_VO, to 2005 Tgt").Protect Password:="psw",
UserInterfaceOnly:=True
Sheets("Macros").Protect Password:="psw"
Sheets("Glossary").Protect Password:="psw"
Sheets("DB").Protect Password:="psw"
Sheets("DB_VO").Protect Password:="psw"
Charts("Chart3").Protect Password:="psw"
Charts("Chart4").Protect Password:="psw"

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet

On Error GoTo ws_exit:
arySheets = Array("2003", "Reduction Target 2004_05", "2005 Target",
"2005 Act", "2005 Comp to 2003", "2005 Comp to 2003_ Volume Only", "Diff of
2005 Comp, to 2003", "Diff of 2005 Comp, to 2005 Tgt ", "Diff of 2005
Comp_VO, to 2003", "Diff 2005 Comp_VO, to 2005 Tgt", "DB", "DB_VO")
Application.EnableEvents = False
If SheetInArray(Sh.Name) Then
If Target.Address = "$B$5" Then
With Target
If .Value >= 1 And .Value <= 12 Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name <> Sh.Name And
SheetInArray(oSheet.Name) Then
If oSheet.ProtectContents Then
oSheet.Unprotect Password:="psw"
oSheet.Range("B5").Value = .Value
oSheet.Protect Password:="psw"
Else
oSheet.Range("B5").Value = .Value
End If
End If
Next oSheet
Else
MsgBox .Value & " is an invalid value"
.Value = ""
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

Private Function SheetInArray(Name As String)
Dim fSheet As Boolean
Dim i As Long
fSheet = False
For i = LBound(arySheets, 1) To UBound(arySheets, 1)
If arySheets(i) = Name Then
fSheet = True
Exit For
End If
Next i
SheetInArray = fSheet

End Function

/QUOTE

Thanks
Alex
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime error 424 in Windows 8 environment 15
runtime error 9 1
Runtime Error '9': Subscript Out Of Range 6
Runtime Error '9' 6
Load a File Error 9 7
Macros disabled, not by security settings? 5
Debuging Macros 1
"Subscript out of range Runtime Error 9" 1

Top