I have a simple macro at my Excel worksheet, which does some simple calculation. What happens is that for a better user experience I want the macro to run and when it is finished to return to the cell that the user was before he activated the macro, so that the user won't see any change on the screen.
Ok, so that's not to hard:
Dim activeRow As Integer
Dim activeCol As Integer
Sub myCode
activeRow = ActiveCell.Row
activeCol = ActiveCell.Column
' code...
Cells(activeRow, activeCol).Select
End Sub
The problem with the code above is that the line and columns that were visible before the macro runs are not the same on the end, because the macro may have scrolled the worksheet to the left, right, up or down.
For example, you can see the cell E26 on your display either if the first line visible at your worksheet is 15 or 16.
So the question here is how can I know the line and column number that are visible at the user display?
I was wondering that would be something like:
Dim topLine As Integer
Dim bottomLine As Integer
topLine = ActiveWorksheet.GetVisibleGrid.TopLine
bottomLine = ActiveWorksheet.GetVisibleGrid.BottomLine
re: "... for a better user experience I want the macro to run and when it is finished to return to the cell that the user was before he activated the macro, so that the user won't see any change on the screen."
The easiest way to do this is never use .Select or .Activate. There are less than an isolated handful of circumstances where using .Select is preferable to direct cell and worksheet reference(s) and only half again of those are actually necessary.
See How to avoid using Select in Excel VBA macros for methods on getting away from relying on .Select and .Activate to accomplish your goals.
Get started converted your code to eliminate .Select and .Activate. If you run into problems, post your working code here or at Code Review (Excel). If it is more than a page or so, post sections of it.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments