Print userform on 8x14 possible ?

Here is the updated workbook. Please change the setting using page setup before you print.

Sid
Book2.pdf
Currentdb-v5a.xlsm

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

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

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.

Don't use this file. Use the one that I gave in

https://www.experts-exchange.com/questions/26847475/Force-GoogleMaps-to-be-visible-on-the-userform.html?anchorAnswerId=34979617#a34979617

I have updated it with the latest code.

Sid

Sid. just ran into a compile error on this file as explained in the other question.

Answered that.

Sid

>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

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 ?

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

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.

I wrote a small article few years ago. See if that helps :)

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

Sid

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

Select allOpen in new window

Currentdb-v5a-modofied-print-2.xlsm

>> 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

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.

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.

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 :(

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

Select allOpen in new window

Sid

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

>>>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

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

>> 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

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

Sorry, you need to change the height as well.
 
   For Each shp In ActiveSheet.Shapes
        shp.Width = 400
        shp.Height = 400
    Next

Sid

Ohhh ok, I had to set it at 403. Besides the printer almost ran out of paper as I tried every value :)

That's why you should always try a pdf/xps printer before taking a print out in the paper :)

Sid

BTW here is PDF Printer. It comes with the creator. Free...

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

Sid

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.

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

Select allOpen in new window

Sid

You might want to comment

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

and just use

.CropBottom = 10

for the 1st test.

Sid

Ok, I'm going to test it now. The ''crop'' word makes me think about vegetables...you need to ''crop'' them, then ''harvest'' :)

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 :)

Great :)

So that sorts the problem?

Sid

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 ?

Yes it will.

Sid

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.

That is the margins...

You might want to increase it?

Sid

The margins, yes. Not increase, but decrease. I think decreasing margins will cover the white space on the paper.

I feel it will increase the white space...
or do one thing increase the width of the shp and try.

Sid

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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 :)

I thought you were now using pdf printer ;)

Sid

Noooo can't use it on a protected computer. That's why :(

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

Can you paste the exact code that you are using?

Sid

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

Select allOpen in new window

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

Sid

the code pasted came from btnPrint

if you do refer to this button, you want the entire code that controls it ?

Yeah the entire code for btnPrint_Click()

Sid

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

Select allOpen in new window

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!

Ok what happens when you use this

.RightMargin = Application.InchesToPoints(1)

instead of


.RightMargin = Application.InchesToPoints(0)

Sid

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 ?

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

>> 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

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 ?

Yes.

Sid

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.

How about placing the controls closer?

Sid

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 ?

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

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 :)

Here's the workbook with all relevant controls.
userform-sample-v7.xlsm

Not sure if you will like the new look...

Here is the file.

Sid
userform-sample-v7.xlsm

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  :)

Sid, bug thank you for your patience through this. I would be lost without your help. Thanks again! :)

Sid, I just opened a new question here:

https://www.experts-exchange.com/questions/26859154/Open-map-location-in-a-second-userform.html

thanks!