4

I'm aware that I can use Worksheet_SelectionChange but this doesn't do exactly what I want.

For example when I move the active cell with the arrow keys it will still run the code.

How do I only make it run the code when actually physically clicking on the cell?

Community
  • 1
  • 1
jvh
  • 143
  • 2
  • 13
  • 2
    There's right click and double click but no left click (that would be a nightmare XD) – findwindow Aug 11 '15 at 13:43
  • So there's no way to use a cell as a button? – jvh Aug 11 '15 at 13:44
  • I was using activeX buttons before but they get messed up all the time so I'm looking for other options – jvh Aug 11 '15 at 13:45
  • If we know what you're trying to do, we might be able to suggest other solutions. Or ask a question to fix your current buttons? – findwindow Aug 11 '15 at 13:49
  • @findwindow I'm using 2 kinds of buttons. 1 to hide/unhide some predefined rows. The other one just changing values of other cells with predefined values, but this one needs to work similar to a radio button. – jvh Aug 11 '15 at 13:50
  • There is a Private Sub Worksheet_Change(ByVal Target As Range) event but that will fire until after a change has been made. – MatthewD Aug 11 '15 at 13:50
  • @MatthewD What kind of change is required? Isn't clicking the cell enough change? (Activecell value changes?) – jvh Aug 11 '15 at 13:51
  • Actually, testing it seems that it does fire on cell selection. – MatthewD Aug 11 '15 at 13:54
  • The buttons don't sound that complex. I would ask a new question to fix whatever issue you have with them. – findwindow Aug 11 '15 at 13:58
  • @findwindow I have asked that question. The answer to that question basicly came down to "It's a bug in Excel, can't be fixed" – jvh Aug 11 '15 at 14:00
  • 2
    Check out my answer [here](https://stackoverflow.com/questions/23564019/changing-cell-value-with-mouse-buttons-excel/23565221#23565221) – EngJon Aug 11 '15 at 14:09
  • Here is the Windows stuff for keypress. You can record if an arrow key is pressed and the time. Then you could put a check at the begining of the selectionchanged and exit if the last key pressed was an arrow within a fraction of a second. If that is true you will exit. http://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell – MatthewD Aug 11 '15 at 14:18
  • Yes, there is a bug / problem with ActiveX buttons which seem to get messed up from time to time. But this problem vanishes if you use ordinary form buttons. I removed all ActiveX buttons years ago and changed to from buttons and all problems disappeared. – Ralph Aug 11 '15 at 14:27

2 Answers2

3

You can detect left clicks as well. I answered a similar question here

Insert the following code inside the specific worksheet module (for example "Sheet1"):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (GetAsyncKeyState(vbKeyLButton)) Then 'left mouse button
            'do something here
    End If
End Sub

additionally, you have to insert the following part on the top of a normal module (like the standard "Module1"):

Public Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

That's it. The part "do something here" can be filled by your needs.

However, this has some flaws: If you do something that finishes with a click (e.g. a MsgBox), the next selection change with arrow keys will also fire the event and re-execute your stuff. To bypass this, you can add an extra "empty"

If (GetAsyncKeyState(vbKeyLButton)) Then 'left mouse button 'blank End If

in the end of the selectionChange Sub. As I said, there are flaws, so this won't disable all unwanted behaviour by Excel. Another one is clicking somewhere else in Excel (e.g. choosing another ribbon) and changing the selection of cells per arrow keys afterwards. Haven't found a solution to that one unfortunately.

EngJon
  • 987
  • 8
  • 20
  • Tried this but couldn't get this to work. No errors, but nothing happening either – jvh Aug 12 '15 at 07:47
  • You have to make sure everything is in the right place. The worksheet_selectionchange has to be in the module of the sheet you are trying it on, not "this workbook". And the declare command has to be placed in a normal module (like module1 if you insert one). – EngJon Aug 12 '15 at 08:38
  • Double checked and triple checked. Everything is in the right place – jvh Aug 13 '15 at 07:26
  • If you skip the key check, does the selection change work as it should? – EngJon Aug 13 '15 at 07:40
  • @JentevanHeuverswyn And I assume you exchanged `do something here` with actual code? Just asking – EngJon Aug 13 '15 at 09:18
2

The left mouse click cannot be trapped with VBA for the Excel application. There are some methods to check globally if the left mouse button is pressed down but it does not seem simple and reliable from what I gather. See:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28343702.html which is unresolved (basically the same question you have)

SelectionChange cannot only work for the mouse: http://excel.tips.net/T003070_Mouse_Click_Event_in_VBA.html

After lots of searching nothing on the web is conculsive about this. This may not be the answer you're looking for but I don't think you'll find what you want.

David G
  • 2,315
  • 1
  • 24
  • 39
  • As for the lack of 'official' documentation... msdn is good for what IS implemented in VBA but doesn't state what cannot be done. – David G Aug 11 '15 at 13:57
  • Alright. thank you for the effort. I guess selectionChange is gonna have to do then. – jvh Aug 11 '15 at 13:58
  • @JentevanHeuverswyn well this isn't true. Check out my answer to a similar question: [here](https://stackoverflow.com/questions/23564019/changing-cell-value-with-mouse-buttons-excel/23565221#23565221) – EngJon Aug 11 '15 at 14:07
  • @EngJon NICE! That's a revelation. I get a compilation error when trying it out but if you can make it work for him I will delete as soon as he sees your comment. If you post your answer, I will delete mine. – David G Aug 11 '15 at 14:16
  • It seems like your initial solution was unresolved in the end from the comments underneath it. Was he eventually able to make it work as intended? I need to know this for personal use as well :D – David G Aug 11 '15 at 14:19
  • @DavidG What is your compilation error? It may be solved by [this](https://support.microsoft.com/de-de/kb/983043) – EngJon Aug 11 '15 at 14:20