Forum Discussion
Solved
In VBA how can I prevent change of focus when a cell is selected?
In my VBA application, the user may click on certain chart elements (e.g., a series or a point in a series). This triggers an EventChart_Select event. In the event handler, I select a cell on the sa...
- If i understand what you are doing, the reason Application.EnableEvents = False doesn't work is because that macro ends and hence EnableEvents gets turned back on and Excel will scroll. That said you can try adding ActiveWindow.ScrollRow = Range("cell where you want the window to be") before you turn EnableEvents back to True
I agree with mtarler : it is rarely a good idea to select cells or ranges in a macro. You can refer to the value of a cell, or manipulate that cell, without selecting it.
But if you really must: use
Application.EnableEvents = False
before, and
Application.EnableEvents = Trueafter selecting something.
I just tried that. Unfortunately, it does not prevent the change of focus from occurring.
- If i understand what you are doing, the reason Application.EnableEvents = False doesn't work is because that macro ends and hence EnableEvents gets turned back on and Excel will scroll. That said you can try adding ActiveWindow.ScrollRow = Range("cell where you want the window to be") before you turn EnableEvents back to True
mtarler Good solution, thanks. It still has the "whiplash" problem that I mentioned in the OP bu it's a lot better than forcing the user to scroll.
- I still don't understand why you need the selected cell. I would go with the global variable or even if you want to use a cell to store info or to change the info in that cell you can still reference that cell in the latter procedure. Or maybe just have the macro prompt the user for the change/update you want. It is very hard to suggest without seeing the actual application/process.