Vba deleting vba code throwing an error

[chaostheory]

Ok so we have a spreadsheet with like 15 tabs, oodles of code and triggers and subs that run when you change tabs etc to pull data from databases to populate drop down menus. At any rate...the file is 5mb and we will be generating hundreds of these. So to cut down on space i wrote a macro to run through the spreadsheet and delete all unused tabs which worked great. But the problem is we have code in the This.Workbook that now goes defunct cause most of the tabs done exist. I googled around a lot to try and find out how to delete code and i have one working. But there is a weird problem. It always errors out the first time run, then when you rerun it...it runs perfect. Im racking my brain and can't figure it out, it's stuff i have never dealt with before.

Sub saveDeleteWorkbookCode()
Dim x As Integer
[B]With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule[/B]
[B].DeleteLines 1, .CountOfLines[/B] 
[COLOR=red]I get two different errors depending if i try to debug or just run it.[/COLOR]
[COLOR=red]This line gives me a "Can't enter break mode at this time" when i try to F8[/COLOR]
[COLOR=red]through it.  But it did delete the code from This.Workbook like it was [/COLOR]
[COLOR=red]supposed to.  But the error halts it every time.[/COLOR]
[COLOR=#ff0000]If i insert the code and just run it.  I get a "compile error, expected end [/COLOR]
[COLOR=#ff0000]property" but once again, it does delete the code, then if i rerun it, it runs[/COLOR]
[COLOR=#ff0000]perfectly to end because the code is gone.[/COLOR]
End With
Call saveOptimization
End Sub
 
Sub saveOptimization()
Dim R As Integer, C As Integer
Dim theForm As String, formName As String
theForm = Sheets("Main Menu").Range("C3")
Application.DisplayAlerts = False
Worksheets("Form Info").Visible = True
Worksheets("Form Info").Activate
For R = 1 To ActiveSheet.UsedRange.Rows.count
    If Range("A1").Offset(R, 0) = "Form Selection on Main Menu" Then
        titleRow = R
    End If
    If Range("A1").Offset(R, 0) = theForm Then
        For C = 1 To ActiveSheet.UsedRange.Columns.count
        Worksheets("Form Info").Activate
        formName = Range("A1").Offset(titleRow, C)
            If Range("A1").Offset(R, C).Value <> "X" And Range("A1").Offset(R, C).Value <> "M" Then
                If formName = "Form Info" Then
                    Exit For
                End If
                Worksheets(formName).Delete
            End If
            If Range("A1").Offset(R, C).Value = "X" Or Range("A1").Offset(R, C).Value = "M" Then
                Worksheets(formName).Activate
                ActiveSheet.Unprotect
                Range("A1:IV30000").Select
                Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, _
                SkipBlanks:=False, _
                Transpose:=False
                ActiveSheet.Protect
            End If
        Next C
    End If
    If formName = "Form Info" Then
        Exit For
    End If
Next R
Worksheets("Save").Activate
ActiveWorkbook.Save

 

[chergh]

I'm a bit drunk at the moment but I don't think you can enter break mode when changing the IDE. Does it work if you just wrap the delete lines code with on error resume next statement and then on error goto 0 and just run the code rather than stepping through or entering break mode?

 

[chaostheory]

No i posted the error for that above too. If i put in the code and close the editor and try to run it i get "Compile Error: Expected End Property". It has to be something specific to this workbook because if i create a blank one, throw in some code under ThisWorkbook, and then create a module and put in the delete code, it works fine. We can't figure it out. Were most likely just going to work around it somehow.

On error resume next and on error goto 0 don't stop either of the errors from popping up either. Nor does application.displayalerts = false.

 

[chaostheory]

No i posted the error for that above too. If i put in the code and close the editor and try to run it i get "Compile Error: Expected End Property". It has to be something specific to this workbook because if i create a blank one, throw in some code under ThisWorkbook, and then create a module and put in the delete code, it works fine. We can't figure it out. Were most likely just going to work around it somehow.

On error resume next and on error goto 0 don't stop either of the errors from popping up either. Nor does application.displayalerts = false.

 

[chergh]

I can't replicate the error but I guess it may be related to workbook events being triggered after deleting the code module. Try using:

Sub saveDeleteWorkbookCode()
Dim x As Integer

application.enableevents = false
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines 
End With

Call saveOptimization
application.enableevents = true
End Sub