Excel worksheet change

VBA Excel Worksheet.Change Event

This Excel VBA tutorial explains how to use Worksheet.Change Event.

You may also want to read:

VBA Excel Worksheet_Change Event

Excel predefines some popular actions that you would do on different Objects (worksheet, workbook, button, etc), those actions are called Event. For example, activating a worksheet is an Event, closing a workbook is an Event, clicking on a button is an event. Each Object has its own list of Events, Workbook has a list of Events (e.g. close workbook, open workbook), worksheet has a list of Events (e.g. activate worksheet, edit a Cell).

If you perform an Event, say, closing a workbook, your desired code can be triggered. For example, you may want to save a workbook automatically when you close a workbook, or you may want a welcome message box to pop up when a workbook is opened. Event is a Sub Procedure (begin with Private Sub and end with End Sub) and is generated automatically (see in the below section) with a specific name, you can call a Sub Procedure or write your own code within the Event code.

Excel Worksheet_Change Event is an Event triggered when a you leave a Cell from edit mode (even no value is changed). For example, you double click on Cell A1 to enter edit mode, the event is triggered as you press “Enter” or click on any other Cell to exit the edit mode. Excel Worksheet_Change Event is not about value change of a Cell (of course the Event will trigger if you change a value), don’t be misled by the name.

If you want to know how to capture the initial value before change in order to compare the old and new value, read the below article

How to insert Excel Worksheet_Change Event

Like all other worksheet events, you have to define the event and your desired actions within a specific worksheet where you want to Macro to trigger, each worksheet can have its own independent events.

1) Press Alt+F11 to enter into Visual Basic Editor

2) In the Project Explorer Window on the left, double click on the target worksheet

3) On top of the coding area, select “Worksheet” in the drop down box on the left, and then select “Change”.

4) Now you should be able to see two lines of code as below. Insert your action code between the two lines.

Example of Excel Worksheet_Change Event

For example, I want to prompt a message box if column A value >100.

In the above code, “Target” is the Range you make a change. Target.Column = 1 bounds the checking to column A.

Trigger Macro when Value Changes

Below is a solution I copied and pasted from Microsoft Community that was answered by me.

Question

How can you instruct a macro to run when a certain cell changes?

For example, as soon as text in cell A1 changes, a macro is triggered.

Excel Dashboards VBA and more

Your Custom Text Here

Excel Dashboards VBA and more

Worksheet Change Event

Automatically Run Excel Macros When a Cell Changes

VBA Change to a Single Cell

In Excel a Worksheet Change Event is a trigger for a macro when a cell or group of cells change. I will start out by showing how a change to a single cell can trigger an action. The following will colour cell B2 Red whenever the cell changes. The following uses the(ByVal Target As Range) line which uses the Variable named Target. The Target is the Range which will trigger an action. You assign the Range within the code itself.

Before you fill your boots with the following it is worth mentioning that when you employ the use of the VBA change events you lose the ability to undo in Excel. Normally Excel keeps a record of a number of actions.

The VBA code to perform this action needs to go in the sheet object you want to perform the event. If you wanted to put the code in Sheet1 then you would double click on the sheet you wish to run the code from.

The following is an example of Excel VBA coding you could put in Sheet1 or any of the other sheet objects.

In the example above you need to keep the $ (absolute sign) or the code will not work. So when referencing a single cell the range reference needs to be absolute.

The following VBA performs the same action as the above example. It is a little more flexible if you wish to add to the range. Once Inside the Worksheet Change Event, if the Target falls within the defined Range and the cell contents change, it will trigger an action inside VBA.

Private Sub Worksheet_Change( ByVal Target As Range ) ‘Excel VBA with more cells in the range.

Disable Events

Occasionally one of the things you may wish to do with the cell that is changing is delete, copy, cut or some other action which triggers a circular loop. For example, if you wanted to move a line to another sheet which met a condition, when the condition was met you would trigger the Change Event and when you deleted the row you would start another change event. This second change event would cause a debug error. To get around this you can turn Events off at the start of the procedure and turn them back on at the end of the procedure.

The line of code is;

and the following is an example of how it might be used.

The VBA macro will copy the entire row from one sheet to another and delete the row which was just copied. The example is shown in the file below.

VBA Worksheet Change Event Multiple Cells

When we want to perform an action when more than one cell is changed we can use the following VBA code to change a larger range.

VBA Double Click Event

A double click event in Excel VBA is self explanatory. It will occur on double click of a cell in the Target range. So if you have a range between C13 and O26 where you want to perform an action on Double click, the following should help.

VBA Before Save Event

This event is triggered as the name suggests before each Save. So as the save Excel file icon is clicked the code which is associated with this event will trigger.

The before Save event needs to go into the ThisWorkbook Object in order for it to run.

The following Excel VBA macro will put the word False in Cell A1 before the file is saved.

Excel Dashboards VBA and more

Your Custom Text Here

Excel Dashboards VBA and more

Worksheet Change Event

Automatically Run Excel Macros When a Cell Changes

VBA Change to a Single Cell

In Excel a Worksheet Change Event is a trigger for a macro when a cell or group of cells change. I will start out by showing how a change to a single cell can trigger an action. The following will colour cell B2 Red whenever the cell changes. The following uses the(ByVal Target As Range) line which uses the Variable named Target. The Target is the Range which will trigger an action. You assign the Range within the code itself.

Before you fill your boots with the following it is worth mentioning that when you employ the use of the VBA change events you lose the ability to undo in Excel. Normally Excel keeps a record of a number of actions.

The VBA code to perform this action needs to go in the sheet object you want to perform the event. If you wanted to put the code in Sheet1 then you would double click on the sheet you wish to run the code from.

The following is an example of Excel VBA coding you could put in Sheet1 or any of the other sheet objects.

In the example above you need to keep the $ (absolute sign) or the code will not work. So when referencing a single cell the range reference needs to be absolute.

The following VBA performs the same action as the above example. It is a little more flexible if you wish to add to the range. Once Inside the Worksheet Change Event, if the Target falls within the defined Range and the cell contents change, it will trigger an action inside VBA.

Private Sub Worksheet_Change( ByVal Target As Range ) ‘Excel VBA with more cells in the range.

Disable Events

Occasionally one of the things you may wish to do with the cell that is changing is delete, copy, cut or some other action which triggers a circular loop. For example, if you wanted to move a line to another sheet which met a condition, when the condition was met you would trigger the Change Event and when you deleted the row you would start another change event. This second change event would cause a debug error. To get around this you can turn Events off at the start of the procedure and turn them back on at the end of the procedure.

The line of code is;

and the following is an example of how it might be used.

The VBA macro will copy the entire row from one sheet to another and delete the row which was just copied. The example is shown in the file below.

VBA Worksheet Change Event Multiple Cells

When we want to perform an action when more than one cell is changed we can use the following VBA code to change a larger range.

VBA Double Click Event

A double click event in Excel VBA is self explanatory. It will occur on double click of a cell in the Target range. So if you have a range between C13 and O26 where you want to perform an action on Double click, the following should help.

VBA Before Save Event

This event is triggered as the name suggests before each Save. So as the save Excel file icon is clicked the code which is associated with this event will trigger.

The before Save event needs to go into the ThisWorkbook Object in order for it to run.

The following Excel VBA macro will put the word False in Cell A1 before the file is saved.

How to Switch Between Worksheets in Excel

When you have 50 tabs this will be a godsend

Excel is a very powerful program. If you’re someone who uses spreadsheets often, learning how to operate Excel can make a huge difference. But even if you already know the basics, there are always a few more tricks that can improve your overall experience working in the app.

One of the handy tricks to learn is the different methods you can use to quickly switch between sheets in Excel. We’ll begin with the simple options like doing it manually and then gradually move on to other alternatives for more advanced users.

Keyboard Shortcuts To Switch Between Sheets In Excel

So you’ve got an Excel document in front of you with multiple worksheets. When you’ve got to switch between them, the obvious choice would be to do it by manually selecting a different sheet at the bottom of the workbook. However, that option might not be available.

In case you can’t use your mouse or touchpad or have a big workload and don’t waste time on that, you can switch between sheets in Excel using a keyboard shortcut.

There are two shortcuts that you’ll need to learn here: Ctrl + PgDn (page down) for moving to the sheet on the right, and Ctrl + PgUp (page up) for moving to the sheet on the left. Or simply follow these steps to switch between sheets in the workbook:

1. In order to move one sheet to the right, hold down Ctrl, then press the PgDn key.
2. To keep moving to the right, press the PgDn key again.
3. In order to move back or one sheet to the left, hold down Ctrl, then press the PgUp key.
4. To keep moving to the left, press the PgUp key again.

That’s just one example of a useful keyboard shortcut for Excel. Learning a few more essential shortcuts can help improve your productivity. For example, it will save you time while navigating Excel, or doing such routine tasks like changing row height and column width in the program.

Use The Go To Command

Using the Go To command is another handy way of moving around your Excel worksheet. You will especially appreciate this method if you have to deal with overloaded Excel documents with lots of rows and columns on a daily basis.

In order to move to a different space of your workbook using the Go To command, follow these steps:

• Open your Excel worksheet.
• Choose Edit from the menu on top of the screen.

• Select Go To… option from the drop down menu. You will see the Go To window open. You can use that to jump from one place in your document to another.

• In the dialog window, find the Reference line and type in your cell reference, like “C22” or “H134”. Then press OK.

This will take you to the place you’ve chosen by moving the active cell box onto that specific cell.

Enable Go To Command Using Keyboard Shortcuts

A quicker way to use the Go To command in Excel is by learning the right keyboard shortcuts for it. There are two different options here, you can either press the F5 key or use the Ctrl + G keyboard shortcut to bring the Go To window up.

Once you start using the Go To feature, you will see that it also stores all of your previous searches in that little window. So you can find a list of all your previous cell references in the top box above the Reference line. A useful feature if you find yourself jumping back and forth between specific areas of your Excel document.

However, remember that this information will disappear once you close your Excel workbook. So if you think you’ll need it for future reference, make sure you save it elsewhere before exiting the document.

Switch Between Sheets in Excel Using Go To

If you don’t want to jump to a specific cell in a different worksheet, but just want to open the whole thing, you can also use the Go To command to do it.

In order to switch between sheets in your Excel workbook, follow these steps:

• Open your Excel workbook.
• Bring up the Go To window using one of the shortcuts or manually.

• In the Reference line, type in the desired sheet name along with the cell reference. For example, if you’re on Sheet 2 and want to move to Sheet 3, you’ll need to type in “Sheet3 + exclamation mark + the cell reference” in the Reference line, like Sheet3!B5.

The exclamation mark here serves as a separator between the sheet name and the cell number, as you can’t use spaces in cell references.

Use Hyperlink To Change Worksheets

You can navigate around your Excel workbook and jump from one sheet to another using hyperlinks as well. In Excel, there are different hyperlink types, and you can link to an existing file on your computer, a web page, or an e-mail address.

If you want to create a hyperlink and use it to move to a different worksheet, do the following:

• Select a place in your sheet (a cell) where you want to insert the hyperlink.

• Right-click on it and choose Hyperlink from the drop-down menu.

• From the options in the m >

• In the pop-up window, choose the sheet you want to link to and type in the cell reference where you’d like the hyperlink to take you.

• After you press OK, you’ll be taken to the previous window where you can change the text of the hyperlink that you’ll see in the document. Type it in under Display.

You can later delete or edit this hyperlink to change its destination or appearance.

Use a VBA Script to Move Between Sheets

While all of the above options are great for switching between Excel sheets, you might find yourself in a situation when you need more. For example, if you’re working with an Excel document of many worksheets and constantly need to move back and forth between them. In that case you can create a VBA macro or script to automate that process for you.

Excel allows you to automate many different features and commands using Visual Basic for Applications (VBA) scripting. It’s the programming language that Excel uses to create macros.

If you’re completely new to the topic, check out our beginner’s guide to VBA and an advanced VBA guide for MS Excel to learn everything about DIY programming in Microsoft Office.

If you already know a thing or two about it, then you can use macros like the following to move around your Excel workbook.

VBA Macro To Move To The Right

VBA Macro To Move To The Left

Install both codes in your workbook to be able to move in both directions, right and left one sheet at a time.

Become a Pro Excel User

You’re now one step closer to mastering Excel and saving yourself a bunch of time and effort at work at the same time. Of course, there will always be new tips and tricks that you can learn about this software. Like the many ways of sharing an Excel file with your colleagues, or learning to password protect your Excel files.

However, automating your everyday workflow is the first and probably the most important step you can take diving into the world of Excel.

What Excel automation tips do you know and use? Share them with us in the comments section below!

Anya is a freelance technology writer. Originally from Russia, she is currently a full-time Remote Worker and Digital Nomad. With a background in Journalism, Language Studies, and Technical Translation, Anya couldn’t imagine her life and work without using modern technology on a daily basis. Always looking out for new ways to make her life and location-independent lifestyle easier, she hopes to share her experiences as a tech- and internet-addict through her writing. Read Anya’s Full Bio