| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 9
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Runtime error 9
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 "Metallo" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Runtime error 9
Alex,
First - it's possibly macro refers to another workbook, Second - macro create by another user and project have password - disable debug Regards Mark -----Original Message----- 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Runtime error 9
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 "Mark" wrote: Alex, First - it's possibly macro refers to another workbook, Second - macro create by another user and project have password - disable debug Regards Mark -----Original Message----- 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Runtime error 9
Hi,
Can anybody help? Thank you ALex "Metallo" wrote: 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 "Mark" wrote: Alex, First - it's possibly macro refers to another workbook, Second - macro create by another user and project have password - disable debug Regards Mark -----Original Message----- 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
| Runtime error '1004' General ODBC error | New Users to Excel | |||
| Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
| Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
| Unknown where is the problem on the Runtime error - Automation error | Excel Programming | |||
![Metallo[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
