Print userform on 8x14 possible ?

Hi all,

I was wondering if it's possible to print an userform on a 8x14 paper. I do not know if Excel is limited on this side because the userform I works on was populated with many fields, but also seems to stop at some length. I believe I reached this length as I cannot make it longer and longer. If it's also possible, it's great.

Thanks for any help you can provide.
Cheers
currentdb
LVL 1
currentdbAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Try this.

Paste this code in a module.

'~~> Code by Tom Ogilvy
Public Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Public Const VK_SNAPSHOT = 44
Public Const VK_LMENU = 164
Public Const KEYEVENTF_KEYUP = 2
Public Const KEYEVENTF_EXTENDEDKEY = 1

Open in new window


and this in the Command Button click event.

Private Sub CommandButton1_Click()
    DoEvents
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    DoEvents
    Workbooks.Add
    Application.Wait Now + TimeValue("00:00:01")
    ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
    DisplayAsIcon:=False
    ActiveSheet.Range("A1").Select
    ActiveSheet.PageSetup.Orientation = xlLandscape
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWorkbook.Close False
End Sub

Open in new window


This will print the Userform in Landscape mode. You can then use the Workbook's page setup to print it in the size that you want.

Sid
0
 
SiddharthRoutCommented:
Here is the updated workbook. Please change the setting using page setup before you print.

Sid
Book2.pdf
Currentdb-v5a.xlsm
0
 
patrickabCommented:
currentdb,

Set the Height and Width properties of the Userform to:

Height = 14*72 =1008
Width = 8 * 72 = 576

As the size is defined in 'points' and there are 72 points per inch.

Patrick
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
patrickabCommented:
currentdb,

Having set the properties of the Userform remember to use a printer that can cope with 8 x 14 paper, and that you have set the paper dimensions in the printer set up.

BTW there are relatively few printers that can cope with paper much larger than A4 and 8 x 14 it is much larger than A4 - at least in one dimension.

Patrick
0
 
currentdbAuthor Commented:
Hi Patrick,

Just did as you explained, but I can't scroll down. I set all sroll bars to visible. I can see the vertical bar, but nothing moves which means I cannot see the other fields that are down down the userform.

Sid,
I'm downloading it to test.
0
 
SiddharthRoutCommented:
Don't use this file. Use the one that I gave in

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26847475.html#a34979617

I have updated it with the latest code.

Sid
0
 
currentdbAuthor Commented:
Sid. just ran into a compile error on this file as explained in the other question.
0
 
SiddharthRoutCommented:
Answered that.

Sid
0
 
patrickabCommented:
>I can see the vertical bar, but nothing moves which means I cannot see the other fields that are down down the userform.

I see what you mean. It's the same in Excel2002.

Patrick
0
 
currentdbAuthor Commented:
Sid,
Compile error gone. Just removed the line before you had to answer. At least I was a little bit quick lol
As per Patrickab post, I had to make the userform longer, so set the userform Height at 1008, but scrollbars (vertical) does not make it go down. I also set the scroll height at 2 (was o), but still nothing.

Patrick
Same in Excel2002, then it's some compile bug from Microsoft ?
0
 
SiddharthRoutCommented:
currentdb: Like I mentioned, You can use the Excel built in page setup in the code to print in the size that the printer allows :)

Sid
0
 
currentdbAuthor Commented:
Sid, yes, but I was meaning about the userform's height.

It was befire at 568.5 and I set it to 700 because I added more and more fields. Per your code, it does print in the size that the printer allows, however it will print in Landscape mode and I cannot see the other fields as it goes down down the list because the scroll bar does not want to scroll down. If I can scroll down, then I can reach the print, quit and save changes buttons.
0
 
SiddharthRoutCommented:
I wrote a small article few years ago. See if that helps :)

http://www.vbforums.com/showthread.php?t=511916

Sid
0
 
currentdbAuthor Commented:
Hi Sid, just added the code you provided in your article (a very good article by the way).

Th scrollbar appears as the userform is longer and longer. But once I made a print attempt, I noticed that it also print the Windows active bar (where you see every application that is opened). It was not there before. I suppose it's because of the scroll bar (not sure). You can take a look at the uploaded sample
Private Sub UserForm_Activate()
    With Me
        'This will create a vertical scrollbar
        .ScrollBars = fmScrollBarsVertical
        
        'Change the values of 2 as Permission your requirements
        .ScrollHeight = .InsideHeight * 2
        .ScrollWidth = .InsideWidth * 9
    End With
End Sub

Open in new window

Currentdb-v5a-modofied-print-2.xlsm
0
 
SiddharthRoutCommented:
>> it also print the Windows active bar (where you see every application that is opened). It was not there before.

I just tested it. I don't see the bar. It spans two pages though.

Sample Attached.

Sid
Book2.pdf
0
 
currentdbAuthor Commented:
Weird. I'm gonna run another test on a different printer and see if the results are the same as yours or..same as mine.
0
 
currentdbAuthor Commented:
I'm back, ran another thest on a different printer. Still prints the Window active bar even if I set a different printer. Moreover I tried to force the printer to print on Legal (8x14), but everything comes out on Letter (8x12). I even deactivated the code that controls scrollbars..same effect.
0
 
currentdbAuthor Commented:
I was able to print, but print to xps format and the Windows active bar does appear on it. How to print to pdf ? Is an Adobe printer required ? Cause I cannot post the xps file. EE denied access. Tried to zip it, but still denied :(
0
 
SiddharthRoutCommented:
Try this code. What happens?

Private Sub btnPrint_Click()
    DoEvents
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    DoEvents
    Workbooks.Add
    Application.Wait Now + TimeValue("00:00:01")
    ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
    DisplayAsIcon:=False
    ActiveSheet.Range("A1").Select
    With ActiveSheet.PageSetup
        .PaperSize = xlPaperLegal
        .Orientation = xlPortrait
    End With
    
    Dim shp As Shape

    For Each shp In ActiveSheet.Shapes
        shp.Width = 350
        shp.Height = 350
    Next

    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWorkbook.Close False
End Sub

Open in new window


Sid
0
 
currentdbAuthor Commented:
I added your code and what happened is the printer just printed on a 8x14 paper :)

Second, what was printed looks small, much smaller than it was printed before because there is a big empty space to the right margin (but the entire userform is still visible).

As I added your code, I tried to print to a XPS document and automatically it added an excel copy. Was able to save as xlsx so I can send you one...a proof that the Windows active bar is there as I mentioned earlier :)
sample-snapshot.xlsx
0
 
SiddharthRoutCommented:
>>>I added your code and what happened is the printer just printed on a 8x14 paper :)

One problem solved :)

>>>Second, what was printed looks small, much smaller than it was printed before because there is a big empty space to the right margin (but the entire userform is still visible).

Increase 350 to 400 in

shp.Width = 350

>>>Taskbar issue.

I am really surprised as I don't get that, If you right click on the taskbar and click on properties, can you post a screenshot of that?

Sid
0
 
currentdbAuthor Commented:
I increased to shp.Width = 400, still no change. Maybe I have to insert a higher value ?

Here's a snapshot of the taskbar properties. It's in French, but everything is same as it is in English. So you are not lost (I hope)
snapshot-task-properties.JPG
0
 
SiddharthRoutCommented:
>> I increased to shp.Width = 400, still no change. Maybe I have to insert a higher value ?

Yes try it.

In the meantime, let me see why you are getting the taskbar.

Sid
0
 
currentdbAuthor Commented:
No problem. Also try to use the xlsm file I attached. Probably you'll see the taskbar there.

Also I changed shp.Width = 400 still same thing. Changed to a higher value, 700, 800, 900, even 1000, but same no change. Maybe something else is blocking it ?
Currentdb-v5a-modofied-print-2.xlsm
0
 
SiddharthRoutCommented:
Sorry, you need to change the height as well.
 
   For Each shp In ActiveSheet.Shapes
        shp.Width = 400
        shp.Height = 400
    Next

Sid
0
 
currentdbAuthor Commented:
Ohhh ok, I had to set it at 403. Besides the printer almost ran out of paper as I tried every value :)
0
 
SiddharthRoutCommented:
That's why you should always try a pdf/xps printer before taking a print out in the paper :)

Sid
0
 
SiddharthRoutCommented:
BTW here is PDF Printer. It comes with the creator. Free...

http://www.primopdf.com/download.aspx

Sid
0
 
currentdbAuthor Commented:
Thanks for the link to PDF printer. It will help better than xps cause on xps it's hard to see if it prints on 2 pages. Just tried it a couple times and it charges everything in memory resulting in less space to use xps again and again.

BTW, did you find a solution to the taskbar problem ? Maybe just hide it and make it appear once you hover the mouse down the screen. Just tried it and the printer does not print the taskbar anymore.  However, are you sure there is nothing to do to reduce the right margin ? If you look on pdf printer, you wil notice this white space on the right.
0
 
SiddharthRoutCommented:
Try this and tell me what happens?

Private Sub btnPrint_Click()
    DoEvents
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    DoEvents
    Workbooks.Add
    Application.Wait Now + TimeValue("00:00:01")
    ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
    DisplayAsIcon:=False
    ActiveSheet.Range("A1").Select
    With ActiveSheet.PageSetup
        .PaperSize = xlPaperLegal
        .Orientation = xlPortrait
    End With
    
    Dim shp As Shape
    Dim shpLeft As Single, shpTop As Single

    For Each shp In ActiveSheet.Shapes
        With shp
            .Width = 400
            .Height = 400
            shpLeft = .Left
            shpTop = .Top
            With .PictureFormat
            .CropLeft = 10
            .CropTop = 10
            .CropBottom = 10
            .CropRight = 10
            End With
            .Left = shpLeft
            .Top = shpTop
        End With
    Next
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWorkbook.Close False
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
You might want to comment

.CropLeft = 10
.CropTop = 10
.CropRight = 10

and just use

.CropBottom = 10

for the 1st test.

Sid
0
 
currentdbAuthor Commented:
Ok, I'm going to test it now. The ''crop'' word makes me think about vegetables...you need to ''crop'' them, then ''harvest'' :)
0
 
currentdbAuthor Commented:
I'm back. This line:
.CropBottom = 10 will ''eat'' half of the taskbar. In other words it will hide it partially.
I changed the crop to 15 and now the taskbar has been eaten by the crop :)

0
 
SiddharthRoutCommented:
Great :)

So that sorts the problem?

Sid
0
 
currentdbAuthor Commented:
Yes the taskbar is gone. I don't know if you had time to read my posts earlier. If we activate the ''Automatically hide Windows taskbar'', in the taskbar properties, it will not appear at all on the print paper. So both solutions are good.

As for the 3 crops that wee commented out, do you think the .CropRight = 10 will reduce the white space to the right ?
0
 
SiddharthRoutCommented:
Yes it will.

Sid
0
 
currentdbAuthor Commented:
Sid, the .CropRight = 10 line will just hide the scrollbar (a very good idea), but the white space is still there. I don't mean the white space on the useform, just the space next to the userform when it's printed out.
0
 
SiddharthRoutCommented:
That is the margins...

You might want to increase it?

Sid
0
 
currentdbAuthor Commented:
The margins, yes. Not increase, but decrease. I think decreasing margins will cover the white space on the paper.
0
 
SiddharthRoutCommented:
I feel it will increase the white space...
or do one thing increase the width of the shp and try.

Sid
0
 
currentdbAuthor Commented:
Sid, you mentioned earlier that changing the width values means also change the height values as well. I increased the width of the shp to 440, and the printout paper is still the same. Notice here that I left the height at 403, just changed the width from 403 to 440.
For Each shp In ActiveSheet.Shapes
        With shp
            .Width = 440
            .Height = 403
            shpLeft = .Left
            shpTop = .Top
            With .PictureFormat
            '.CropLeft = 10
            .CropTop = 5
            .CropBottom = 15
            .CropRight = 5
            End With
            .Left = shpLeft
            .Top = shpTop
        End With
    Next

Open in new window

0
 
SiddharthRoutConnect With a Mentor Commented:
Add this line

.LockAspectRatio = msoFalse

above

.Width = 440

Sid
0
 
currentdbAuthor Commented:
It does not have any effect. The only effect it had is that the printer began again to cough 2 pages instead of one lol

I had to set the crop right to 10 instead of 5, but not much effect and the margin to the right is still the same.
With shp
            .LockAspectRatio = msoFalse
            .Width = 440
            .Height = 403
            shpLeft = .Left
            shpTop = .Top
            With .PictureFormat
            '.CropLeft = 10
            .CropTop = 5
            .CropBottom = 15
            .CropRight = 10
            End With

Open in new window

0
 
SiddharthRoutConnect With a Mentor Commented:
If you just use this

    For Each shp In ActiveSheet.Shapes
        With shp
            .LockAspectRatio = msoFalse
            .Width = 440
            .Height = 403
            shpLeft = .Left
            shpTop = .Top
            With .PictureFormat
                .CropBottom = 15
            End With
            .Left = shpLeft
            .Top = shpTop
        End With
    Next

Open in new window


What happens?

See it is totally based on your printer. You will have to experiment with the values :)

Sid
0
 
currentdbAuthor Commented:
Nothing else happens and it's all the same. I had to play with these values, reduced again paper numbers from 2 to 1 by changing width value from 440 to 420...but the white space is still there.

Increasing the width will only make the printer cough 2 pages instead of one. Changing margins will ensure once for all that this white space is reduced.

You know, when the print button has only this code:
Private Sub btnPrint_Click()
  Me.PrintForm
End Sub

The userform covered the entire page and the white space to the right was relatively small.

For Each shp In ActiveSheet.Shapes
        With shp
            .LockAspectRatio = msoFalse
            .Width = 420
            .Height = 403
            shpLeft = .Left
            shpTop = .Top
            With .PictureFormat
                '.CropLeft = 10
                '.CropTop = 5
                .CropBottom = 15
                .CropRight = 13
            End With
            .Left = shpLeft
            .Top = shpTop
        End With

Open in new window

0
 
SiddharthRoutConnect With a Mentor Commented:
Try introducing this piece of code and then chnage the margins accordingly?

    With ActiveSheet.PageSetup
        .PaperSize = xlPaperLegal
        .Orientation = xlPortrait
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0.92)
        .BottomMargin = Application.InchesToPoints(0)
    End With

Open in new window


Sid
0
 
currentdbAuthor Commented:
I left the computer some 30 min ago. Will try it again from home. This time I copied the worksheet on my usb drive. Also I have to use the free PDF Printer to prevent printing too much. Maybe I will print 2 times instead of 60 times :)
0
 
SiddharthRoutCommented:
I thought you were now using pdf printer ;)

Sid
0
 
currentdbAuthor Commented:
Noooo can't use it on a protected computer. That's why :(
0
 
currentdbAuthor Commented:
Sid, I'm back and use Excel 2003 instead of 2007. I also installed the free pdf printer and i'm sure it will save me tons of papers. I don't have to cut more trees :)

Ok so I integrated your code. What it looks is like this here (see example)

Left margin is at 0 which is ok there.
But right margin, I don't understand why it did not move even if it's already at 0.

What we do now ?
Book2.pdf
0
 
SiddharthRoutCommented:
Can you paste the exact code that you are using?

Sid
0
 
currentdbAuthor Commented:
Sure, here it is:

With ActiveSheet.PageSetup
        '.PaperSize = xlPaperLegal
        .PaperSize = xlPaperLetter
        .Orientation = xlPortrait
        '.Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0.92)
        .BottomMargin = Application.InchesToPoints(0)
    End With

Open in new window

0
 
SiddharthRoutCommented:
That is just the page setup. Can you paste the code for Btn Click?

Sid
0
 
currentdbAuthor Commented:
the code pasted came from btnPrint

if you do refer to this button, you want the entire code that controls it ?
0
 
SiddharthRoutCommented:
Yeah the entire code for btnPrint_Click()

Sid
0
 
currentdbAuthor Commented:
ok, here we are:

Private Sub btnPrint_Click()
       
    DoEvents
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + _
    KEYEVENTF_KEYUP, 0
    DoEvents
    Workbooks.Add
    Application.Wait Now + TimeValue("00:00:01")
    ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
    DisplayAsIcon:=False
    ActiveSheet.Range("A1").Select
    With ActiveSheet.PageSetup
        '.PaperSize = xlPaperLegal
        .PaperSize = xlPaperLetter
        .Orientation = xlPortrait
        '.Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.92)
        .BottomMargin = Application.InchesToPoints(0)
    End With
    
    Dim shp As Shape
    Dim shpLeft As Single, shpTop As Single

    For Each shp In ActiveSheet.Shapes
        With shp
            .LockAspectRatio = msoFalse
            .Width = 420
            .Height = 403
            shpLeft = .Left
            shpTop = .Top
            With .PictureFormat
                '.CropLeft = 10
                '.CropTop = 5
                .CropBottom = 15
                .CropRight = 13
            End With
            .Left = shpLeft
            .Top = shpTop
        End With
    Next
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWorkbook.Close False
End Sub

Open in new window

0
 
currentdbAuthor Commented:
Hello Sid, I was wondering if you are still around and if you had time to look at the code posted at ID:34985018.
Thanks!
0
 
SiddharthRoutCommented:
Ok what happens when you use this

.RightMargin = Application.InchesToPoints(1)

instead of


.RightMargin = Application.InchesToPoints(0)

Sid
0
 
currentdbAuthor Commented:
Hi Sid, I was sleeping when you answered :o)

I just tested what you told me to and nothing happens at all if RightMargin = Application.InchesToPoints(1).

Do negative margins work ?

0
 
currentdbAuthor Commented:
Sid,
I tried to play a lot with the values, especially those for the height (.Height = 403) because I added more and more fields. Problem is once I scroll down then click on the print button, it will only print half of everything, not the entire userform from the beginning. I don't know how else to make it work.

Hope you can help as I also attached a sample.
Thanks!
Currentdb-v5a-modified-print-3.xlsm
0
 
SiddharthRoutCommented:
>> Problem is once I scroll down then click on the print button, it will only print half of everything, not the entire userform from the beginning. I don't know how else to make it work.

If you use smaller fonts and rearrange the form then won't that help?

Sid
0
 
currentdbAuthor Commented:
Hi Sid,

Actually fonts are set to 10. Make them smaller to 8 will only make some place, but still it will print half of the userform.
If my understanding is correct, does that means that all fields need to cover the screen and other fields that are not inside the ''screen range'' will be printed just as that, half ?
0
 
SiddharthRoutCommented:
Yes.

Sid
0
 
currentdbAuthor Commented:
I forgot to mention one more thing. Changing fonts from 10 to 9 or even 8 and when you send everything to printer, everything will be really small.

Actually when everything is printed out, the font do actually looks smaller on paper even if on screen it's set to 10.
0
 
SiddharthRoutCommented:
How about placing the controls closer?

Sid
0
 
currentdbAuthor Commented:
There is not something else we can change to make sure that the userform is ''captured'' correctly even if some fields are out of screen range ?
0
 
SiddharthRoutCommented:
Not that I know off. Can you put all the relevant controls and post the workbook back? Let me arrange it for you. But use the actual controls and don't just copy paste it :)

Sid
0
 
currentdbAuthor Commented:
Sure. Gimme some mins to re-send it to you with all controls on it. Will use actual controls, so no worries about copy/paste :)
0
 
currentdbAuthor Commented:
Here's the workbook with all relevant controls.
userform-sample-v7.xlsm
0
 
SiddharthRoutCommented:
Not sure if you will like the new look...

Here is the file.

Sid
userform-sample-v7.xlsm
0
 
currentdbAuthor Commented:
Just reviewed it 2 mins ago. Excellent idea to put relevant form controls (quit, save and print) at the top. It does not look ugly at all :)
It's just better than nothing and I will live with this change  :)


0
 
currentdbAuthor Commented:
Sid, bug thank you for your patience through this. I would be lost without your help. Thanks again! :)
0
 
currentdbAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.