none
Handling Click Event in Excel RRS feed

  • Question

  • Hi,

    I want to handle a Click event in an Excel cell. But, I cannot make the Cell as hyperlink. 

    Excel VBA do not have a handler for Click Event. How can i do so?

    Thanks,

    Ashok

    Tuesday, May 1, 2012 4:15 PM

All replies

  • It's not clear what you are really trying to do.

    Cells can contain hyperlinks, select a cell and do Ctrl-k

    Various Worksheet level Events are exposed, including selection of one or more cells, change of cell value, hyperlink click (if say you want to intercept a hyperlink click), and some other things. Right-click a sheet module and click "View code" to open the VBE to the worksheet module. Look a Events in the top right drop down.

    Peter Thornton

    Wednesday, May 2, 2012 1:34 PM
    Moderator
  • Hi Peter, 

    Thanks for your reply. 

    Like i mentioned in my first post, I checked for the handlers you are saying and found none for handler event.

    Also, I cannot handle this Click/DoubleClick event from the sheet. I want to handle these events outside the sheet.

    The actual scenario is I will be looking for a text in an excel. If there is a match, I have to perform a Click/Double Click on that cell. Upon this action a Message box will pop up. I wanna capture the info in the popup window.

    I don't find any way to perform a Click/Double click on an Excel in the first place.

    Thanks,

    Ashok

    Tuesday, May 15, 2012 7:27 AM
  • Excel exposes several worksheet level Events which you can trap.

    The SelectionChange event is almost equivalent to a cell click event except it will only fire when a new cell range is selected. There are also right click & hyperlink events.

    As I explained before right click a sheet tab and click View-code to open the VBE and the appropriate sheet module.
    In the top middle dropdown select "Worksheet".
    In the top right dropdown select the event(s) you wish to trap to write the Event stub in the module.

    The Events should be easy to find but if you can't explain exactly what you are doing to try and find them.

    Peter Thornton

    Tuesday, May 15, 2012 2:58 PM
    Moderator
  • Also, I cannot handle this Click/DoubleClick event from the sheet. I want to handle these events outside the sheet.

    Am I interpreting the above comment correctly in assuming that you do not know in what worksheet the DoubleClick event is required. If so, in lieu of placing the event code in the worksheet module, you can place the code in ThisWorkbook module as per the code example below so that the event will work from any worksheet in the workbook. The Worksheet where the Double Click took place is passed to the sub via Sh variable and the cell that was Double clicked is passed via Target variable.

    Note that Double Clicking a cell normally places the cell in Edit mode. The Cancel = True actually cancels the Edit mode and then the remainder of the code is processed without being in Edit mode in the cell.

    To open ThisWorkbook module:

    Alt/F11 to open VBA editor (Alt/F11 also toggles back to the worksheets.)

    Double click ThisWorkbook in the Project explorer in the left column.

    Copy the code below and paste into ThisWorkbook module and then edit the code to perform the required task.

    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        Cancel = True
        MsgBox "Worksheet.name is " & Sh.Name & vbCrLf & _
                "Cell address is " & Target.Address(0, 0)
    End Sub


    Regards, OssieMac

    Wednesday, May 16, 2012 1:07 AM