How do you insert code into a standard VBA module from within a running VBA macro? RRS feed

  • Question

  • I'm running a macro from Excel that takes information from a row in an Excel database and populates a Word document with it. (Users click a button on the row to generate the document.)  All I need to do is to pass the row number to the subroutine that creates the Word document, but I'm having to deal with all sorts of workarounds because I can't find a way to simply do that when the button is clicked.

    I first tried using Active X command buttons, since they have self-aware event handlers, but found the text frame's generous margins didn't allow the button to display any text in the standard narrow Excel row size.  (I tried changing the margins, but couldn't.)

    Then I tried using Form Control Command buttons, because their margins can display the text, but those won't allow me to pass any parameters to the Word macro (I can just launch simple macros without any parameters), and I haven't found a way for the macro to figure out which button called it.

    So I've resorted to creating a separate macro (named after the button calling it -- e.g., Button4_click) for each button (a kludge, but I don't see any alternatives).  Unfortunately, there may be hundreds of rows in the database, so I would like to automatically generate the macro code when I create the buttons.  It would look something like this:

    Sub Button4_Click()

    CreateWordDetail ActiveSheet.Shapes("Button 4").TopLeftCell.row, ActiveSheet

    End Sub

    The question is what syntax do I use to insert these code snippets into a VBA module when I'm creating the buttons?  I don't want to copy and paste the above into a module manually, because, as I said, there may be hundreds on a sheet.

    Thursday, November 19, 2015 4:13 AM

All replies