Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Excel 2003. Win XP
The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi
What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi Per
The macro stops running at the line Set SheetCodeModule. and the error message <<Runtime error 9 and <Subscript out of range is displayed. If I run my add worksheet routine on its own, without the routine which adds code to the worksheet object, then the new sheet is created without error. If I then run the add code routine separately, it runs ok and the required code is added. But if the add code routine is called from the add worksheet routine, then the macro stops as above. Adding screenupdating=true to the second macro had no effect. I'm still baffled!! -- donwb "Per Jessen" wrote: Hi What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi
I can not reproduce the error with the code below. Try to make a breakepoint at the line " Set SheetCodeModule =..." and check the value of MySh. Is the value what you expect it to be? Sub AddSheet() Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Call WriteCode End Sub Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "Application.ScreenUpdating = True" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Regards, Per On 29 Dec., 00:18, donbowyer wrote: Hi Per The macro stops running at the line Set SheetCodeModule. and the error message <<Runtime error 9 and <Subscript out of range is displayed. If I run my add worksheet routine on its own, without the routine which adds code to the worksheet object, then the new sheet is created without error. If I then run the add code routine separately, it runs ok and the required code is added. But if the add code routine is called from the add worksheet routine, then the macro stops as above. Adding screenupdating=true to the second macro had no effect. I'm still baffled!! -- donwb "Per Jessen" wrote: Hi What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le * With SheetCodeModule * * CodeLine = .CountOfLines + 1 * .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ * *"Application.ScreenUpdating = False" & Chr(13) & _ * *"If MyNewSheet = ""Running"" Then" & Chr(13) & _ * *"MyNewSheet = ""NotRunning""" & Chr(13) & _ * *"Go To Line2" & Chr(13) & _ * *"End If" & Chr(13) & _ * *"Call Load" & Chr(13) & _ * *"Line2:" & Chr(13) & _ * *"End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi Per
I put a BP on the line Set SheetCodeModule....... The value of MySh was "sheet1" as expected. On reaching this break point the new sheet1 has been created OK. If I then hit Run, to complete execution of the macro, it runs to the end without error, and the required code is written into the sheet. ?? -- donwb "Per Jessen" wrote: Hi I can not reproduce the error with the code below. Try to make a breakepoint at the line " Set SheetCodeModule =..." and check the value of MySh. Is the value what you expect it to be? Sub AddSheet() Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Call WriteCode End Sub Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "Application.ScreenUpdating = True" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Regards, Per On 29 Dec., 00:18, donbowyer wrote: Hi Per The macro stops running at the line Set SheetCodeModule. and the error message <<Runtime error 9 and <Subscript out of range is displayed. If I run my add worksheet routine on its own, without the routine which adds code to the worksheet object, then the new sheet is created without error. If I then run the add code routine separately, it runs ok and the required code is added. But if the add code routine is called from the add worksheet routine, then the macro stops as above. Adding screenupdating=true to the second macro had no effect. I'm still baffled!! -- donwb "Per Jessen" wrote: Hi What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi donwb
Your error will (only) occure when the value of MySh isn't referring to at sheet in the active workbook. If i set MySh = "Dummy" then I get the error you mentioned earlier. //Per On 29 Dec., 01:45, donbowyer wrote: Hi Per I put a BP on the line Set SheetCodeModule....... The value of MySh was "sheet1" as expected. On reaching this break point the new sheet1 has been created OK. If I then hit Run, to complete execution of the macro, it runs to the end without error, and the required code is written into the sheet. ?? -- donwb "Per Jessen" wrote: Hi I can not reproduce the error with the code below. Try to make a breakepoint at the line " Set SheetCodeModule =..." and check the value of MySh. Is the value what you expect it to be? Sub AddSheet() Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Call WriteCode End Sub Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le * *With SheetCodeModule * * *CodeLine = .CountOfLines + 1 * *.InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ * * "Application.ScreenUpdating = False" & Chr(13) & _ * * "If MyNewSheet = ""Running"" Then" & Chr(13) & _ * * "MyNewSheet = ""NotRunning""" & Chr(13) & _ * * "Go To Line2" & Chr(13) & _ * * "End If" & Chr(13) & _ * * "Call Load" & Chr(13) & _ * * "Line2:" & Chr(13) & _ * * "Application.ScreenUpdating = True" & Chr(13) & _ * * "End Sub" End With Set SheetCodeModule = Nothing End Sub Regards, Per On 29 Dec., 00:18, donbowyer wrote: Hi Per The macro stops running at the line Set SheetCodeModule. and the error message <<Runtime error 9 and <Subscript out of range is displayed.. If I run my add worksheet routine on its own, without the routine which adds code to the worksheet object, then the new sheet is created without error. If I then run the add code routine separately, it runs ok and the required code is added. But if the add code routine is called from the add worksheet routine, then the macro stops as above. Adding screenupdating=true to the second macro had no effect. I'm still baffled!! -- donwb "Per Jessen" wrote: Hi What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le * With SheetCodeModule * * CodeLine = .CountOfLines + 1 * .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ * *"Application.ScreenUpdating = False" & Chr(13) & _ * *"If MyNewSheet = ""Running"" Then" & Chr(13) & _ * *"MyNewSheet = ""NotRunning""" & Chr(13) & _ * *"Go To Line2" & Chr(13) & _ * *"End If" & Chr(13) & _ * *"Call Load" & Chr(13) & _ * *"Line2:" & Chr(13) & _ * *"End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi Per
Yes - I agree with you. But since MySh gives the expected result, what I am wondering is something like this:- I run the macro that generates the new sheet, and even though the new sheet appears immediately in the workbook, maybe its information or data is not actually available for further use until the generating macro finishes. If that was the case, then the code loading subroutine, if run as a Call from the sheet generating subroutine, would not <<Find MySh. Whereas, if it is started separately as a Subroutine in its own right, then it works. Note that I do have MySh declared as a Public variable. The problem with this is that I cannot believe this is how Excel VBA works. -- donwb "Per Jessen" wrote: Hi donwb Your error will (only) occure when the value of MySh isn't referring to at sheet in the active workbook. If i set MySh = "Dummy" then I get the error you mentioned earlier. //Per On 29 Dec., 01:45, donbowyer wrote: Hi Per I put a BP on the line Set SheetCodeModule....... The value of MySh was "sheet1" as expected. On reaching this break point the new sheet1 has been created OK. If I then hit Run, to complete execution of the macro, it runs to the end without error, and the required code is written into the sheet. ?? -- donwb "Per Jessen" wrote: Hi I can not reproduce the error with the code below. Try to make a breakepoint at the line " Set SheetCodeModule =..." and check the value of MySh. Is the value what you expect it to be? Sub AddSheet() Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Call WriteCode End Sub Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "Application.ScreenUpdating = True" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Regards, Per On 29 Dec., 00:18, donbowyer wrote: Hi Per The macro stops running at the line Set SheetCodeModule. and the error message <<Runtime error 9 and <Subscript out of range is displayed.. If I run my add worksheet routine on its own, without the routine which adds code to the worksheet object, then the new sheet is created without error. If I then run the add code routine separately, it runs ok and the required code is added. But if the add code routine is called from the add worksheet routine, then the macro stops as above. Adding screenupdating=true to the second macro had no effect. I'm still baffled!! -- donwb "Per Jessen" wrote: Hi What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi donwb
I just don't get it. As you can get the name of the new sheet, I am sure that you have the full control of it. Try to add this line after mySh = Active... Debug.Print MySh then open the Immediate window (ctrl+G) before you run the subroutine generating the new sheet. Is the value in the Immediate window "Sheet1" as expected? If it is the subroutine adding the code should work for sure. Have you tried to make the code loading Subroutine a part of the sheet generating subroutine. It shouldn't be nessecary to declare MySh as a Public variable, as you assign a value to MySh in the Subroutine where you use it. I agree with you Excel VBA shoulden't work this way. //Per "donbowyer" skrev i en meddelelse ... Hi Per Yes - I agree with you. But since MySh gives the expected result, what I am wondering is something like this:- I run the macro that generates the new sheet, and even though the new sheet appears immediately in the workbook, maybe its information or data is not actually available for further use until the generating macro finishes. If that was the case, then the code loading subroutine, if run as a Call from the sheet generating subroutine, would not <<Find MySh. Whereas, if it is started separately as a Subroutine in its own right, then it works. Note that I do have MySh declared as a Public variable. The problem with this is that I cannot believe this is how Excel VBA works. -- donwb "Per Jessen" wrote: Hi donwb Your error will (only) occure when the value of MySh isn't referring to at sheet in the active workbook. If i set MySh = "Dummy" then I get the error you mentioned earlier. //Per On 29 Dec., 01:45, donbowyer wrote: Hi Per I put a BP on the line Set SheetCodeModule....... The value of MySh was "sheet1" as expected. On reaching this break point the new sheet1 has been created OK. If I then hit Run, to complete execution of the macro, it runs to the end without error, and the required code is written into the sheet. ?? -- donwb "Per Jessen" wrote: Hi I can not reproduce the error with the code below. Try to make a breakepoint at the line " Set SheetCodeModule =..." and check the value of MySh. Is the value what you expect it to be? Sub AddSheet() Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Call WriteCode End Sub Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "Application.ScreenUpdating = True" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Regards, Per On 29 Dec., 00:18, donbowyer wrote: Hi Per The macro stops running at the line Set SheetCodeModule. and the error message <<Runtime error 9 and <Subscript out of range is displayed.. If I run my add worksheet routine on its own, without the routine which adds code to the worksheet object, then the new sheet is created without error. If I then run the add code routine separately, it runs ok and the required code is added. But if the add code routine is called from the add worksheet routine, then the macro stops as above. Adding screenupdating=true to the second macro had no effect. I'm still baffled!! -- donwb "Per Jessen" wrote: Hi What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code into MS Object Sheet Hangs up
Hi Per
I did try all you suggested in your last posting, but results were the same. However think I have found the problem. In the declarations before the code adding subroutine there were two missing:- Dim VBProj As VBProject Set VBProj = ThisWorkbook.VBProject With these in it now works fine - take them out and I get the error message. Thanks for all your inputs. It serves to show that declarations are important!! -- donwb "Per Jessen" wrote: Hi donwb I just don't get it. As you can get the name of the new sheet, I am sure that you have the full control of it. Try to add this line after mySh = Active... Debug.Print MySh then open the Immediate window (ctrl+G) before you run the subroutine generating the new sheet. Is the value in the Immediate window "Sheet1" as expected? If it is the subroutine adding the code should work for sure. Have you tried to make the code loading Subroutine a part of the sheet generating subroutine. It shouldn't be nessecary to declare MySh as a Public variable, as you assign a value to MySh in the Subroutine where you use it. I agree with you Excel VBA shoulden't work this way. //Per "donbowyer" skrev i en meddelelse ... Hi Per Yes - I agree with you. But since MySh gives the expected result, what I am wondering is something like this:- I run the macro that generates the new sheet, and even though the new sheet appears immediately in the workbook, maybe its information or data is not actually available for further use until the generating macro finishes. If that was the case, then the code loading subroutine, if run as a Call from the sheet generating subroutine, would not <<Find MySh. Whereas, if it is started separately as a Subroutine in its own right, then it works. Note that I do have MySh declared as a Public variable. The problem with this is that I cannot believe this is how Excel VBA works. -- donwb "Per Jessen" wrote: Hi donwb Your error will (only) occure when the value of MySh isn't referring to at sheet in the active workbook. If i set MySh = "Dummy" then I get the error you mentioned earlier. //Per On 29 Dec., 01:45, donbowyer wrote: Hi Per I put a BP on the line Set SheetCodeModule....... The value of MySh was "sheet1" as expected. On reaching this break point the new sheet1 has been created OK. If I then hit Run, to complete execution of the macro, it runs to the end without error, and the required code is written into the sheet. ?? -- donwb "Per Jessen" wrote: Hi I can not reproduce the error with the code below. Try to make a breakepoint at the line " Set SheetCodeModule =..." and check the value of MySh. Is the value what you expect it to be? Sub AddSheet() Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Call WriteCode End Sub Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "Application.ScreenUpdating = True" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Regards, Per On 29 Dec., 00:18, donbowyer wrote: Hi Per The macro stops running at the line Set SheetCodeModule. and the error message <<Runtime error 9 and <Subscript out of range is displayed.. If I run my add worksheet routine on its own, without the routine which adds code to the worksheet object, then the new sheet is created without error. If I then run the add code routine separately, it runs ok and the required code is added. But if the add code routine is called from the add worksheet routine, then the macro stops as above. Adding screenupdating=true to the second macro had no effect. I'm still baffled!! -- donwb "Per Jessen" wrote: Hi What do you mean saying that the macro hangs? Does it comes up with an error, and what does it says? I tried your code in excel 2000, even in a new sheet that i just added by macro and then activated before ending the macro. It never caused any trouble. Btw: You might want to set screenupdating=true in the macro you add by the first macro. //Per "donbowyer" skrev i en meddelelse ... Excel 2003. Win XP The subroutine below is called to programatically write code into a worksheet object sheet that is added to the sheets collection with an add sheet macro. On first use, it hangs at the second line Set SheetCodeModule. If I delete the new sheet and run the add sheet routine again, the WriteCode() subroutine works fine and the code now appears in the sheet object. Sub WriteCode() MySh = ActiveSheet.CodeName Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents(MySh).CodeModu le With SheetCodeModule CodeLine = .CountOfLines + 1 .InsertLines CodeLine, "Sub Worksheet_Activate()" & Chr(13) & _ "Application.ScreenUpdating = False" & Chr(13) & _ "If MyNewSheet = ""Running"" Then" & Chr(13) & _ "MyNewSheet = ""NotRunning""" & Chr(13) & _ "Go To Line2" & Chr(13) & _ "End If" & Chr(13) & _ "Call Load" & Chr(13) & _ "Line2:" & Chr(13) & _ "End Sub" End With Set SheetCodeModule = Nothing End Sub Any suggestions as to what is wrong would be welcome. -- donwb- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel hangs when copying sheet with charts to Powerpoint | Excel Discussion (Misc queries) | |||
Code hangs second time around | Excel Programming | |||
calculating cells hangs when filtering ranges in a sheet | Excel Worksheet Functions | |||
.NET code hangs setting up a chart | Excel Programming | |||
Code hangs Excel ... Why? ... and how can i debug?? | Excel Programming |