Excel Visual Basic modules require triggers to initiate. They can be initiated by using CTRL + combination shortcut keys, clicking a button, or simply pressing an ENTER key.
I normally shun the use of CTRL + combination shortcut keys because it is not user-friendly. It requires the user to remember what keys to be pressed at the same time. You can put an instruction on the Excel worksheet but it will make the worksheet look ugly.
Using buttons is a preferential choice for most of the Visual Basic modules. It stands out from the worksheet but still look neat.
In some cases, you may want to consider using the ENTER key to trigger a VB module. It is especially helpful if a VB module should be initiated once a piece of data is inputted. For example, an input of data affects the data source of a pivot table. A pivot table must be manually refreshed to reflect the data change. If a user does it a hundred times, you bet that he/she may forget to refresh the pivot table a few times. A button can be put right next to the input field, but a better way is to let the ENTER key to trigger the refresh of a pivot table.
Below is the suggested Visual Basic module:
The above VB code will be initiated when user input new data in cell B5 and press the ENTER key afterwards. Please note that the user can also press the “Tab” key to initiate the VB module.
In the above VB code, cell B17 is one of the cell of my pivot table. The name of my pivot table is “PivotTable1”.
To avoid the user messing up the input cell location by inserting or deleting a column or row, I usually prefer to name a cell that will affect the VB code. For example, I can rename the cell B5 as “FreqThreshold”. I can also have more than one input cell that will trigger the same VB module. For example, cell C5 is another input cell and I name it as “BalThreshold”. The below is the modified VB code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range(“B5”).Address Then
Range(“B17”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotCache.Refresh
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = Range(“FreqThreshold”).Address) Or (Target.Address = Range(“BalThreshold”).Address) Then
Range(“B17”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotCache.Refresh
End If
End Sub
Please note that the above VB module must be placed in the worksheet, not the “Module” or otherwise it will not work.
