Capture mouse click in excel 2

[kb9prt]

A rather silly question but sometimes my little mind gets wrapped around something and will not let go.

I am trying to capture a mouse click in an excel spreadsheet. Worksheet_SelectionChange() works fine except if the user clicks on the same cell when the cell does not change.

I have a userform that pops up to facilitate data entry whenever a cell from column c is entered: works great except when the user has closed the userform and clicks in the same cell that the curser was already in (no selection change-just click)- forcing the user to click out of column c and back in.

Any help would be appreciated

 

[combo]

Not posible without API.
Have you considered the BeforeDoubleClick event (with Cancel=True and showing the userform)? It's more natural to expect a userform after non-standard action, alternatively you could consider modeless userform that interact with SelectionChange to adapt to current selection.

combo

 

[SkipVought]

I have used a technique like this:

When the user SELECTS a cell (one cell only) in the target range, my logic in the Worksheet_SelectionChange event, selects A1 before exiting. Then the user can select in the same cell again.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kb9prt]

thank you for the suggestions! and sorry I did not respond sooner - I was off line for a bit.
I ended up positioning the focus one cell to the right on the dialog box close. It is not perfect but forces the selectionChange event if they should need to enter the data again. This is kind of the same idea as Skip, if I understand correctly.

Combo - I like the idea as well and did not think of the standard VS non-standard action and the user expectation. something I will have to put some thought into.

thanks again

 

[N1GHTEYES]

I've come across this issue a few times too. I've usually used one of the solutions proposed by combo or Skip. But combo's remark got me thinking... I'm assuming the not "without the API" comment implies some solution involving subclassing the workbook. I've done that kind of thing in the past in VB (though not VBA), but it can often seem to get messy - and can be a pain to debug too.

I've no urgent need to do this right now, but it seems like it might be a useful weapon in the armoury. So does anyone know if anyone has written a neat encapsulation of this kind of behaviour and coded it up as a useful class one can simply drop into a project? If so, where might one find a copy?

I'm thinking of some kind of Excel Code Assistant object which provides this and related kinds of functionality which is missing from plain ol' Excel.

Tony