onclick event for cell

T

ties

I would like to add a OnClick event to a cell using VBA: A user clicks
certain cell and then for instance a form pops up. I'm not sure if it's
possible.

So far I have used Workbook_SheetSelectionChange, with which I can monitor
whether a cell is selected... This works pretty well, but it also reacts
when a user is scrolling the cursor with the arrow-keys over the cell and
offcourse doesn't react when the cell is already selected and the user
clicks the cell.

Does anyone know an easier solution than the one I found, which covers the
problems I encounter?

Thanks for your help
Thijs van Bon
 
J

Jan Karel Pieterse

Hi Ties,
So far I have used Workbook_SheetSelectionChange, with which I can monitor
whether a cell is selected... This works pretty well, but it also reacts
when a user is scrolling the cursor with the arrow-keys over the cell and
offcourse doesn't react when the cell is already selected and the user
clicks the cell.
Click to expand...

You could place a label control from the control toolbox on top of the cell
you wish the click event for and use the click event of the label. Make sure
the label has no caption and has the background and border set to none.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
A

Andy Wiggins

Put this code in a worksheet module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "Worksheet_BeforeDoubleClick"
End Sub

... or this code in the ThisWorkbook module.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
MsgBox "Workbook_SheetBeforeDoubleClick"
End Sub

Double clicking on any cell should achieve what you want.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
D

Damien McBain

This works (in the "before double click" even for the sheet):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Selection = Range("A1") Then
'selection is always what you are double clicking coz you
'always select it by clicking once before you double click it
MsgBox "it works"
Else
End If
End Sub

HTH,

Damo
 
Top