Application level event help
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.
|