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

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.


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

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"



"ties" wrote in message
...
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

bedankt!

I'm sure this must work great!

Regards,
Thijs van Bon

"Jan Karel Pieterse" schreef in bericht
...
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.


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

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


ties wrote:
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