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 -