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
Single click can be detected by Private Sub Worksheet_SelectionChange(ByVal Target As Range) but that only works if the user does not click in the same cell twice. Control click, never tried that. Best advice is to google your question.
Did you define the excel object with the withevents keyword? My guess you missed that.
The Withevents key word was not used. Other events were working, (such as BeforeDocumentClose). I added the withevents key word. No improvement and the previous events still work.
Any other ideas?
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??????
Before not befor
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