MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA code


Posted by Terry on January 23, 2002 11:41 AM

I have a multi-page VBA form. Does anyone know a way to print each page of the form from within VBA while the form is being used by the user? Basically when the user is done completing the form entries I want to print out each page of the form with the values that were selected by the user.


Posted by Joe Was on January 23, 2002 11:56 AM

The easy way would to have your form populate a sheet, which you set up as a report. Then your report can be printed. You can add code to print after all the data is entered and after printing re-set the report if needed? JSW

Posted by Terry on January 23, 2002 12:09 PM

Thanks Joe. I had thought of that but doing so loses all of the information that's on the form pages such as labels and other text values. You could recreate the entire form on a worksheet page and populate the fields as you state but that seemed like a lot of extra work compared to just snap shotting the form page to the printer. I was even going to research if you could simulate a Alt-Print Screen and then paste the image to a worksheet as an image but haven't had a chance to see if there is a way to do that yet.

Posted by Joe Was on January 23, 2002 12:22 PM

Try This Code!

This code needs to go on your forms click event, you may even add a form button to each sheet?


' This is the click event for UserForm1
Private Sub UserForm_Click()
UserForm1.PrintForm
End Sub

It sends a screen shot to the printer of your form. JSW

Posted by Joe Was on January 23, 2002 12:31 PM

Side note on this code.

I tried the, UserForm1.PrintForm code in several user forms and in different events. Each time I added just the code above to the end of a code block, each time it printed a clean screen shot of the user form. JSW

Posted by Terry on January 24, 2002 9:14 AM

Re: Side note on this code.

Joe:
Thanks, after much digging I did come across the Printform function. Now my probem is printing each page of a multipage form without the user having to click something. I am trying to write a windup routine so that just before the user exits the form they can click an option that would select each page of the mulitpage form and then do a Printform. I have ten pages in the multipage form so I would need to select/activate each individual page then issue the Printform with no object reference which supposedly just prints the form with the current focus. My problem now is finding out how to cycle through the pages of the form. I've tried Formname.Pages(index).Printform but VBA doesn't like that. I can't find anything in the VBA help on how to bring focus or select a page of a multipage form, it just says that if you reference any control on a page it will become the active page but that seems like a poor way of doing it.

Posted by Joe Was on January 24, 2002 3:16 PM

Re: Side note on this code.

I think the bet way to hide the print function from the user would be to hide the print coge in each form. As the user moves to the next form(page) use my code to print. You could even re-direct the print to a different network printer, bypassing the users default printer?

Your method would require a way to keep track of all the forms for a dump at the end. doing as it gos is more efficient. The PrintForm code is verry fast! JSW

Posted by Terry on January 28, 2002 9:55 AM

Re: Side note on this code.

Your method would require a way to keep track of all the forms for a dump at the end. doing as it gos is more efficient. The PrintForm code is verry fast! JSW

Good idea Joe, my problem is the user can go back and forth from one form to another making changes so I was hoping I would be able to print the forms once the user clicked the finished button I have provided since then I know they will not be making any further changes. Finding all the forms is relatively easy as they appear as the collection of pages, my problem is getting each one to gain focus so I can use the printform command. Thanks for your help though.