Modify Excel VBA Code in Sample Files

On my website, there are hundreds of Excel sample files that you can download and use. Some of the files contain macros, and you might need to adjust those macros, to use them in your own files.

There is a page on my Contextures site, that explains how to copy the sample code into your files, and where to paste it. There are written instructions, and a few videos, to help you get started.

viewcode

Change the Target Columns or Rows

On that page, I’ve just added a new section, that shows how you can modify the code, so it runs on specific rows or columns.

For example, in the screen shot below, you can see some code from one of my Data Validation Multi Select files. There is an If…End If section, marked with red dots at the start and end.

The If line checks the column number, for the cell where you made the change (the Target cell). If the column number is 3, the code in the If…End If section will run. For any other column, it skips that section of the code.

DataValMulti04b

You can change the code so it runs on a different column, or multiple columns, or any column. There are code samples below.

NOTE: In all of these examples, you could use Row instead of Column, to limit the target to specific rows.

Change the Column Number

The simplest change is to use a different number in that line of code. For example, if you want the code to run when a cell in column E is changed, you could change the 3 to a 5.

If Target.Column = 5 Then

Use Multiple Column Numbers

Instead of restricting the code to just one column, you can add more columns in the code. For example:

If Target.Column = 3 _
    Or Target.Column = 5 _
    Or Target.Column = 6 Then

Remove the Column Limits

If you don’t want to limit the code to a specific column, you could delete the two rows (If…End If) that are marked with red circles. In that case, the code will run for a change in every column.

Be sure to delete both the If and the End If lines.

Exclude a Specific Column Number

If you want code to run on any column EXCEPT a specific column, use the Not Equal To operator — <> — instead of the equal sign.

For example, this code will run after a change in any column, except column 2:

If Target.Column <> 2 Then

I hope that helps you get started with customizing code that you copy from sample files!

_____________________

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *