Automation Events

A

AA2e72E

In Visual Basic, how do I code this statement, xl.SheetSelectionChange = "Newx" to trap the selection change event

Private Sub Form_Load(
Set xl = CreateObject("Excel.Application"
xl.Workbooks.Ad
xl.SheetSelection.Change = "Newx
xl.Cells(1, 4).Selec
End Su

Sub Newx(
' ... cod
End Su
 
T

Tom Ogilvy

xl.SheetSelection.Change = "Newx"

is not very descriptive. What functionally are you trying to do?

--
Regards,
Tom Ogilvy



AA2e72E said:
In Visual Basic, how do I code this statement, xl.SheetSelectionChange =
Click to expand...
"Newx" to trap the selection change event ?
 
A

AA2e72E

Excel has several events; I use the one that I have referred to earlier, SheetSelectionChange.

I want to be able to say:

xl.SheetSelectionChange = "Newx" i.e set the SheetSelectionChange event to run the sub "Newx"; it should be trigerred when I execute xl.Cells(1,2).Select i.e change the selection.

Thsi is a trivial example: the principal should apply to more useful Excel events, such as BeforeClose etc.
 
C

Chip Pearson

You can't assign event handlers to macros. You have to use the
event handling code itself. Create a class module named
CExcelEvents with code like the following:

Public WithEvents XL As Excel.Application

Private Sub XL_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
' your event handler code here
End Sub

Then, in your start up code, instantiate that class and assign
your Excel application reference to the XL variable in the class.
E.g., in a standard code module,

Dim XLEvents As CExcelEvents
Sub StartExcelEvents()
Set XLEvents = New CExcelEvents
Set XLEvents.XL = your_XL_application
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





AA2e72E said:
Excel has several events; I use the one that I have referred to earlier, SheetSelectionChange.

I want to be able to say:

xl.SheetSelectionChange = "Newx" i.e set the
Click to expand...
SheetSelectionChange event to run the sub "Newx"; it should be
trigerred when I execute xl.Cells(1,2).Select i.e change the
selection.
Thsi is a trivial example: the principal should apply to more
Click to expand...
useful Excel events, such as BeforeClose etc.
 
M

Michel Pierron

Hi AA2e72E,
You can do:
Sub Form_Load()
Set xl = CreateObject("Excel.Application")
Set wbk = xl.Workbooks.Add
Set cmd = wbk.VBProject.VBComponents(wbk.Sheets(1).Name).CodeModule
With cmd
..InsertLines .CountOfLines + 1, "Private Sub Worksheet_Selection" _
& "Change(ByVal Target As Range)"
..InsertLines .CountOfLines + 1, "Call Newx"
..InsertLines .CountOfLines + 1, "End Sub"
..InsertLines .CountOfLines + 1, ""
..InsertLines .CountOfLines + 1, "Sub Newx"
..InsertLines .CountOfLines + 1, "MsgBox ""Hello !"", 64"
..InsertLines .CountOfLines + 1, "End Sub"
End With
xl.Visible = True
End Sub

Regards,
MP

AA2e72E said:
In Visual Basic, how do I code this statement, xl.SheetSelectionChange = "Newx"
Click to expand...
to trap the selection change event ?
 
Top