Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA del VBA (Office 2000 ExcelVBA)

    I have some VBA code run by a button on one spreadsheet, the code copies that page to a new workbook and saves under a different file name. The code also travels with the "copy" command, is there any way to prevent this from happening or how do I code the page copy without also copying the code? OR how do I delete the code from the new workbook?
    This is the code I use,
    Sheets("PackingDelivery Slip").Select
    Sheets("PackingDelivery Slip").Copy Before:=Workbooks(strFName2 & ".xls").Sheets(1)
    Doing it this way copies all the formats etc over to the new workbook.

    Any help much appreciated,
    Thanks
    JovialJohn

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA del VBA (Office 2000 ExcelVBA)

    Try the the following code :

    Sheets("PackingDelivery Slip").Copy Before:=Workbooks(strFName2 & ".xls").Sheets(1)

    With Workbooks(strFName2 & ".xls").VBProject.VBComponents("PackingDeliver y Slip").CodeModule
    .DeleteLines 1, .CountOfLines
    End With


    Note : There should be no need to select a Sheet to copy it if you are doing the action through VBA.

    Andrew C

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Lancaster, Pennsylvania
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA del VBA (Office 2000 ExcelVBA)

    Another option would be to use code similar to the following in the "This Workbook" object of the workbook with your "PackingDelivery Slip" worksheet:


    Option Explicit
    Dim MnuCopy As CommandBarButton
    Dim MnuCustom As CommandBar

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Custom Commands").DELETE
    End Sub

    Private Sub Workbook_Open()
    AddDaMenus
    End Sub

    Public Sub AddDaMenus()
    '
    'Set reference to new menu item
    Set MnuCustom = Application.CommandBars.Add("Custom Commands", msoBarTop, False, True)

    'Set refernce to sub-items in new menu
    'Controls property of CommandBarControl works here because
    'reference is to type msoControlPopup which contains a CommandBar object
    Set MnuCopy = MnuCustom.Controls.Add( _
    Type:=msoControlButton, _
    Temporary:=True)
    MnuCopy.Caption = "Copy"
    MnuCopy.Width = 100
    MnuCopy.OnAction = "ChooseCopy"
    MnuCopy.Style = msoButtonCaption



    MnuCustom.Visible = True
    MnuCopy.Visible = True



    MnuCustom.Enabled = True
    MnuCopy.Enabled = True



    End Sub

    Private Sub ChooseCopy()
    Sheets("PackingDelivery Slip").Select
    Sheets("PackingDelivery Slip").Copy Before:=Workbooks("Workbook2" & ".xls").Sheets(1)
    End Sub


    This code will create a commandbar for you at the top of the screen. Now, neither the buttons, nor your code will be part of the sheet that you are copying.

    Good luck!

    Andy S.

  4. #4
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA del VBA (Office 2000 ExcelVBA)

    Thanks for the tip Andrew, but it causes a
    Run-time error '9';
    Subscript out of range
    error. And I am baffled.

    Help?

    John

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Location
    Lancaster, Pennsylvania
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA del VBA (Office 2000 ExcelVBA)

    Hi John,

    There may be two causes for the error.
    #1 The first time you close the file, it will cause an error because it will try to "delete" a commandbar that has not yet been created (script out of range). The command bar will be created upon reopening of the file because it is tied to the workbook open event.

    #2 I substituted your destination workbook name, with a workbook that I named "workbook2", just to make it easier for me to test. Plug in your own workbook name where I used "workbook2", and you should no longer experience the "script out of range" error.

    If these two options do not work, then try compiling your project (the option is under the debug menu). Also, stepping through the code may tell you where the module is getting stuck. You may have set things up a little differently than I had understood from your message.

    Good luck,

    Andy S.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA del VBA (Office 2000 ExcelVBA)

    John, ther migh be problems with code names.

    Try the following which should copy your sheet to a new workbook, strip the code and save the file :

    Sub CopySheet()
    Sheets("PackingDelivery Slip").Copy
    With ActiveWorkbook.VBProject.VBComponents(Sheets(1).Co deName).CodeModule
    .DeleteLines 1, .CountOfLines
    End With
    ActiveWorkbook.SaveAs strFName2 & ".xls"
    End Sub

    Andrew

  7. #7
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA del VBA (Office 2000 ExcelVBA)

    Andy S.

    Tried what you suggested and it looks like I could make it work but the problem is that the original code already runs from a button on the page.

    Andrew,
    In the properties window I found that what I had to do was rename the sheet to a definitive name, then it could recognize the location from which to delete. I have 5 template spreadsheets that use the code so what I had to do was rename each instance of the Sheet22 ( "ShippingDelivery Slip" ) to "ShippingDelivery_Slip" as there were 5 template sheets "Sheet22" was also Sheet17 ( "ShippingDelivery Slip" )etc. so now it shows as ShippingDelivery_Slip ( "ShippingDelivery Slip" ).

    Thanks to both of you!

    I learned a bit from each of you

    John

  8. #8
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA del VBA (Office 2000 ExcelVBA)

    So the code now reads

    With ActiveWorkbook.VBProject.VBComponents("PackingDeli very_Slip").CodeModule
    .DeleteLines 1, .CountOfLines
    End With

    John

Posting Permissions

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