Generating and Printing a PDF from a Dynamic Form

[insomniac53]

I've written the code for a form which dynamically creates (and later removes) rows of text boxes. I now want either to print it or produce a pdf using vba - I don't want to use ALT-PRTSCR to copy and paste into another program. I would like a command button that does the trick. Previously, I've used:

Application.Dialogs(xlDialogPrint).Show

and this printed the underlying data tied to a worksheet. In this case, there is no single underlying sheet - the data is taken from various sheets and manipulated in memory. When I tried this earlier and used the Print Dialog command, Excel stopped working and just kept beeping at me. I've looked at other threads, such as:

http://www.mrexcel.com/forum/excel-questions/737916-visual-basic-applications-save-userform-pdf.html

but before I get too deep, I would like to know if the experts here think this is possible, and if it is, what's the best way of tackling it?
Thanks.

 

[insomniac53]

I thought I'd answer my own question just in case someone stumbles across the thread in the future. The answer is no, as far as I can see - it isn't possible, or at least manageable by ordinary mortals, to print data from a dynamic form. The form data has to be tied to a sheet. This is frustrating as the whole point of my particular form was to dispense with the worksheet. However, needs must, so I had to copy the form data back to a sheet. Once there, the other two issues were printing and exporting to pdf.
Printing: this was straightforward with the 'Application.Dialogs...' line shown in the original post. Just make sure the correct sheet is activated.
Exporting: this was more tricky, but not terribly so. There are threads about saving to a PDF file, and after some research, this is the simplest code I found, placed in the appropriate form control button:

    Dim w As Worksheet
    Dim fname As String
    Set w = Sheets("[your sheet name here")
    w.Activate
    fname = PDF_FILEPATH & w.Range([your file name here]) & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fname, Quality:=xlQualityStandard, IncludeDocProperties:= _
        True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    MsgBox "File Saved"

It doesn't offer a dialogue box to enter a new filename, but in my case I didn't need it. The file name is in the worksheet and the path is in the string constant PDF_FILEPATH. What amazed me is that it worked, first time....