Re: Deleting Code from a Work*** is Locking Up

On Sep 4, 5:50 pm, Chip Pearson <c...@xxxxxxxxxxxx> wrote:
I'm not entirely clear what you're attempting to do, but as a general
rule, you should never use the VBE methods to modify the module that
contains the code that does the modification. E.g., don't put code in
Module1 that in any way modifies Module1. The results can be
unpredictable.

If you need to delete code from a *** module and initiate that
deletion from an event procedure in the same *** module, you should
use OnTime to schedule a procedure to run immediately when control
returns to Excel. Create a procedure like the following in a regular
code module:

Private VBCompToDelete As VBIDE.VBComponent

Sub DeleteTheComp()
    If VBCompToDelete Is Nothing Then
        Exit Sub
    End If
    If VBCompToDelete.Type = vbext_ct_Document Then
        With VBCompToDelete.CodeModule
            .DeleteLines 1, .CountOfLines
        End With
    Else
        VBCompToDelete.Collection.Remove VBCompToDelete
    End If
End Sub

Then, in your ***'s code module event procedure, use

Set VBCompToDelete = ThisWorkbook.VBProject.VBComponents( _
    ThisWorkbook.Worksheets("Sheet1").CodeName)
Application.OnTime Now, "'" & ThisWorkbook.Name & "'!DeleteTheComp"

This sets the public variable VBCompToDelete to Sheet1's VBComponent
and then uses OnTime to schedule the procedure to run. Since Now is
give as the time to run, it will run as soon as the current chain of
execution terminates and control returns to Excel.

If you are having event-related problems when copying worksheets, turn
off events, do your thing, and turn events back on.

Application.EnableEvents = False
' your code here
'Application.EnableEvents = True

See alsowww.cpearson.com/Excel/VBE.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

On Fri, 4 Sep 2009 13:13:25 -0700 (PDT), Keith <altnr...@xxxxxxxxx>
wrote:

On Sep 4, 4:08 pm, Keith <altnr...@xxxxxxxxx> wrote:
I have been spending many days trying to figure out what part of the
code is not working correctly.  I am using Excel 2003.  I followed the
instructions on the Ozgrid website (listed below).http://www.ozgrid.com/VBA/delete-***-code.htm

Sub DeleteSheetEventCode()
''Needs Reference Set To _
    "Microsoft Visual Basic For Applications Extensibility"
'Tools>References.

Dim s*** As Object, strName As String
        For Each sSheet In Sheets
            Select Case UCase(s***.Name)
                Case "SHEET1", "SHEET2", "SHEET3"
                    strName = s***.CodeName
                    With ThisWorkbook.VBProject.VBComponents
(strName).CodeModule
                            .DeleteLines 1, .CountOfLines
                    End With
                Case Else
                   'Whatever
            End Select
        Next s***
End Sub

I have checked the checkbox for Tools>>Reference>>Microsoft Visual
Basic for Applications Extensibility 5.3 in the VBA Project.
I also have checked the checkbox for
Tools>>Macro>>Security...>>Trusted Publishers>>Trust Access to Visual
Basic Project.

I run this macro and it works.  I try and run it in break mode and it
will go through two loops and then provide an error on the third.  I
call the macro, and it freezes inside of this macro (it never
completes).

I have tried this as a separate macro and called it, and also as part
of the regular macro.  I need to run code like this as my originating
worksheet is running the Worksheet_Change event macro and is causing
errors when this code is copied.

I am listing below the pertinent snippet of my code.

=================================================================================
    Windows(DSCName).Activate  

    shts = Application.Sheets.count    
    count = 2                              

    For Each Worksheet In Worksheets      

        Sheets(count).Activate
        ActiveSheet.Copy After:=Workbooks(DWORName).Sheets(count)

        Windows(DWORName).Activate                      

'=======================================================================================
        With ThisWorkbook.VBProject.VBComponents(Active***.CodeName).CodeModule
                .DeleteLines 1, .CountOfLines
        End With
'=========================================================================================

        Windows(DSCName).Activate  
        Active***.Range("Comments").Copy
        Windows(DWORName).Activate  
        Active***.Paste Destination:=Range("Comments")
        SendKeys ("^{HOME}")

        Windows(DSCName).Activate
        FirstDay = Active***.Range("WkDateMon").Value
        Windows(DWORName).Activate
        With Active***
            .Unprotect
            .Range("WkDateMon").Value = FirstDay
        End With

        SendKeys ("^{HOME}")                            ' Ctrl+Home
        Windows(DSCName).Activate

        If count = shts Then
            Workbooks(DSCName).Close SaveChanges:=False
            Windows(DWORName).Activate
        End If
        count = count + 1
    Next

Oooops.  This submitted before I could clean it up.  I hope it is
easier to read here.

I think I replied to author rather than a regular reply as I don't see
my update. Here are some additional pertinent information.

1. I am not trying to delete the work*** information from the
work***.
1a. I have the above macro in Module 1.
2. I am disabling events at the beginning of the macro and disabling
at the end. I have placed it right after the Dim statements and just
before the End Sub statement.
3. I feel like it is some issue outside of the macro that I need to
take care of.
3a. I can run the macro all by itself and it runs fine. It is only
when I call the macro, or insert the macro information within another
macro that it locks up.

As I get no error message, I am unsure what to do.
When I tried to troubleshoot this as a separate macro that is called,
it goes through 2 or 3 of the 4 worksheets then provides an error
(when in break mode only) that the information on the Internet seems
to say I should ignore. I just ran the code listed above. I had a
break point at Next s***. The macro made it to the break point
once. When I select the arrow to continue, it pops up an error that
says "Can't enter break mode at this time.". Without the break point
in the macro it does not give any error message; it just locks up.

Anybody have any ideas I could check?
.

Loading