Ruturn the cursor to its original location after a macro moved it

[qwerasdz]

I'm creating a form that has fill in the blanks and option buttons. The user can fill out the form in any order-option buttons first and fill in the blanks second or vise versa. Each option button runs a macro that moves the cursor to a different cell location in a hidden column. Currently, I have each macro end by selecting cell E7, which is in a visible column and near the fill in the blank section.

Users are confused why they left the cursor on cell B4, clicked on an option button, and now the cursor is on cell E7.

How can the cell location be memorized, the macro run that moves the cursor to hidden cells for processing, and then the cursor returned to where the user left it? The cursor position is a variable. Thanks in advance.

 

[LoNeRaVeR]

qwerasdz:

That is a such a great question! It's one that probably stumps each of us until we realize the power that variables can provide. The easiest way to do this is to save the current cell address to a variable and then select that address when needed.

Sub RememberLocation()
Dim sRange As String
sRange = ActiveCell.Address
' Perform other actions
Range(sRange).Select
End Sub

Assign the users current cell address to a variable. In this case sRange. Perform your actions. Then use the Range Object with the Select Method to select the address previously assigned to your variable. A few lines of code to end your user's frustration. ;-)

If you are switching to other sheets and performing numerous copying, pasting, and other actions, I suggest that you stop updating the screen before making these changes. This will eliminate any flicker that users may see when performing these "unseen" tasks. This can be done by using

Application.ScreenUpdating = False

After selecting the "original" cell address and just before relinquishing control back to the user, don't forget to turn ScreenUpdating back on with

Application.ScreenUpdating = True


I hope that helps! Regards,

LoNeRaVeR

 

[qwerasdz]

This woked exactly the way I wanted it to work. Thank you.

Thanks also for the Application.ScreenUpdating = True/False. It makes the macro run quicker and the spreadsheet looks more polished. Thanks again.

 

[LoNeRaVeR]

qwerasdz:

I have numerous spreadsheets that do similar actions and find that Application.ScreenUpdating helps a great deal.

I'm glad I was able to help! Regards,

LoNeRaVeR

 

[Store300]

As an aside, I tend to find that switching off the screen updating causes problems with my not knowing what's going on at any given point in time (VBA rather than life in general..although...).
A useful addition is:

Application.StatusBar = "Useful bits of text"

Which can be used to show whats going on on your status bar. To switch it off at the end of the macro use:

Application.StatusBar = False