What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleUse VBA code to make buttons that jump to the first row beginning with specific letters
DescriptionThis example shows how to use VBA code to make buttons that jump to the first row beginning with specific letters
KeywordsVBA, Excel, hyperlink, letter, index
CategoriesOffice, Miscellany
The code loops through the worksheet's rows. When it finds a row with first letter that is different from the previous row's first letter, it adds a button. It then displays event handler text for the button in the Debug window.

Unfortunately I don't know of an easy way to automatically add the event handler code to the worksheet so you need to copy and paste the event handlers into the worksheet's code module.

The code finishes by making a top pane so you can display the buttons there and the data in the bottom pane.

Sub MakeLetterButtons()
Dim work_sheet As Worksheet
Dim last_letter As String
Dim next_letter As String
Dim r As Integer
Dim X As Integer
Dim ole_obj As OLEObject
Dim btn As CommandButton

    Set work_sheet = ActiveSheet
    X = 0               ' Make the next button at X = 0.
    last_letter = ""    ' Make the next letter different
        ' from this.


    Debug.Print "***************"
    For r = 2 To work_sheet.UsedRange.Rows.Count
        ' Get the first letter in the next row.
        next_letter = Left$(Cells(r, 1), 1)

        ' See if this is a new letter.
        If last_letter <> next_letter Then
            last_letter = next_letter

            ' Make a button.
            Set ole_obj = work_sheet.OLEObjects.Add( _
                ClassType:="Forms.CommandButton.1", _
                Link:=False, DisplayAsIcon:=False, _
                Left:=X, Top:=0, _
                Width:=10, Height:=10)
            ole_obj.Name = "btn" & next_letter

            Set btn = ole_obj.Object
            btn.AutoSize = True
            btn.Caption = next_letter
            X = X + ole_obj.Width * 2

            ' Write some code for the button.
            Debug.Print "Private Sub " & ole_obj.Name & _
            Debug.Print "    ActiveWindow.Panes(2).Activate"
            Debug.Print "    Cells(" & Format$(r) & ", " & _
            Debug.Print "End Sub"
        End If
    Next r
    Debug.Print "***************"

    ' Move the second row down to make room for the buttons.
    work_sheet.Rows(1).RowHeight = 25

    ' Split the worksheet into two panes.
    ActiveWindow.SplitRow = 1
    ActiveWindow.Panes(1).ScrollRow = 1
    ActiveWindow.Panes(2).ScrollRow = 2
End Sub
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.