Run a Macro when a User Does Something in the Worksheet in Excel

How to run a macro when a user does something in the worksheet, everything from selecting a cell, to editing a cell, to changing the worksheet.

You can set a macro to run in many different situations based on what the user does.

There are two different kinds of events that we can use to initiate macros, here, I will cover events that are worksheet specific, those that happen within a worksheet.

Sections:

Worksheet Events - What they are and How to Use Them

Common Worksheet Events

Notes

Worksheet Events - What they are and How to Use Them

A Worksheet event is a way to run a macro when a user does something within a worksheet. These events are specific to the worksheet in which you place them.

To access these events go to the VBA editor window (Alt + F11) and then double-click any worksheet from your project:

The worksheet that you double-click is the one where a user's action will cause the macro to run. In other words, if you put the macro code in Sheet1, then it will only run when the desired event occurs in Sheet1.

Now, in the window that opens, click the left drop-down menu and select Worksheet

You will see text fill-in by default, but don't worry about it, click the right drop-down menu and you will see all of the options.

The default text that appeared is for the SelectionChange event, explained below.

The options in the drop-down menu refer to events that can a user can do in the worksheet that can cause a macro to run.

When you select an event, some code will automatically appear and you will put your macro code within that code.

Using worksheet events and specific examples are covered in the next section.

Common Worksheet Events

There are a number of worksheet events that you can use but I'm going to touch on the most often used events here.

Once you read through an example or two, you will be able to easily understand the events that I don't cover; their names are usually self-explanatory and they all use similar syntax.

Worksheet Activate

This runs the macro every time the user navigates back to this worksheet after being on another worksheet.

Select Activate from the menu.

Put a simple message box in there and test it out.

Now, when I go back to Excel and navigate to another sheet and then go back to Sheet1, I see this:

Worksheet Change

Run a macro any time the contents of a cell are changed, removed, or added.

Select Change from the drop-down menu.

Let's put our message box code in there and see what happens in Excel.

I put a 1 into cell A1 and the second that I hit Enter, the macro runs and I see the pop-up message.

This Event seems different:

You may have noticed this: ByVal Target As Range in-between the parenthesis. This allows you to get information about the specific cell that was changed.

To access the information about the changed cell, we use Target. Target is the variable that contains all of the information about the changed cell.

So, to get the value of the cell, I type Target.Value. Let's combine that with the msgbox code to output the current value of the edited cell.

Go back to Excel and edit a cell and we see this:

I changed A1 from 1 to 4 and, using Target, I output that value into the message box.

To see all of the possible pieces of info that you can get from Target, just type Target. (note the period after it) and you will see a list of options:

This may seem a little confusing at first, but play around with this code and you will get the hang of it pretty quickly.

There are a number of other worksheet change events that have a similar format.

Worksheet Deactivate

This runs a macro when the user navigates away from this worksheet, as in, when they click the tab of another worksheet. This is the opposite of the Activate worksheet event.

Select Deactivate from the drop-down menu.

Put some sample code in there and it will run each time the user leaves this worksheet.

Worksheet Selection Change

This runs a macro every time the user selects a cell within the worksheet.

Select SelectionChange from the drop-down menu.

We can put some message box code in there to test it out:

To be honest though, the message box code gets really annoying with this event because it runs every time a cell, row, column, or group of cells is selected.

Get Info about the Selected Cell:

Like the worksheet Change event, this event has this: ByVal Target As Range

That means that all of the information about the selected cell will be contained in the variable Target. We can get lots of information about the currently selected cell, but the most common thing is to get the value of it. This can be used when you want a macro to run only if the user has input a certain value into the worksheet.

Here, I'll keep it simple and output the value of the currently selected cell into a message box.

Back in Excel, it will look like this:

I clicked cell A1, so it gave me the value of cell A1.

You can also see a full list of attributes that you can get from the Target variable once you type Target. (note the period after it).

Notes

Using a worksheet event to trigger a macro in Excel is a very useful feature. If allows you to have your macros run when the user is performing a normal task in Excel instead of forcing them to click a button or select and run a macro.

This feature is a great way to add automation to your spreadsheets.

Remember that macros need to be enabled in order for them to work.

You can also run macros based on changes made in the Workbook and that is covered in the next tutorial.

Download the sample file attached to this tutorial so you can see this code in Excel and use it and better understand it.