10 Replies Latest reply on Jul 20, 2016 12:40 PM by Edwin Thompson

    How to detect Excel Cell click, or double click, or ^click

    Edwin Thompson

      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.

        • Re: How to detect Excel Cell click, or double click, or ^click
          Amen Allah Jlili

          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

            • Re: How to detect Excel Cell click, or double click, or ^click
              Edwin Thompson

              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

            • Re: How to detect Excel Cell click, or double click, or ^click
              David Brukardt

              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.

              • Re: How to detect Excel Cell click, or double click, or ^click
                Amen Allah Jlili

                Did you define the excel object with the withevents keyword? My guess you missed  that.

                • Re: How to detect Excel Cell click, or double click, or ^click
                  Amen Allah Jlili

                  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

                    • Re: How to detect Excel Cell click, or double click, or ^click
                      Edwin Thompson

                      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??????

                      • Re: How to detect Excel Cell click, or double click, or ^click
                        Edwin Thompson

                        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????????

                         

                      • Re: How to detect Excel Cell click, or double click, or ^click
                        Edwin Thompson

                        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