Excel VBA Active Cell Property
Instead of typing the name of a cell you can also refer to which cell on your spreadsheet is currently highlighted. The currently highlighted cell is called the ActiveCell (no spaces and with capital letters for the “A” and “C”). You can use this in your code. Let’s see how it works. After the End Sub of your first Subroutine, add the following:
Press the enter key on your keyboard to let the VB editor add the End Sub for you. Now add the following line between the Sub and End Sub of your code:
Your coding window will then look like this:
So the top left cell we want to select is the ActiveCell, which is whatever cell you clicked in on your spreadsheet. The bottom right cell we want to select is D6. Click the icon to return to your Excel spreadsheet. Now draw another button on your form, just below the first one. You should see the Assign Macro dialogue box appear again.
Select your new Macro (your Sub) from the list and click OK.
When you get back to your spreadsheet, edit the text of the button again. Type ActiveCell as the text. When you have finished editing the text, click away. Click inside another cell on your spreadsheet, cell A2 for example. Now click your button. You should see the cells A2 to D6 highlighted.
Click inside any other cell on your spreadsheet and click the button again. The cells from your active cell to D6 will be selected. In the next slide of this tutorial, we’ll take a look at the Offset property. So save you work before moving on.