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).CodeModule
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

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" <(E-Mail Removed)> skrev i en meddelelse
news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
> 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).CodeModule
> 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

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" <(E-Mail Removed)> skrev i en meddelelse
> news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
> > 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).CodeModule
> > 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
>
>
>

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).CodeModule
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 <donbow...@discussions.microsoft.com>
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 bythe
> > first macro.
>
> > //Per
>
> > "donbowyer" <donbow...@discussions.microsoft.com> skrev i en meddelelse
> >news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
> > > 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).CodeModule
> > > * 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 -

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).CodeModule
> 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 <donbow...@discussions.microsoft.com>
> 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" <donbow...@discussions.microsoft.com> skrev i en meddelelse
> > >news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
> > > > 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).CodeModule
> > > > 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 -
>
>

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 <donbow...@discussions.microsoft.com>
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).CodeModule
> > * *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 <donbow...@discussions.microsoft.com>
> > 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" <donbow...@discussions.microsoft.com> skrev i en meddelelse
> > > >news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
> > > > > 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 withan 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).CodeModule
> > > > > * 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 -

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 <donbow...@discussions.microsoft.com>
> 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).CodeModule
> > > 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 <donbow...@discussions.microsoft.com>
> > > 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" <donbow...@discussions.microsoft.com> skrev i en meddelelse
> > > > >news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
> > > > > > 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).CodeModule
> > > > > > 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 -
>
>

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" <(E-Mail Removed)> skrev i en meddelelse
news:3434C39E-C966-4BCD-814D-(E-Mail Removed)...
> 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 <donbow...@discussions.microsoft.com>
>> 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).CodeModule
>> > > 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 <donbow...@discussions.microsoft.com>
>> > > 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" <donbow...@discussions.microsoft.com> skrev i en
>> > > > > meddelelse
>> > > > >news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
>> > > > > > 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).CodeModule
>> > > > > > 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 -
>>
>>

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" <(E-Mail Removed)> skrev i en meddelelse
> news:3434C39E-C966-4BCD-814D-(E-Mail Removed)...
> > 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 <donbow...@discussions.microsoft.com>
> >> 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).CodeModule
> >> > > 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 <donbow...@discussions.microsoft.com>
> >> > > 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" <donbow...@discussions.microsoft.com> skrev i en
> >> > > > > meddelelse
> >> > > > >news:F1E0232E-060A-4FA0-AECD-(E-Mail Removed)...
> >> > > > > > 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).CodeModule
> >> > > > > > 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 -
> >>
> >>
>
>
>