By using expertatexcel.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

expertatexcel.com

 


Home
What can Excel do?
Learning Excel Books
Basic concepts Immediate window Macros 101 Syntax Use existing code Hello world Math formulas Functions Modifying Recorded Macros
Selecting Moving data Formatting Clipboard Range names Filters Sorting Fill Combining Hyperlinks Comments Options Vlookup Conditional Formatting Data validation Pivot Tables Charts Charts
Debugging Strings Active This vba statements VBA functions Run stuff
Workbook Sheet Cells Rows Column Range Selection Methods Properties Events Collections Chart
Variables Data types variables/scope Conditionals Loops functions/sub Arrays testing Batch Software dev techniques Backup/Recovery Documentation Wrappers webservice Refactor
#Errors Problems Too Slow/Optimize Error handling Problem Solving Method Someone else's sheet complex formula
Date/times Code Generation Random Shortcuts Forms Special Chars Customize your training Work and learn
Reporting Sample projects Reconcliation Adjusting data Generate Slides Special Characters
Outlook Web Database Files PPT Word API Objects Script host Dos Bloomberg JIRA Input Spreadsheets or files
Stocks Bitcoin Sports Job Productivity Project Management Tracking Health Games Programmer

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

    •