Ease of Use

Intermediate

Version tested with

97, 2003 

Submitted by:

Sir Babydum GBE

Description:

This code, placed at the beginning and end of any code you choose will note the current selected cell or range and return to it after main code has finished running. 

Discussion:

Sometimes you'll want your current cell or selection to be remembered when you run code so that you don't lose your place in a list or large sheet of data. Macros can sometimes be written in such a way that nothing is physically "selected", so your selection remains unchanged. But often, macros will involve the selection and manipulation of other cells or objects which usually means you have to find your place again when the code finishes. So this very simple macro, with your own code inserted, will take you back to where you were before, making the transition from manual input to code and back to manual input seamless. 

How to use:

1. Once you have written or recorded your own macro you can do one of the following:
2. Copy the code you just wrote or recorded (minus the "Sub" and "End Sub" lines) and paste it into the above macro in the indicated place, then change the name of the macro from "Sticky_Selection()" to a name of your choice.
3. Then run your macro as normal.

Test the code:

1. Record a new macro (or use one you have already) and in that macro ensure that you click on a few different cells (perhaps type some random stuff as you go). Run the macro as-is a couple of times and note where the selected cell is when it's finished.
2. Now follow the instructions to add your code to this macro. Once you've done that, select a random cell, then run your macro. Now you will notice that the selected cell will be the same after the macro as it was before.
3. You can test it on the attached sample file too. This file includes a macro (Address1) that inserts an address in the top left corner of the sheet and formats the cells. Click any cell on the worksheet, and then click the first address button. Notice where the selected cells are. Click on "Reset", then select another random cell on the worksheet. Finally, click the second address button (which executes a macro identical to the first one, but with the extra code added) and note where the cell selection is now.

Sample File:

Sticky Selection.zip 14.06KB 

Approved by mdmackillop