Forum Discussion
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 same worksheet. This cell contains metadata about the selected element that is only used by the application. So the user shouldn't see it. Unfortunately, when the cell is selected, focus shifts to the selected cell. I would like to prevent this change of focus from taking place.
I should mention that the application also handles Worksheet_SelectionChange events, so when the cell is selected, the handler is called. Unfortunately, by the time the handler is called, focus has already shifted. I could immediately shift focus back to the chart, but this would cause an annoying whiplash effect.
Is there any way to suppress the focus change?
- 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
10 Replies
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 = Falsebefore, 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
- Let me ask why you select that cell in the first place? Without the code i don't know what you are doing but in nearly all cases it is poor form, poor performance, and generally a bad idea to actually "select" cells in your macro. Just assign the .value of that cell to a variable as needed.
- When the cell is selected, its contents do not change. However what the user does next on the chart WILL change the contents. You could argue that I should be using a global variable for this, but the cell is being watched for changes by Worksheet_Change. That's why I'm using a cell and not a variable. I don't think there's a way in VBA to watch for changes to a variable, but I'm new to VBA so maybe there is.
