Chapter 15. Cursors

Chapter 15. Cursors

Let's select the Payments worksheet and click Wizards, Form Wizard, Add Cursor:

Form Wizard, Add Cursor

The add-in highlights the active table row:

Sample of the cursor in Microsoft Excel

Adding cursors using VBA

Adding cursors is easy. Just call the AddTableCursor method:

' Adding cursors, Chapter 15

Sub Chapter15_1_AddCursors()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Dim ws As Worksheet
    Set ws = GetWorksheet(ActiveWorkbook, "Payments")

    If ws Is Nothing Then
        MsgBox "Worksheet Payments not exists"
        Exit Sub
    End If

    ws.Select

    Dim lo As ListObject
    Set lo = GetActiveListObject()

    If lo Is Nothing Then
        MsgBox "Worksheet Payments does not contain a table"
        Exit Sub
    End If

    ' Call addIn.RemoveTableCursor(lo)
    Call addIn.AddTableCursor(lo)

End Sub

Adding cursor buttons using VBA

' Adding cursor buttons, Chapter 15

Sub Chapter15_2_AddCursorButtons()

    Dim ws As Worksheet
    Set ws = GetWorksheet(ActiveWorkbook, "Payments")

    If ws Is Nothing Then
        MsgBox "Worksheet Payments not exists"
        Exit Sub
    End If

    ws.Select

    Call AddSaveAndLoadButtons(ws)
    Call AddCursorButtons(ws)

End Sub

You may use the following macros to create buttons to load and save data, and to move cursors:

' Adds Save and Load buttons

Sub AddSaveAndLoadButtons(ByVal ws As Worksheet)

    If ws.Buttons.Count > 0 Then Exit Sub

    With ws.Buttons.Add(204, 4, 72, 18)
        .Name = "Save"
        .Caption = "Save"
        .OnAction = "Save"
    End With

    With ws.Buttons.Add(278, 4, 72, 18)
        .Name = "Load"
        .Caption = "Load"
        .OnAction = "Load"
    End With

End Sub

 

' Adds cursors buttons

Sub AddCursorButtons(ByVal ws As Worksheet)

    If ws.Buttons.Count > 2 Then Exit Sub

    With ws.Buttons.Add(360, 4, 36, 18)
        .Name = "MoveToFirst"
        .Caption = "|<"
        .OnAction = "MoveToFirst"
    End With

    With ws.Buttons.Add(398, 4, 36, 18)
        .Name = "MoveToPrev"
        .Caption = "<"
        .OnAction = "MoveToPrev"
    End With

    With ws.Buttons.Add(436, 4, 36, 18)
        .Name = "MoveToNext"
        .Caption = ">"
        .OnAction = "MoveToNext"
    End With

    With ws.Buttons.Add(474, 4, 36, 18)
        .Name = "MoveToLast"
        .Caption = ">|"
        .OnAction = "MoveToLast"
    End With

End Sub

Below are the macros of the buttons.

Moving cursors using VBA

The SaveToDB add-in has special methods to move cursors: MoveFirst, MoveNext, MovePrevious, and MoveLast

' Moves the table cursor to the first record

Sub MoveToFirst()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Call addIn.MoveFirst

End Sub

' Moves the table cursor to the previous record

Sub MoveToPrev()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Call addIn.MovePrevious

End Sub

' Moves the table cursor to the next record

Sub MoveToNext()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Call addIn.MoveNext

End Sub

' Moves the table cursor to the last record

Sub MoveToLast()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Call addIn.MoveLast

End Sub