PDA

View Full Version : Print Userform



ironj32
02-12-2007, 01:50 PM
What is the code to print the entire UserForm. My form is quite tall and has a scroll bar so users can navigate through the entire thing. I have a cmdButton

Private Sub cmdPrint_Click()
frmCREContractForm.PrintForm
End Sub

this code only prints what is shown on the screen. i need it to print the entire form.
thanks!

lucas
02-12-2007, 02:01 PM
I have a question. Why would you print the entire form? Why not input the data from the form into a worksheet that's formatted to print.

ironj32
02-12-2007, 02:06 PM
because some users will want to print out the form, answer the questions, and fax it back.

Ivan F Moala
02-12-2007, 08:22 PM
because some users will want to print out the form, answer the questions, and fax it back.

That's why lucas sugested what the above, ie put data in sheet and print this.

moa
02-13-2007, 06:23 AM
Is there a way to print the whole form though? You're telling ironj32 to recreate his whole form, which sounds like a rather large form if it needs a scroll bar, in a sheet. Would be quicker just to print the entire form if possible.

ironj32
02-13-2007, 06:51 AM
yes, i think for this particular project it would be easier to simply print the entire form. sooo, if anyone would happen to know how to accomplish this that would be great.
thanks.

lucas
02-13-2007, 06:56 AM
Well....I'm not going to waste a lot of ink printing a lot of unnecessary grey form just to figure this out for one thing. I also don't think someone who is printing it will find it any better than a formatted page printed out with the data and a place provided for answers, etc. Quicker is not necessarily better.

Remember the form will be printed in it's entirety, buttons, boxes, etc. will all be in the printout. I'm not saying this can't be done. I just think there are much better, cleaner solutions for the problem.

lucas
02-13-2007, 07:05 AM
I see nothing in the help files except this on printform:

PrintForm prints all visible objects and bitmaps (javascript:hhobj_7.Click()) of the UserForm object.
Hopefully someone who has tried this before will have a solution. I don't think userforms are designed to be printed......

lucas
02-13-2007, 07:15 AM
Also if you fax it, what color do you think the form will be in the fax?


because some users will want to print out the form, answer the questions, and fax it back.

malik641
02-13-2007, 07:27 AM
Building sheets from code can be a large task if the sheet is built from scratch. But you could set up a template sheet and just copy the template sheet and populate it with the necessary information. Then add some printing preferences via code, and print. The user doesn't even have to seen anything if you don't want them to. Plus with a sheet to print, if you do want the user to see it, they can distribute the sheet to other workers or keep it as reference (rather than hard copy).

Besides, it looks way more professional.

And about printing the whole form that has scroll bars to display everything...I don't think it is possible. How is VBA supposed to know how to stretch out your userform to display all the data? And what about multipages? If you can print out the 'whole' form, what about those? They probably all won't need to be stretched out the same way. Just doesn't seem worth it, really. But that's just me.

I say use a template sheet and populate it from the userform, then print.

johnske
02-13-2007, 07:31 AM
Doing what you want would be more trouble than it's worth - following Steve's suggestion is the better option :)

moa
02-13-2007, 07:54 AM
Sounds like more of a hassle making an otherwise useless sheet that is essentially a list of all the text box captions from the form with spaces next to each one for data entry. hello, sounds like a form but on paper...

Try making the hieght of the form equal the scroll height of the form, print it then set it back to the original height.

Lucas has a really good point about printing a grey form though...

malik641
02-13-2007, 08:14 AM
I guess it just depends on what ironj32 wants to do with it.

The only reason I would print a form is in the design process of the form. Just something I can scribble on to decide how I want it to look. I see no other meaningful purpose for it. I would not print the form if it's going to be used for presenting purposes. That, to me, is unacceptable.

moa
02-13-2007, 08:26 AM
Seems like he wants to print it and use it like a questionaire that people (who possibly don't have access to the app) can fill in and fax/send back to him. If a form is set out well enough it can look pretty good when printed.

malik641
02-13-2007, 08:41 AM
I see your point, Glen. But the App must do something else...otherwise, why not just make this 'questionaire' (or whatever it is) in word?


because some users will want to print out the form, answer the questions, and fax it back.
ironj32,
What does the form do?

moa
02-13-2007, 08:56 AM
Just read the other post by ironj32. It is a questionnaire.

ironj32
02-13-2007, 08:57 AM
thanks for all your reply's...
i want the UserForm set up so i can email out the questionaire...Managers open it, complete it, and click "return" and it will be sent back to me. Once the data is returned to me I am going to be putting it into an Access Database that i have set up. However, some people would rather print it out, complete it, and fax or mail it back. Also, would like to use a UserForm because many times I get questionaires returned to me only partially completed...then i have to contact the manager and ask them all the questions by phone. I am hoping to set the user form up so that they must answer all the questions before it can be submitted back to me.

ironj32
02-13-2007, 08:58 AM
so you guys are saying that i should have another worksheet in the workbook that is more "printer friendly". then two buttons...one that will open the UserForm...and one that print out a "printer friendly" form?

malik641
02-13-2007, 09:15 AM
Just curious, what does the form do when the user presses "Return"? Does it send you an email of the workbook that you sent them? Or do you put the information somewhere else and email that to yourself?


I am hoping to set the user form up so that they must answer all the questions before it can be submitted back to me.
That's easy enough, just place a condition at the top of the code for the command button "Return" that checks if everything is filled in.


so you guys are saying that i should have another worksheet in the workbook that is more "printer friendly". then two buttons...one that will open the UserForm...and one that print out a "printer friendly" form?
If you're looking to make users have to fill out the form before sending it to you, I'd start with that first. If you have customers that wouldn't bother filling it out on the computer and you really want to give that option, maybe you should setup the worksheet ("printer friendly") and send out that sheet to those specific customers rather than the form. Then let them print it out. Why bother giving them the form if they're not going to use it (or even partially use it? ...you can also still have check boxes on worksheets and radio buttons and whatnot).

I don't know why people want to do extra work. They would print it out, write on it by hand, then go to the fax machine, dial your number, and fax it. Or they can fill out your form, and press return and it's done.

ironj32
02-13-2007, 09:27 AM
By pressing return I want the data to be copied/saved into a worksheet "answers" then the workbook to be sent back to me. Actually, I really only need the worksheet "answers" that contains all the data to be sent back to me.

The only reason I want users to be able to print it out is because some of them are unable to open up the UserForms (macro setting probably?) Just figured that it would be simpler to have the print option, then to have them change their settings.

This is really my first time going into any real depth with Excel...never really used it much before.

ironj32
02-13-2007, 09:29 AM
I am basically looking for the easiest way to send out a great number of these questionaires (probably around.....2 thousand or so). I am hoping to set something up in excel or access where i can click on the managers name and it will automatically send out the survey to them.

lucas
02-13-2007, 10:06 AM
The only reason I want users to be able to print it out is because some of them are unable to open up the UserForms (macro setting probably?) Just figured that it would be simpler to have the print option, then to have them change their settings.

If they can't open the file and run the userform how are they going to print it?
Sounds like you just need a spreadsheet form with no macro's or userforms.......

Spreadsheets are highly configurable as far as creating forms.

ironj32
02-13-2007, 10:40 AM
You are right.

malik641
02-13-2007, 10:41 AM
You should have 2 sheets, now that I think about it.
Sheet1 will be what the user sees.

Sheet2 should have Column headings that are equivalent to your access database table. And (in the excel file) under the headings will be cells that reference the data you want from sheet1. So when you get the workbook back to you, just unhide / unprotect Sheet2 and copy and paste into your access database table. You can even do that by code if you really wanted.