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.
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.
- I'm new to VBA and didn't know about hidden worksheets. Good suggestion.
