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


--
-John
Please rate when your question is answered to help us and others know what
is helpful.