My code crashes excel

Hi,

The first time that I run this code it creates the new sheet and adds the code to it fine but the second time that I run it it will crash excel and exit the program.
I have tried writting the code in different ways that hasn't helped. Here is the code:


Dim newWorkSheetName As String

On Error Resume Next
'Asks the user to input the Name of the new schedule.
newWorkSheetName = InputBox("Please input the name of the new Schedule:", _
"New Worksheet Name")
ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = newWorkSheetName
Dim VBCodeMod As CodeModule
Dim InsertLineIndex As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(Worksheets(newWorkSheetName).CodeName).CodeModule
With VBCodeMod
InsertLineIndex = .CountOfLines + 1
.InsertLines InsertLineIndex, "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & Chr(13)
InsertLineIndex = InsertLineIndex + 1
.InsertLines InsertLineIndex, _
" Call sheetDoubleClick(ActiveSheet.Name)" & Chr(13)
InsertLineIndex = InsertLineIndex + 1
.InsertLines InsertLineIndex, "End Sub" & Chr(13)
'more code but very similar
End With
Set VBCodeMod = Null


Since it crashes excel I don't get a chance to see an error message or have a change to debug it

Any help or suggestions would be fantastic. I am VERY stuck

Ben

Originally Posted by ben_sumner

Hi,

The first time that I run this code it creates the new sheet and adds the code to it fine but the second time that I run it it will crash excel and exit the program.
I have tried writting the code in different ways that hasn't helped. Here is the code:


Dim newWorkSheetName As String

On Error Resume Next
'Asks the user to input the Name of the new schedule.
newWorkSheetName = InputBox("Please input the name of the new Schedule:", _
"New Worksheet Name")
ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = newWorkSheetName
Dim VBCodeMod As CodeModule
Dim InsertLineIndex As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(Worksheets(newWorkSheetName).CodeName).CodeModule
With VBCodeMod
InsertLineIndex = .CountOfLines + 1
.InsertLines InsertLineIndex, "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & Chr(13)
InsertLineIndex = InsertLineIndex + 1
.InsertLines InsertLineIndex, _
" Call sheetDoubleClick(ActiveSheet.Name)" & Chr(13)
InsertLineIndex = InsertLineIndex + 1
.InsertLines InsertLineIndex, "End Sub" & Chr(13)
'more code but very similar
End With
Set VBCodeMod = Null


Since it crashes excel I don't get a chance to see an error message or have a change to debug it

Any help or suggestions would be fantastic. I am VERY stuck

Ben

Do I presume there is an 'On Error Goto 0' even though it's not shown, or could you be looping on some event that should have been suppressed?

(just a thought, albeit it not a very advanced one)

Thanks but that isn't it

Thanks..I gave it a go and it worked fine but didn't add the code to worksheets. There is a second problem with with finding the codename of the sheets. The first time you run it, it will return a blank value. All othertimes it works fine.

I solved the first problem....Its was because I had the form open in the background and never closed it after then new sheet had been inserted. I tried a simple hide then show again (because i need the form to be open at all times) but that did't work either. What worked was hiding the form and then opening it using module that only had form.show in it and calling it using the ontime method 1 second after I close the form.

I know that it sounds crazy and i'm not sure the reasoning behind it working ut its works and im happy.

Thanks for all your help Bryan

Originally Posted by ben_sumner

Thanks..I gave it a go and it worked fine but didn't add the code to worksheets. There is a second problem with with finding the codename of the sheets. The first time you run it, it will return a blank value. All othertimes it works fine.

I solved the first problem....Its was because I had the form open in the background and never closed it after then new sheet had been inserted. I tried a simple hide then show again (because i need the form to be open at all times) but that did't work either. What worked was hiding the form and then opening it using module that only had form.show in it and calling it using the ontime method 1 second after I close the form.

I know that it sounds crazy and i'm not sure the reasoning behind it working ut its works and im happy.

Thanks for all your help Bryan

Good to see, as you observed, it was a little over my '10%'.

Thanks for the response.