UserForm Printing

[PATRON58]

Hello all,

Am quite new to VBA and am having a slight problem. Need to print the contents of a Frame which contains a whole heap of objects. Is there a way of doing this?

I would normally have just done the PrintForm method but I do not need the whole form printed. In actual fact Im trying to print 2 seperate Frames with objects to overcome the problem of a long UserForm not printing out on one page and not having the ability to tell the program to continue on another sheet.

Any suggestions would be very much appreciated.

Thank you, PATRON

[Norie]

Userforms aren't really intended for printing.

Can't you transfer the data to a spreadsheet and print that?

That would give you much more control over what's printed and how it's formatted.

[PATRON58]

Hi there, thanks for getting back,

Good idea but the program is for use by a few other people and in all honesty it would be preferable to keep them out of the spreadsheet. The idea is to create an application for them which is straight forward to use as none of the people this is intended for has knowledge of excel. Technophobes.

Alternatively the only thing I can think of is to see if there is a way of printing parts of a userform between certain heights.

PATRON

[Norie]

Who said anything about letting the user access the worksheet?

The whole thing could be done in code.

[PATRON58]

Hi there,

Thats a very good point indeed! Maybe I'll have a little re-think instead in heindsight.


Thank you!

PATRON

[Leith Ross]

Hello Patron58,

I realize that this is a late response to your post, but I think you may find it useful. The macro copies the UserForm using Print Screen and pastes it to a new Worksheet as a bitmap. The page is printed, and the new Worksheet is deleted. Attach the macro to a button on the UserForm, like "Print Form".

'Written: July 25, 2007
'Author: Leith Ross
' SendKeys can't be used to call the Print Screen function.
' This macro emulates the user key strokes using keyboard events.
' This can only be done using the API.
       
' Keyboard key constants
  Private Const KEYEVENTF_KEYUP = &H2
  Private Const KEYEVENTF_EXTENDEDKEY = &H1
  Private Const VK_SNAPSHOT = &H2C
  Private Const VK_MENU = &H12
  Private Const VK_LMENU = &HA4

' Keyboard Event API call
  Private Declare Sub keybd_event _
    Lib "user32.dll" _
     (ByVal bVk As Byte, _
      ByVal bScan As Byte, _
      ByVal dwFlags As Long, _
      ByVal dwExtraInfo As Long)
       
Public Sub PrintUserForm()
  
  ' This macro must be called directly from the UserForm to work
  ' either by attaching it to a "Print" button or includng it in
  ' an event procedure.
  
    Dim WksTemp As Worksheet
      
      ' Simulate pressing ALT+Printscreen to copy the form window (=picture) to
      ' the clipboard
        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

      ' Add a worksheet named Temp, but don't show it
        Application.ScreenUpdating = False
          Set WksTemp = ThisWorkbook.Worksheets.Add
        
      ' Print the UserForm after pasting it onto the worksheet
        With ActiveSheet
          .Name = "Temp"
          .Paste
          .PrintOut
        End With

      ' Delete the worksheet Temp and suppress the not-saved Warning
        Application.DisplayAlerts = False
          ThisWorkbook.Worksheets("Temp").Delete
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
End Sub

Sincerely,
Leith Ross