In this article, I present 5 useful Excel worksheet based tricks:
- Set a date and time for the beginning of the latest update on the sheet;
- Color the changed cells in red;
- Change the sheet name based on the value of a cell;
- Automatic Upper Case conversion in a given range;
- 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:
Showing the date and the time of the activation of a specific worksheet is easily done with 3 lines of code:
|
1 2 3 4 5 6 |
Private Sub Worksheet_Activate() Range("A1").NumberFormat = "[$-F400]h:mm:ss AM/PM" Range("A1").Value = Format(Time, "hh:mm:ss") Range("A2").Value = Format(Date, "dd/mmm/yyyy") Columns("A:A").EntireColumn.AutoFit End Sub |
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.
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:
|
1 2 3 |
Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = 3 End Sub |
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:
|
1 2 3 4 |
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "B1" Then Exit Sub ActiveSheet.Name = Range("B1").Value End Sub |
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:
|
1 2 3 4 5 6 7 8 |
Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range Set MyRange = Range("D1:D100") If Not Intersect(Target, MyRange) Is Nothing Then Target = UCase(Target) End If End Sub |
With this code you will receive automatic UPPER CASE within the range D1:D100.
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.
|
1 2 3 4 |
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "A1" Then Exit Sub MsgBox "Brace Yourself! A change has been carried out!" End Sub |
That’s all folks!