It seems like this should be easy to find but I have not found how to detect when an Excel cell has been clicked with events.
I appreciate any ideas on this.
Thank you.
It seems like this should be easy to find but I have not found how to detect when an Excel cell has been clicked with events.
I appreciate any ideas on this.
Thank you.
What I have so far is shown below, but doesn't seem to work is. Any ideas what is wrong?
The event is defined as:
Dim EventDel_BeforDoubleClick As Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler
The AddHandler is:
EventDel_BeforDoubleClick = New Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(AddressOf CellsChange)
AddHandler oSheet.Change, EventDel_BeforDoubleClick
The Sub is:
Private Sub BeforDoubleClick(ByVal Target As Microsoft.Office.Interop.Excel.Range) 'ByVal target As Range, ByVal cancel As Boolean)
Debug.Print("Cells " + Target.Address + " Selected")
End Sub
I would define an excel object with the withevents keywords.
The handling function must be public(? Not sure I think I had read it somewhere in msdn) it has to have its signature ending with handles execelobj.event
At the top of the Form is the events are defined:
Dim EventDel_CellsChange As Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler
Dim EventDel_BeforDoubleClick As Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler
I have added Withevents which was not needed for the CellChange and had no improvement for the BeforeDoubleClick. I'm not sure tha this is needed.:
Dim WithEvents oExcel As Microsoft.Office.Interop.Excel.Application
When Excel is loaded the following handlers are added:
EventDel_CellsChange = New Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(AddressOf CellsChange)
AddHandler oSheet.Change, EventDel_CellsChange
EventDel_BeforDoubleClick = New Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(AddressOf CellsChange)
AddHandler oSheet.Change, EventDel_BeforDoubleClick
The two events call the following subs:
Private Sub CellsChange(ByVal Target As Microsoft.Office.Interop.Excel.Range)
'This is called when a cell or cells on a worksheet are changed.
Debug.Print("Cells " + Target.Address + " on " + Target.Worksheet.Name() + " Changed")
End Sub
Private Sub BeforDoubleClick(ByVal Target As Microsoft.Office.Interop.Excel.Range) 'ByVal target As Range, ByVal cancel As Boolean)
'Debug.Print("double click detected")
Debug.Print("Cells " + Target.Address + " Double Clicked")
End Sub
The CellChange command works but the BeforDoubleClick does not work??????
I have fixed "befor" error and have tried changing the Withevents to Public (no difference). The code is a little cleaned up but there is still an error that I don't know how to deal with:
At the top of the Form is the events are defined:
Dim WithEvents oExcel As Microsoft.Office.Interop.Excel.Application
Dim ExcelCellsChange As Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler
Dim ExcelBeforDoubleClick As Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler
When Excel is loaded the following handlers are added:
ExcelCellsChange = New Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(AddressOf CellsChange)
AddHandler oSheet.Change, ExcelCellsChange
ExcelBeforDoubleClick = New Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(AddressOf ExcelDoubleClick)
{Incomparable signature in line above???}
AddHandler oSheet.Change, ExcelBeforDoubleClick
The two events call the following subs:
Private Sub CellsChange(ByVal Target As Microsoft.Office.Interop.Excel.Range)
Debug.Print("Cells " + Target.Address + " on " + Target.Worksheet.Name() + " Changed")
End Sub
Private Sub ExcelDoubleClick(ByVal Target As Microsoft.Office.Interop.Excel.Range)
Debug.Print("Cells " + Target.Address + " Double Clicked")
End Sub
I get this error????????
I want to thank everyone who helped with this problem. I wasn't able to get Control Click to work but below is what was needed for Double Click. The different colors indicate where the names need to be the same. (Hint: note he ByRef in the Sub Declaration).
In the basic declarations:
Dim ExcelBeforeDoubleClick As Microsoft.Office.Interop.Excel.DocEvents_BeforeDoubleClickEventHandler
In the load form:
ExcelBeforeDoubleClick = New Microsoft.Office.Interop.Excel.DocEvents_BeforeDoubleClickEventHandler_
AddressOf DoubleClickCell)
AddHandler oSheet.BeforeDoubleClick, ExcelBeforeDoubleClick
In the Sub:
Private Sub DoubleClickCell(ByVal Target As Microsoft.Office.Interop.Excel.Range, ByRef cancel As Boolean)
'Debug.Print("Cells " + Target.Address + " Double Clicked")
End Sub
What you're looking is the event for mouse click.
Put this sub in the worksheet you want it to run on.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "range " & Target.Address & " was double clicked"
End Sub