Majority of people working with computer systems love shortcuts on keyboard. Few of the shortcuts which are custom made can help you to clean Undo Stack, which is quite painful. You have to basically try to balance the shortcut’s gain on productivity and their side effects. You can make a code to track the shortcuts you use. The following article provides you with a code. For instance, take a look at a random result shown below:
You can see from the table that, pasting special is at the top of the list. The count is low during the 1st two weeks as the shortcut Alt+E+S+V was used to pop up the dialog of paste special as against the shortcut Ctrl+Shift+V which is assigned to the macro. The latter shortcut was put in use in February.
The code is as follows:
gclsAppEvents.AddLog "^+v", "CopyPasteValues"
If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then
ElseIf Application.CutCopyMode = xlCut Then
If Not ActiveSheet Is Nothing Then
The values are pasted in number format. If you are interested in seeing the code for any other macros present in the list then see MakeComma, FillSeries, SelectAdjacentCol, ChangeSign, FrozenHome, Increment Date, Wrap Sheets and Formatting Taskpane
You can create a Clog class as well as a Clogs class to track all of them. Clog has LogID, ProcName getter/setter properties, DateTime and the keys.
CLogs is a parent class with deviation. You can add an AddLog as well as a WriteLog procedure.
Public Sub AddLog(ByVal sKeys As String, ByVal sProcName As String)
Dim clsLog As CLog
Set clsLog = New CLog
clsLog.Keys = sKeys
clsLog.ProcName = sProcName
clsLog.DateTime = Now
Public Sub WriteLog()
Dim sFile As String, lFile As Long
If Me.Logs.Count > 0 Then
sFile = ThisWorkbook.Path & Application.PathSeparator & "UIHelpers.log"
lFile = FreeFile
Open sFile For Append As lFile
Print #lFile, Me.Logs.LogFileLines
All you have to do is insert the call to the AddLog.
Private Sub Class_Terminate()
In CLogs, one can return the whole log lines as a huge sequence to write to the file.
Public Property Get LogFileLines() As String
Dim aWrite() As String
Dim clsLog As CLog
Dim lCnt As Long
If Me.Count > 0 Then
ReDim aWrite(1 To Me.Count)
For Each clsLog In Me
lCnt = lCnt + 1
aWrite(lCnt) = clsLog.LogFileLine
LogFileLines = Join(aWrite, vbNewLine)
It calls the CLog.LogFileLine
Public Property Get LogFileLine() As String
Dim aWrite(1 To 3) As String
aWrite(1) = Me.DateTime
aWrite(2) = Me.Keys
aWrite(3) = Me.ProcName
LogFileLine = Join(aWrite, "|")