Hi,

I am using vba to display the built in Print Dialog box, but i would like to
somehow store the number of copies that one chooses into a variable. So if
one chooses 10 as the number of copies to print, i want to be able to store
10 somewhere in my code. Can anyone help me with this?

Thanks

Hi,

the number of copies put in the Print dialog box doesn't seem to be
stored. (the number in the printer properties ("options" in the Page
Setup dialog) seems to be stored in each sheet)

I wrote some code for excel 2000. but I don't know a good way.
creating a custom userform to print may be more certain.


Public Const WH_CBT = 5
Public Const HCBT_DESTROYWND = 4
Public Const GWL_HINSTANCE = -6

Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" ( _
ByVal idHook As Long, ByVal lpfn As Long, _
ByVal hMod As Long, ByVal dwThreadId As Long) As Long
Declare Function UnhookWindowsHookEx Lib "user32" ( _
ByVal hHook As Long) As Long
Declare Function CallNextHookEx Lib "user32" ( _
hHook As Long, nCode As Long, _
wParam As Long, lParam As Long) As Long
Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, ByVal lpszWindow As String) As Long
Declare Function GetCurrentThreadId Lib "kernel32" () As Long
Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" ( _
ByVal hwd As Long, ByVal nIndex As Long) As Long
Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" ( _
ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private g_hHookProc As Long
Private g_nCopies As Long

Public Function CBTHookProc(ByVal nCode As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
Dim hwnd As Long, Ret As Long
Dim buf As String

On Error Resume Next

If nCode = HCBT_DESTROYWND Then
buf = String(260, Chr(0))
Ret = GetWindowText(wParam, buf, Len(buf))
buf = Left(buf, InStr(1, buf, Chr(0), 0) - 1)
If buf = "Print" Then
hwnd = FindWindowEx(wParam, 0, "EDTBX", vbNullString)
hwnd = FindWindowEx(wParam, hwnd, "EDTBX", vbNullString)
hwnd = FindWindowEx(wParam, hwnd, "EDTBX", vbNullString)
If hwnd < 0 Then
buf = String(260, Chr(0))
Ret = GetWindowText(hwnd, buf, Len(buf))
buf = Left(buf, InStr(1, buf, Chr(0), 0) - 1)
If buf = "" Then
g_nCopies = 0
Else
g_nCopies = CLng(buf)
End If
End If
End If
End If
CBTHookProc = CallNextHookEx(g_hHookProc, nCode, wParam, lParam)
End Function


Sub Test_PrintDialog()
Dim hwnd As Long, hInstance As Long, ThreadId As Long
Dim Ret As Long
hwnd = FindWindowEx(0, 0, "XLMAIN", Application.Caption)
hInstance = GetWindowLong(hwnd, GWL_HINSTANCE)
ThreadId = GetCurrentThreadId()
g_hHookProc = SetWindowsHookEx(WH_CBT, AddressOf CBTHookProc, _
hInstance, ThreadId)
If g_hHookProc < 0 Then
g_nCopies = -1
Ret = 0
On Error Resume Next
Ret = Application.Dialogs(xlDialogPrint).Show()
On Error GoTo 0
UnhookWindowsHookEx g_hHookProc
MsgBox g_nCopies
End If
End Sub

--
HTH,

okaizawa


LHaro wrote:
Hi,

I am using vba to display the built in Print Dialog box, but i would like to
somehow store the number of copies that one chooses into a variable. So if
one chooses 10 as the number of copies to print, i want to be able to store
10 somewhere in my code. Can anyone help me with this?

Thanks