Logger is an Excel application that supports shortcuts and logging of information.
The Original Logger Doc is here
A small form is used that appears like
 .
Note: Below is the raw code. In the upcomming weeks I'll be adding videos and lessons on how to create this tool.
Form
frmLogger.Height = 53
.Width = 348
The form has the following events:
Activate
*
The form has one txtInput control.
.top = 0
.left = 0
.width = 342
.height = 30
Events
Keydown - this is where the majority of the code gets executed.
Private Sub txtInput_KeyDown(ByVal KeyCode as MSForms.ReturnInteger, ByVal Shift As Integer)
'There's an O n e r r o r r e sume next here that causing issue f'n weird
If KeyCode = 13 Then
sSheet = ActiveSheet.Name
sCommand = LCase(Trim(txtInput))
s = sCommand
lSpace = InStr(s," ")
If lSpace > 0 Then
'Check for sc = 1 parm
x = ShortcutExists(Mid(s, 1, lSpace -1), 1)
If x > 0 Then
sRun = Sheets("sc").Cells(x, 4)
sParm = Mid(s, lSpace +1）
sRun = Replace(sRun, "<:parm1>", sParm)
If Left(sRun, 2) = "@v" Then
aMacro = Split(Mid(sRun, 3), " ")
Run aMacro(0), aMacro(1)
Else
RunProgram sRun
End If
Me.txtInput = ""
Sheets("sc").Cells(x, 6) = Seets("sc").Cells(x, 6) + 1
Exit Sub
Else
'Stop
End If
x = ShortcutExists(s, 0)
If x = 0 Then
'Shortcut doesn't exist so
' check if special sc
' or log the data
If Left(s, 2) = "f " Then
sSearch = Mid(s, 3)
'Python code = os.path.expanduser('˜/logger_log.txt),"r")
y = bOpenSeqFile("c:\users\userid\logger_log.txt,"I")
z = bOpenSeqFile("c:\users\userid\logger_search.txt,"O")
Do While Not Eof(y)
Line Input #y, sInput
If IsDAte(Mid(sInput, 1, 10)) and sInput <> "" Then
bLogEntry = True
Else
bLogEntry = False
End If
Loop
Close #y
Close #z
RunProgram "c:\users\userid\logger_search.txt"
ElseIf txtInput = "sc" Then
Sheets("sc").Select
Me.txtInput = ""
Exit Sub
ElseIf Left(s, 3) = "sc," Then
Shortcuts = Split(s, ",")
x = ShortCutExists(Shortcuts(1), Shortcuts(2))
If x = 0 Then
Sheets("sc").Select
r = FindLastRow(1) + 1
Cells(r, 1) = Shortcuts(1)
If Instr(Shortcuts(2), " 0 Then
Cells(r, 2) = 1
Else
Cells(r, 2) = 0
End If
Cells(r, 3) = Shortcuts(1) & Cells(r, 2)
Cells(r, 4) = Shortcuts(2) ' this is optional
cells(r, 7) = Now()
else
'Stop
sOldSC = Sheets("sc").Cells(x, 4) '@hc 4
If Not dispYN("sc '" & Shortcuts(1) & "' exists as '" & sOldSC & "' continue?") Then
Exit Sub
End If
Sheets("sc").Select
Cells(x, 4).Select
Cells(x, 4) = Shortcuts(2)
If InSTr(Shortcuts(2), " 0 Then
sParm = 1
Else
sParm = 0
End If
Cells(x, 3) = Shortcuts(1) & sParm
Cells(x, 5) = Shortcuts(3)
Cells(x, 6) = 0
Cells(x, 7) = Now()
End If
Else
RunProgram s
End If
Me.txtInput = ""
Else
'Shortcut exists
Sheets("sc").Select
s = Cells(x, 4)
If Left(s, 2) = "@b" Then
Msgbox Mid(s,3)
ElseIf Left(s, 2) = "@c" Then
'copy to clipboard
SetClipboard Mid(s, 3)
ElseIf Left(s, 2) = "@h" Then
Sheets("sc").select
Cells(x, 6) = Cells(x, 6) + 1
Sheets(Mid(s, 3)).Select
r = FindLastRow(1)
Cels(r + 1, 1).Select
Me.txtInput = ""
AppActivate title:=ThisWorkbook.Capation
Exit Sub
ElseIf Left(s,2) = "@v" Then
Sheets(sSheet).Select
Run Mid(s, 3)
Else
RunProgram s
Me.txtINput = ""
Exit Sub
End If
txtInput = ""
End If
Sheets(sSheet).Select
End Sub
Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturInteger)
If KeyAscii = 27 Then
Unload Me
Exit Sub
ElseIf KeyAscii = 115 Then '??
'Nothing
Exit Sub
End If
End Sub
UserForm_Activate
Private Sub UserForm_Activate()
Me.Top = 10
Me.Left = Application.Left + 700
End Sub
Function ShortcutExists(sShortcut, sAction)
'passes shortcut, parm count
'returns 0 if flase or the row if true
'@only supports 1 parm for now
If InStr(sAction, " 0 Or sAction = 1 Then
lCnt = "1"
Else
lCnt = "0"
End if
ShortcutExists = Application.Evaluate("=match(""" & sShortcut & lCnt & """,sc!C:C,0)")
IF IsError(ShortcutExists) Then
ShortcutExists = 0
End if
End Function
|