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

xl.SheetSelection.Change = "Newx"

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

--
Regards,
Tom Ogilvy



"AA2e72E" wrote in message
...
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.Add
xl.SheetSelection.Change = "Newx"
xl.Cells(1, 4).Select
End Sub

Sub Newx()
' ... code
End Sub

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.

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" wrote in message
...
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.

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).Cod eModule
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" a écrit dans le message de
...
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.Add
xl.SheetSelection.Change = "Newx"
xl.Cells(1, 4).Select
End Sub

Sub Newx()
' ... code
End Sub