Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Location
    New York, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically insert code into an Excel module. (Exc

    I have a VB6 application that outputs data into an Excel spreadsheet. The spreadsheet is highly formatted and contains several non-contiguous print areas which I want to print (on separate pages) via a Button that i also create dynamically using the following code:

    Dim BBB as Button
    set BBB = wsXL.Buttons.Add(25,25,80,25)
    BBB.Name = "PrintButton"
    BBB.Text = "Print"
    BBB.OnAction = "Print_Ranges"

    My question is: How do I now dynamically (from VB6) insert the code for the procedure "Print_Ranges", into the spreadsheet?

    Or ideally, is there a way for me to dynamically (from VB6) populate the "Click" event and create the PrintButton_Click code (which resides in the spreadsheet behind the Button) ?

    The code I want to insert into the "Click" event is as follows:

    Sub PrintButton_Click()
    Worksheets("xxx").PageSetup.PrintTitleRows = "$40:$41"
    Worksheets("xxx").PageSetup.Range("A40:Q61").Print out
    Worksheets("xxx").PageSetup.PrintTitleRows = "$65:$66"
    Worksheets("xxx").PageSetup.Range(A65:Q90").Printo ut
    ... Continues until all ranges have been printed...
    end sub


    Any suggestions would be greatly appreciated.
    A solution is desperately sought as this is part of a mission critical deliverable that is now overdue.

    Many Thanks,

    Nelson

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamically insert code into an Excel module. (Exc

    1) you could add the code to a file then open up the file, make any changes, etc, then save with a different name.

    2) Chip Pearson has some examples of how to do Programming To The VBE.

    Steve

  3. #3
    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: Dynamically insert code into an Excel module. (Exc

    Nelson,
    If yo upopulate an existing workbook, it is probably easiest to have the print macro and button already in the workbook. Simply change the macro to something like:
    <pre>Sub PrintData()
    With Worksheets("xxx")
    .PageSetup.PrintTitleRows = ThisWorkbook.names("PrintTitles1").RefersToRange.A ddress
    .Range("PrintRange1").Printout
    .PageSetup.PrintTitleRows = ThisWorkbook.names("PrintTitles2").RefersToRange.A ddress
    .Range("PrintRange2").Printout
    ... Continues until all ranges have been printed...
    End With
    end sub
    </pre>

    Your VB6 code can then adjust the named ranges as necessary, using code like:
    xlApp.wbk.Names.("PrintTitles1").RefersTo = "'xxx'!$40:$41"
    etc.

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Sep 2006
    Location
    New York, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically insert code into an Excel module.

    Dear Rory,

    Many thanks for the recommendation.
    This solution should work perfectly.

    Nelson

Posting Permissions

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