VBA – 5 Excel Worksheet Based Tricks

In this article, I present 5 useful Excel worksheet based tricks:

  1. Set a date and time for the beginning of the latest update on the sheet;
  2. Color the changed cells in red;
  3. Change the sheet name based on the value of a cell;
  4. Automatic Upper Case conversion in a given range;
  5. Set a message box that alerts if a change has been performed.

The code of all of them should be put in the worksheet and not in a module, as follows:

HourAndMinutes


1. Showing the date and the time of the activation of a specific worksheet is easily done with 3 lines of code:

Rather easy and understandable. The first line sets a number format for the time on cell A1. The second one sets the cell A1 to current time. Then we have current date on cell A2. Due to the fact that the time takes more place and it cannot be displayed normally by Excel, we have to enlarge the columns slightly. Thus, we use the last line of the code, causing the AutoFit effect in Excel.


2. Color Changed Cells in Red.

As we already know from this article, about 50 colors in VBA have some kind of index. The index for red is 3. Thus, we simply ask VBA to color every cell in Color.Index=3 upon a change of the value:


3. Change a sheet name based on a value of a cell

As far as we all know how to create a formula, giving us the name of the sheet in Excel (if you do not know this is the formula =RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1))) end of the formula). However, writing something in a cell and automatically changing the name of the spreadsheet based on it seems like a challenge. In VBA this task is carried out with two lines of code:


4. Automatic Upper Case conversion in a given range

This is a challenge task as well. Sometimes we simply want to have places in our documents, where we use only upper case. With the following VBA code, the cells are made UPPER CASE as you write:

With this code you will receive automatic UPPER CASE within the range D1:D100.


5. Set a message box that alerts if a change has been performed.

So, this is the last piece of code for this article. It simply gives a MessageBox if you change anything in the sheet, except for cell A1.


That’s all folks!

About

VBA Developer

Tagged with: , , , , , , , , , ,