J
John Bundy
Hi all, just recently got into application level event programming and need a
little help. First, using Chip's site I worked out the following code that
throws an extra validation if printing more than x sheets, works great. Now i
am trying to capture sheet change events and get an 'Object Required' error.
Working code:
This workbook-
Private XLApp As clsExcelEvents
Private Sub Workbook_Open()
Set XLApp = New clsExcelEvents
End Sub
clsExcelEvents class module-
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub App_WorkbookBeforePrint(ByVal wb As Workbook, Cancel As Boolean)
Dim strPrint As String
Dim pageCount As Integer
pageCount = 10
If Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") >= pageCount Then
strPrint = MsgBox("Are you sure you want to print this? It is " _
& Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") & " Sheets", vbYesNo)
If strPrint = vbNo Then
Cancel = True
MsgBox "Print job hs been cancelled", vbExclamation, "Cancel"
End If
End If
End Sub
Now I have tried several ways but they all fail, here is the latest, keep
workbook_open the same:
clsExcelEvents class module-
Private WithEvents xlWs As Worksheet
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
Set xlWs = Worksheet
End Sub
Private Sub xlWs_Activate()
Call test
End Sub
standard module-
Declare Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Integer
Const VK_CONTROL As Integer = &H11 'Ctrl
Sub test()
If GetKeyState(VK_CONTROL) < 0 Then Ctrl = True Else Ctrl = False
If Ctrl = True Then
MsgBox "pressed"
Else
MsgBox "Not"
End If
End Sub
TIA
little help. First, using Chip's site I worked out the following code that
throws an extra validation if printing more than x sheets, works great. Now i
am trying to capture sheet change events and get an 'Object Required' error.
Working code:
This workbook-
Private XLApp As clsExcelEvents
Private Sub Workbook_Open()
Set XLApp = New clsExcelEvents
End Sub
clsExcelEvents class module-
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub App_WorkbookBeforePrint(ByVal wb As Workbook, Cancel As Boolean)
Dim strPrint As String
Dim pageCount As Integer
pageCount = 10
If Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") >= pageCount Then
strPrint = MsgBox("Are you sure you want to print this? It is " _
& Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") & " Sheets", vbYesNo)
If strPrint = vbNo Then
Cancel = True
MsgBox "Print job hs been cancelled", vbExclamation, "Cancel"
End If
End If
End Sub
Now I have tried several ways but they all fail, here is the latest, keep
workbook_open the same:
clsExcelEvents class module-
Private WithEvents xlWs As Worksheet
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
Set xlWs = Worksheet
End Sub
Private Sub xlWs_Activate()
Call test
End Sub
standard module-
Declare Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Integer
Const VK_CONTROL As Integer = &H11 'Ctrl
Sub test()
If GetKeyState(VK_CONTROL) < 0 Then Ctrl = True Else Ctrl = False
If Ctrl = True Then
MsgBox "pressed"
Else
MsgBox "Not"
End If
End Sub
TIA