Cell activation vba

The name is usually the row character followed by the column number, such as cell A1. In the above image, the active cell address is B3. Even when the many cells are selected as a range of cells, whatever the first cell is in, the selection becomes the active cell. In our earlier articles, we have seen how to reference the cells How To Reference The Cells Cell reference in excel is referring the other cells to a cell to use its values or properties.

By Active Cell property, we can refer to the cell. But here I have used ActiveCell. The reason why I have used Active Cell property because the moment I select the cell A1 it becomes an active cell. Now, look at the below image. There are many such events in VBA, and you can create codes for these events.

This means that as soon as an event occurs, and if you have specified a code for that event, that code would instantly be executed. Excel automatically does this as soon as it notices that an event has taken place.

So you only need to write the code and place it in the correct event subroutine this is covered later in this article. For example, if you insert a new worksheet and you want it to have a year prefix, you can write the code for it. Another example could be that you want to change the color of the cell when someone double-clicks on it.

You can use the double-click event for this. Below is a short visual that shows the double-click event in action. As soon as I double click on cell A1. Excel instantly opens a message box that shows the address of the cell. Double-click is an event, and showing the message box is what I have specified in the code whenever the double-click event takes place. While the above example is a useless event, I hope it helps you understand what events really are.

There are different objects in Excel — such as Excel itself to which we often refer to as the application , workbooks, worksheets, charts, etc. For each worksheet in the workbook, you will see one object.

When you double-click on the worksheet object in which you want to place the code, it would open the code window for that worksheet. To do this, you need to first select worksheet from the drop down at the top-left of the code window. After selecting worksheet from the drop down, you get a list of all the events related to the worksheet.

You can select the one you want to use from the drop-down at the top right of the code window. As soon as you select the event, it would automatically enter the first and last line of the code for the selected event. Now you can add your code in between the two lines. Note: As soon as you select Worksheet from the drop-down, you would notice two lines of code appear in the code window. Once you have selected the event for which you want the code, you can delete the lines that appeared by default.

Note that each worksheet has a code window of its own. When you put the code for Sheet1, it will only work if the event happens in Sheet1. Just like worksheets, if you have a workbook level event code, you can place it in ThisWorkbook code window. After selecting Workbook from the drop down, you get a list of all the events related to the Workbook. Note: As soon as you select Workbook from the drop-down, you would notice two lines of code appear in the code window.

For example, you can specify a code that is executed when the button is clicked. When you double-click on the UserForm or any of the object that you add to the UserForm , it would open the code window for the UserForm. Now just like worksheets or ThisWorkbook, you can select the event and it will insert the first and the last line for that event.

And then you can add the code in the middle of it. In Excel, you can also insert Chart sheets which are different then worksheets. A chart sheet is meant to contain charts only. When you have inserted a chart sheet, you will be able to see the Chart sheet object in the VB Editor. Double click on the Chart sheet object in the Project Explorer.

This will open the code window for the chart sheet. After selecting Chart from the drop-down, you get a list of all the events related to the Chart sheet. Step 1: For this open a module and put the subcategory in the name of the performed function or in any other name as per your choice. We are keeping the name but in sequence, as shown below. Step 2: Again in the same manner use Sheets function with sheet name as shown below. Step 4: Now in the second line of code, we will use Sheets function along with Activate for sheet named Second as shown below.

Step 5: And for the last sheet named as Third we will use Worksheets along with Select function to activate it as shown below. Step 6: Now compile the complete code and run the code step-by-step to see the changes by pressing the F8 key. As the compiler passes through the first line of code, sheet First will be activated.

As the compile goes through the second line of code, sheet Second will be activated. And at last, when compiler comes to End Sub, sheet Third will get activated as shown below. This is how we can use a combination of Sheets and Worksheets along with Activate and Select functions to activate any worksheet.

Here we discussed how to Activate sheet in Excel using VBA code along with some practical examples and downloadable excel template.

You can also go through our other suggested articles —. Submit Next Question. By signing up, you agree to our Terms of Use and Privacy Policy. Forgot Password? Similar threads M. Solved VBA and data validation. Replies 4 Views Aug 3, mhessnm. Solved Run VBA Macro when cell value updates, but cell value is a formula that references another sheet. Replies 12 Views Dec 30, Joe4. Replies 3 Views Sep 30, offthelip. Aug 25, Joe4.

ExCellibur Sep 27, Excel Questions. Replies 0 Views Sep 27, ExCellibur. Understanding data is crucial, and the easiest place to start is with Microsoft Excel. We've detected that you are using an adblocker. We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.