Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Crashes Excel (XL2002 SP1)

    Hi,

    Could somebody please tell me why the following code kills Excel ?

    I want to copy the current sheet to a new workbook by clicking on a custom toolbar button and the following code is one of the several options I have tried. The problem is that if there is event-handling code behind the sheet being copied, that code is naturally also copied to the new workbook, but then causes errors because procedures referred to by that code are not present in the new workbook. Of course the event handlers still try to run in that new book, but they are no longer needed in the copied sheet. I want to strip out the event handlers if they are present on whichever sheet I am copying to the new workbook. Any ideas please ?

    <font color=red>
    Set CurrentBook = ThisWorkbook
    Set shtOrig = ActiveSheet
    OrigSheetCodeName = shtOrig.CodeName

    Sheets.Add
    Set shtCode = ActiveSheet
    Set rng = shtCode.Cells(1, 1)

    Set prj = ThisWorkbook.VBProject

    'copy and trim the code out from behind the current sheet if there is any
    For Each cm In prj.VBComponents
    If cm.Name = OrigSheetCodeName Then
    nLines = cm.CodeModule.CountOfLines
    If nLines > 0 Then
    For i = 1 To nLines
    rng.Offset(i - 1, 0) = cm.CodeModule.Lines(i, 1)
    Next i
    cm.CodeModule.DeleteLines 1, nLines
    Exit For
    End If
    End If
    Next cm

    shtOrig.Copy 'copy the original sheet to a new workbook
    Set NewBook = ActiveWorkbook

    'now replace the code
    CurrentBook.Activate
    If nLines > 0 Then
    For Each cm In prj.VBComponents
    If cm.Name = OrigSheetCodeName Then
    For i = 1 To nLines
    cm.CodeModule.InsertLines i, rng.Offset(i - 1, 1)
    Next i
    Exit For
    End If
    Next cm
    End If
    </font color=red>

    Thanks,

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Code Crashes Excel (XL2002 SP1)

    Hi Adrian,
    Try something like:
    <pre> Dim wks As Worksheet, prj As VBProject
    Application.EnableEvents = False
    Set wks = ActiveSheet
    wks.Copy
    Set wks = ActiveSheet
    Set prj = wks.Parent.VBProject
    With prj.VBComponents(wks.CodeName)
    .CodeModule.DeleteLines 1, .CodeModule.CountOfLines
    End With
    Application.EnableEvents = True
    Set prj = Nothing
    Set wks = Nothing
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Crashes Excel (XL2002 SP1)

    Isn't it much easier to just create an empty sheet in the target workbook and copy all cells to it?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Crashes Excel (XL2002 SP1)

    The code doesn't crash when I try it, but there are a few problems:

    1) The first Exit For should be between the two End If lines.
    2) In the code to put the code back, you should have rng.Offset(i - 1, 0) instead of rng.Offset(i - 1, 1).

    Wouldn't it be easier to copy the worksheet and then remove the code from the copy, instead of removing the code from the original, and putting it back after copying?

    Perhaps your problem is caused by interference with event procedures. Try inserting

    Application.EnableEvents = False

    at the beginning of the code, and

    Application.EnableEvents = True

    at the end.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Crashes Excel (XL2002 SP1)

    I had tried that, but it didn't suit me because some of the sheets contained charts which I wanted to be copied to the new workbook. The PasteSpecial options were however very useful in this case :
    <font color=red>
    rngTgt.PasteSpecial xlPasteValuesAndNumberFormats
    rngTgt.PasteSpecial xlPasteFormats
    rngTgt.PasteSpecial xlPasteColumnWidths</font color=red>

    I have posted a working solution against one of the other replies in this thread.

    Thanks,

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Crashes Excel (XL2002 SP1)

    Thanks Rory, Hans and Jan Karel. The key was definitely the <font color=red>Application.EnableEvents = False</font color=red> statement. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    The code that I originally posted was indeed clumsy and did crash every time it was run on a sheet that had some event-handling code. I had previously tried sht.Copy and then tried deleting the underlying code, but without disabling the events, it wouldn't work. I then tried what Jan Karel suggested, but this didn't copy the charts.

    As for the two errors that Hans pointed out, it took me a while to work out why the first <font color=red>Exit For</font color=red> should move ! The second error was a typo on my part and wasn't in the code that I was running.

    For the benefit of anyone who may be interested in this thread, my final solution including some features like removing all the checkboxes, selected textboxes, macro buttons, etc., is shown below. I also wanted the charts to no longer be linked to the source workbook. Any other extraneous links are also removed as well as any named ranges.
    <font color=red>
    . . .
    Application.EnableEvents = False

    Set wks = ActiveSheet
    wks.Copy

    Set wks = ActiveSheet
    Set bk = ActiveWorkbook
    Set prj = wks.Parent.VBProject

    With prj.VBComponents(wks.CodeName)
    .CodeModule.DeleteLines 1, .CodeModule.CountOfLines
    End With

    For Each shp In wks.Shapes

    'Debug.Print shp.Name & ", " & shp.Type

    Select Case shp.Type

    Case 1 'the boxes that I use to trigger macros for hiding detail, etc. - delete them
    shp.Delete
    Case 8 'MSoForm items like checkboxes and macro buttons - delete them
    shp.Delete
    Case 3 'graphs
    'do nothing - graphs handled below
    Case 17 'text boxes

    If InStr(1, UCase(shp.Name), "GRAPH") = 0 Then 'all text boxes that don't have "graph" somewhere in their name will be deleted (keep graph annotations textboxes)
    shp.Delete
    End If

    End Select

    Next shp

    'run through the graphs and turn the series data links into values so there is no link created back to the source workbook
    For Each co In wks.ChartObjects
    RemoveChartLinks co
    Next co

    'delete any named ranges that went with the sheet into the new book
    For Each nm In bk.Names
    nm.Delete
    Next nm

    var = bk.LinkSources(xlLinkTypeExcelLinks)

    For i = 1 To UBound(var)

    bk.BreakLink var(i), xlLinkTypeExcelLinks

    Next i
    . . .

    and the procedure to "unlink" the chart series from their source data ranges

    Sub RemoveChartLinks(co As ChartObject)

    Dim SeriesCount As Integer
    Dim i As Integer

    On Error Resume Next

    With co.Chart
    'get number of series and then go through each series
    SeriesCount = .SeriesCollection.Count
    For i = 1 To SeriesCount
    With .SeriesCollection(i)
    'change to values
    .Name = .Name
    .XValues = .XValues
    .Values = .Values
    End With
    Next i
    End With

    End Sub</font color=red>

    Thank all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •