60 Things That Excel Can Do

Feature 16 - Logger window (shortcuts)

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