MS Excel — Reasons to Use VBA Event Code in Your Worksheets
I rarely see this used — but it’s super helpful!
When you create Excel workbooks, especially if you are creating them for others to use, implementing basic VBA Event Code is a helpful tool to automate a few steps so that you (or the spreadsheet user) never has to think about it!
Here are two of my favorite reasons to use Worksheet Event Code:
1 — Refreshing a Pivot Table whenever the worksheet is selected. I explained this in an earlier Medium post (see Tip #2).
2 — Making certain formatting changes when a spreadsheet is selected. For example:
The code above steps through three items when I select a worksheet —
- Automatically adjust column width to fit to contents,
- Format column A to a width of 60, and
- Hide columns G & H, which I don’t want displayed for the user (they contain links that I use to create clickable HYPERLINKS).
Here’s the worksheet:
You can learn more about inserting VBA code at the following Contextures.com webpage — the specific section is the third item “Copy Excel VBA Code to a Worksheet Module”.
Give it a try and let me know what you think in the comments below!