PDA

View Full Version : Solved: Print Prompt For Form



Emoncada
05-16-2007, 02:30 PM
I thought this would be a easy search, but came up empty.
I have a form with a print button that I would like to get the Print Prompt, so I can select what printer and how many copies.

Simon Lloyd
05-16-2007, 02:34 PM
Emoncada i think its something like this!
Application.Dialogs(xlDialogPrint).Show

Emoncada
05-16-2007, 02:36 PM
That prints out the spreadsheet, but I want it for the UserForm.

Simon Lloyd
05-16-2007, 02:47 PM
The userform has a print for method like:
UserForm1.PrintForm

Emoncada
05-16-2007, 02:53 PM
Yes but just prints the userform once I need it to prompt the user how many copies wanted.

lucas
05-16-2007, 03:10 PM
I'm gonna throw this out there Em....why would you want to print a form?
Why not format a sheet for printing and input your data to a for print sheet.

mdmackillop
05-16-2007, 03:16 PM
This may be a sledgehammer to crack a nut, based on this KB item (http://vbaexpress.com/kb/getarticle.php?kb_id=528)

Emoncada
05-16-2007, 03:18 PM
This is apacking slip and needs to print in that form for the package then data is seperated on worksheet. Originally had it on a worksheet, but was able to add combo drop downs and works perfectly now except for the little print issue.

lucas
05-16-2007, 03:20 PM
Em,
Read through this before you decide...
http://www.vbaexpress.com/forum/showthread.php?t=11456&highlight=print+form

Emoncada
05-16-2007, 03:41 PM
MdMack is there a way to have that work for a form.
Lucas I saw that post, but would rather keep it in a form.
Is there a way a can call a module that has the print prompt so it can print the form?

mdmackillop
05-16-2007, 04:16 PM
My printer is non-functioning at the moment, but I'll try it at work tomorrow.

Emoncada
05-16-2007, 05:03 PM
Ok Md Thanks. Will look forward to it.

Emoncada
05-17-2007, 11:19 AM
Any Luck with this one?

Simon Lloyd
05-17-2007, 11:36 AM
Em, did you not take a look at the sample that Md supplied?, if you did you would have noticed this section:

Private Sub CommandButton1_Click()
Dim i As Long
If Printer = "" Or Printer = "Not Found" Then Exit Sub
Application.ActivePrinter = Printer
Do
UserForm1.PrintForm
i = i + 1
Loop Until i > Me.TextBox1.Value
Unload frmPrint
End Sub
this in effect loops through thye print form code until i is greater than the value typed in the TextBox!, take a look at the code he supplied trim everything you dont want about setting printers and hey presto sorted!!

Emoncada
05-17-2007, 11:47 AM
So then can i have a Inputbox Prompt to equal the value of i ?

Emoncada
05-17-2007, 11:57 AM
OK I am trying this
Dim l As Long, res As String
'If Printer = "" Or Printer = "Not Found" Then Exit Sub
'Application.ActivePrinter = Printer
res = InputBox("How many Copies?", "Printer")
Do
UserForm1.printform
l = l + 1
Loop Until l > res
Unload frmprint
End Sub

Emoncada
05-17-2007, 11:58 AM
but that's giving me a problem with "frmprint" it's saying it's not defined.

Emoncada
05-17-2007, 12:07 PM
oh ok i just saw the file. I am going to retry it.

Simon Lloyd
05-17-2007, 12:10 PM
Jeeeez! Bob i see what you mean now!

Em you will have a problem with frmprint because if your not using the workbook that Md posted you wont have a UserForm called frmprint!!!!!

Emoncada
05-17-2007, 12:29 PM
Ok i tried it the right way and got Not Found on both optionbuttons. Do i need to put my printer info somewhere for it to pick it up?

Emoncada
05-17-2007, 12:30 PM
I used MdMack's and it gave me the samething.

Simon Lloyd
05-17-2007, 12:35 PM
Sorry Em you gotta start thinking for yourself, take a look at the code Md supplied with a little investigation you will see that you can remove the settings for printer, once you have finished you will very little code left and something that will print on any printer you have connected, why not try some of the lines on their own and see what the do, you already know what this line does UserForm1.PrintFormjust a wild guess but probably a good place to start!

Emoncada
05-17-2007, 12:39 PM
I am able to have it work without all that printer stuff, but would of liked for it to pull up the printer info automatically how I thought the code would do.

Simon Lloyd
05-17-2007, 12:44 PM
It doesnt need to for a single connected printer as the code will just print to the default printer, maybe you could incorporate

Application.Dialogs(xlDialogPrint).Show

to change printers or if you really wanted to do a little investigation work search the Kb (http://vbaexpress.com/kb/default.php)for finding network printers!