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.

If I understand your question correctly you are making it too difficult. Your
App is catching events so you just need to code against those events... Try
adding this to your class.

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox Sh.Parent.Name & vbCrLf & Sh.Name & vbCrLf & Target.Address
End Sub

you don't need the sheet variable
Private WithEvents xlWs As Worksheet
nor do you need the API...
--
HTH...

Jim Thomlinson


"John Bundy" wrote:

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.

You're the man, the reason for the API...i forgot to explain. I know about
ctl+pgup and pgdn but that just cycles through the sheets, i want to-
If caps lock is on, then some set of keys goes to stores current sheet and
goes to last and so on. Thanks for your help and i'll get it eventually!
Application level stuff is pretty slick, never even knew it existed.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Jim Thomlinson" wrote:

If I understand your question correctly you are making it too difficult. Your
App is catching events so you just need to code against those events... Try
adding this to your class.

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox Sh.Parent.Name & vbCrLf & Sh.Name & vbCrLf & Target.Address
End Sub

you don't need the sheet variable
Private WithEvents xlWs As Worksheet
nor do you need the API...
--
HTH...

Jim Thomlinson


"John Bundy" wrote:

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.