Had some time to think and research this, to print preview the form you must use a workaround.
In the following example I have a form called "Userform1", on the form are 2 objects, 1) a check box called "CheckBox1" and 2) a button called "CommandButton1".
I also have a module called "module1".
In module 1 I have the following code:
Sub mdl_ShowForm()
UserForm1.Show (vbmodless)
End Sub
in the code behind the form I have the following:
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal _
bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Const KEYEVENTF_KEYUP = &H2
Private Const VK_SNAPSHOT = &H2C
Private Const VK_MENU = &H12
Private Sub CheckBox1_Click()
keybd_event VK_MENU, 0, 0, 0
keybd_event VK_SNAPSHOT, 0, 0, 0
keybd_event VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0
keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0
End Sub
Private Sub CommandButton1_Click()
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
ActiveWindow.SelectedSheets.PrintPreview
Selection.Delete
End Sub
Now one thing to stress is, DO NOT RUN A MODAL FORM AND THE PRINT PREVIEW AT THE SAME TIME, EXCEL WILL HANG UP.
The code must always be started by executing the mdl_ShowForm code first. This will define the form as modeless and hence allow print preview without hanging.
What is the process doing?
Well when the checkbox is selected, the process is doing the equivilent of the [Alt]+[Print Scrn] buttons on the keyboard, i.e. copy to clipboard only the active windows. When the user clicks the button on the form it pastes the contents of the clipboard into excel and print previews the sheet. When the user clicks close in the print preview, the image of the form is deleted.
hope this is helpful.
Cheers
Matt
|
