Deleting Code from a Worksheet is Locking Up

K

Keith

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-sheet-code.htm

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

Dim sSheet As Object, strName As String
For Each sSheet In Sheets
Select Case UCase(sSheet.Name)
Case "SHEET1", "SHEET2", "SHEET3"
strName = sSheet.CodeName
With ThisWorkbook.VBProject.VBComponents
(strName).CodeModule
.DeleteLines 1, .CountOfLines
End With
Case Else
'Whatever
End Select
Next sSheet
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 ' This brings
the focus to the workbook.

shts = Application.Sheets.count ' Count the
number of sheets in the workbook.
count = 2 ' Sets the
default. This is a counter for the number of sheets being cleared.

For Each Worksheet In Worksheets ' Reminder the
focus is currently on the DSC file when entering this loop.

Sheets(count).Activate
ActiveSheet.Copy After:=Workbooks(DWORName).Sheets(count) '
Copies the worksheet from the DSC file to the DWOR file.

Windows(DWORName).Activate ' Ensures the
focus is on the DWOR file.
' DeleteProcedureCode Workbooks(DWORName), DSCTab,
"Worksheet_Change" ' Deletes the Procedure in the Worksheet that is
activated when any changes is completed. This was just copied from
the DSC spreadsheet.
' Call DeleteSheetCode ' Deletes all
of the code in each of the sheets. The main intent is to remove code
copied from the DSC (such as the "Worksheet_Change" event).

'=======================================================================================
' Needs Tools>References Set To "Microsoft Visual Basic For
Applications Extensibility".
' This will delete all of the code from each sheet.
' This is required to ensure the "Worksheet_Change" event is deleted
when it is copied from the DSC file.

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

Windows(DSCName).Activate ' Returns the
focus to the DSC file after having pasted the sheet to the DWOR file.
ActiveSheet.Range("Comments").Copy ' Copies the
data in the Comments section. This is done separately because it can
easily contain more than 255 characters and that is all that is copied
per cell when copying the sheet.
Windows(DWORName).Activate ' Moves the
focus to the DWOR file.
ActiveSheet.Paste Destination:=Range("Comments") ' Pastes the
Comments information from the DSC file to the DWOR file.
SendKeys ("^{HOME}") ' Ctrl+Home

Windows(DSCName).Activate ' Returns the
focus to the DSC file after having pasted the sheet to the DWOR file.
FirstDay = ActiveSheet.Range("WkDateMon").Value ' Copies the
data values in the Dates section. This is done separately because we
don't want the formula; we want to have the values.
Windows(DWORName).Activate ' Moves the
focus to the DWOR file.
With ActiveSheet
.Unprotect ' Unprotects
the worksheet on the DWOR file.
.Range("WkDateMon").Value = FirstDay ' Pastes the
Date values from the DSC file to the DWOR file. (This can be compared
to the dates in the DWOR then.)
End With

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

If count = shts Then ' Checks to
see if we have copied the last sheet in the DSC workook.
Workbooks(DSCName).Close SaveChanges:=False ' Closes the
DSC file.
Windows(DWORName).Activate ' Focus is
moved to the DWOR file.
End If
count = count + 1 ' This will
set the sheet number to 2 thereby skipping the first tab labeled
"Instructions".
Next
 
K

Keith

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-sheet-code.htm

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

Dim sSheet As Object, strName As String
        For Each sSheet In Sheets
            Select Case UCase(sSheet.Name)
                Case "SHEET1", "SHEET2", "SHEET3"
                    strName = sSheet.CodeName
                    With ThisWorkbook.VBProject.VBComponents
(strName).CodeModule
                            .DeleteLines 1, .CountOfLines
                    End With
                Case Else
                   'Whatever
            End Select
        Next sSheet
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(ActiveSheet.CodeName).CodeModule
                .DeleteLines 1, .CountOfLines
        End With
'=========================================================================================

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

        Windows(DSCName).Activate
        FirstDay = ActiveSheet.Range("WkDateMon").Value
        Windows(DWORName).Activate
        With ActiveSheet
            .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
Click to expand...

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

Chip Pearson

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 Sheet module and initiate that
deletion from an event procedure in the same Sheet 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 Sheet'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 also www.cpearson.com/Excel/VBE.aspx


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

Keith

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 Sheet module and initiate that
deletion from an event procedure in the same Sheet 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 Sheet'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)
Click to expand...

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 worksheet information from the
worksheet.
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 sSheet. 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?
 
K

Keith

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 worksheet information from the
worksheet.
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 sSheet.  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?
Click to expand...

I am sorry. There is one more bit of information that is pertinent.
I need to delete the worksheet code immediately after I copy it. This
is because I am selecting and copying information that could be
enacting the Worksheet_Change event. (I know that I have turned off
the enable events, but I get lost in finding a better time to do it as
I have to enable the events at the end of the macro or it could affect
how the other users use Excel with enable events remaining turned off.
 
K

Keith

I am sorry.  There is one more bit of information that is pertinent.
I need to delete the worksheet code immediately after I copy it.  This
is because I am selecting and copying information that could be
enacting the Worksheet_Change event.  (I know that I have turned off
the enable events, but I get lost in finding a better time to do it as
I have to enable the events at the end of the macro or it could affect
how the other users use Excel with enable events remaining turned off.
Click to expand...

Whatever the issue is, I am unable to figure it out. I am going to
just turn the "EnableEvents" off during all of my macros and turn it
back on at the end.

This will solve my issue while the macro is running but leaves the
issue open if anybody updates the sheet later.

This is as close as I can come without identifying what the underlying
cause is that is locking the spreadsheet. The only thing I could
verify is that it would run the deletion of the code without problem,
but it was not successful in coming back to the macro after it did
that.

Thank you for the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove code from a worksheet before emailing 12
Change event code Find .Activate does not activate Target.value 4
Put worksheet names from inactive file into cells of active worksh 2
Programically remove code from a worksheet 3
Moving to newly Saved Workbook 3
code help 0
Copy Data from Closed File to Existing File 0
Error Handling Problem calling Macro from Other Worksheet 6

Top