Duplex VBA

[redjay]

Hi all

I have a worksheet containing a Userform which allows colleagues to arrange meetings and send the details to me.

The user is required to complete a userform with the meeting details then fill out a list in an ordinary worksheet with details of attendees.

I have a basic command button which prints off both the worksheet and the userform, but they are printing off on seperate pages, despite the printer being set to duplex.

Is there something I can add to the below code to force it to print off duplex?

Private Sub CommandButton1_Click()
With UserForm2
   Sheets("Attendees").Activate
   Sheets("Attendees").PrintOut
   
    .PrintForm
    
    
End With
End Sub

Or perhaps a better way of doing this than I have thought of!?
Many thanks

 

[redjay]

Forgot to mention, the above code runs from a command button within the userform.

 

[redjay]

My apologies for bumping this but I need this urgently for work!

Ultimately I need to be able to code a command button which prints off both the userform and the worksheet onto duplex.

Failing this, is it possible to copy and paste an image of the userform into the worksheet so that it could then be manually printed from the worksheet?

Any help is massively appreciated.

 

[Case_Germany]

Hi,

there is a tricky solution by API.

Duplex

I think it is better you install the printer 2 times. Once with duplex and once without duplex.

Case_Germany

 

[Case_Germany]

Hi again,

Failing this, is it possible to copy and paste an image of the userform into the worksheet so that it could then be manually printed from the worksheet?

Try the following code (the code belongs into the UserForm):

Private Declare Sub keybd_event Lib "user32.dll" _
    (ByVal bVk As Byte, ByVal bScan As Byte, _
    ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Private Declare Function MapVirtualKey Lib "user32" _
    Alias "MapVirtualKeyA" (ByVal wCode As Long, _
    ByVal wMapType As Long) As Long

Const VK_MENU = &H12 'ALT

Private Sub CommandButton1_Click()
    keybd_event VK_MENU, MapVirtualKey(VK_MENU, 0), 0, 0 ' 0 = press ALT
    keybd_event vbKeySnapshot, 0, 0, 0
    DoEvents
    keybd_event VK_MENU, MapVirtualKey(VK_MENU, 0), 2, 0 ' 2 = release ALT
    ThisWorkbook.Worksheets.Add
    ActiveSheet.Paste
    Unload Me
End Sub

Case_Germany

 

[redjay]

Thanks Case_Germany, the copy and paste works brilliantly!

Not much joy with the Duplex thing though. I thought that by copying and pasting the form into a worksheet, it would allow me to print duplex anyway but it is still doing single side.

I am just using

Sheets("attendees").PrintOut
Sheets("Sheet1").PrintOut

.

Any ideas?

Thanks

 

[Case_Germany]

Hi again,

Any ideas?

Not really. There are probably only two ways. Either the printer twice install, or the printer address by API.

Do which always you want.

Case_Grmany