Forum Discussion
In VBA how can I prevent change of focus when a cell is selected?
- 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.
mtarler Yes, I will consider using a global variable. The code that changes the variable then becomes responsible for doing what is now being done by Worksheet_Change when the selected cell changes. I avoid global variables because they need to retain state between program executions. When I restart Excel, the global needs to be set to the value it had when the workbook was last closed. I suppose the value could be written into a worksheet cell at program exit and read from the cell in Workbook Open at program startup.
If I need to store values between sessions, I save them in cells in a hidden worksheet.
