Page up Page down in a list box

[Aussiebear]

I should have thought of this earlier. The mention of SendKeys "Fn+{PGDN}" is the smoking gun.
This immediately tells us a few critical things:

1. You are NOT using TopIndex: Because if you were using TopIndex (which is the direct programmatic way to scroll a UserForm ListBox), you wouldn't need SendKeys.
2. You are simulating key presses: SendKeys is a way to programmatically send keystrokes to the active application or window.
3. You are most likely using an ActiveX ListBox on a Worksheet (or a Form Control ListBox): These types of ListBoxes on a sheet don't expose TopIndex. When they are active, the Page Down key does work on them, which is why SendKeys works to simulate that.

Why SendKeys is "Not Practical" (and generally a bad idea for this):

• Reliability Issues: SendKeys is notorious for being unreliable. It depends on the target application/window being active and ready to receive the keys. If the user clicks away, or another process takes focus, SendKeys can fail or send keys to the wrong place.
• Speed: It's often slow because it's mimicking user input.
• Lack of Control: You can't directly control how much it pages down beyond what the default Page Down key does.
• Foreground Focus: The Excel window needs to be active and the ListBox needs to have focus for SendKeys to work on it reliably.

The core problem remains the same: the .TopIndex property is the correct way to achieve programmatic paging, and it's available for ListBoxes on UserForms.

The SendKeys "Fn+{PGDN}" method is indeed working because it simulates the actual Page Down key press that Excel responds to when a ListBox has focus. However, as you've found, it's not practical or reliable for robust code. The reason you're having to use SendKeys is almost certainly because the ListBox you are working with does not have a .TopIndex property. This is characteristic of:

1. An ActiveX ListBox placed directly on an Excel Worksheet.
2. A Form Control ListBox placed directly on an Excel Worksheet.

For reliable and practical page up/page down functionality, the standard and best practice in Excel VBA is to use a ListBox placed on a UserForm.

• The .TopIndex property: A ListBox control that you add to a UserForm (from the VBA Editor: Insert > UserForm) does have a property called .TopIndex. This property allows you to directly control which item is at the very top of the visible list, enabling precise programmatic scrolling (paging).
• How it works with UserForms:
  
  • You can set ListBox1.TopIndex = ListBox1.TopIndex + [Number of items per page] for page down.
  • You can set ListBox1.TopIndex = ListBox1.TopIndex - [Number of items per page] for page up.
  • You can easily calculate the 'page size' by finding how many items are visible in the ListBox without scrolling, or just define a fixed number.

Open the VBA editor.
Go to Insert > Userform to add a new Userform to the project.
From the toolbox, drag a Listbox control onto the newly created Userform.
Also from the toolbox, drag two CommandButton control onto the Userform. You can name them btnPageUp and btnPageDown.
Move your code that populates your current Listbox into the Userform_Initialize event of the Userform.

Open the VBA Editor.

Go to Insert > UserForm to add a new UserForm to their project.

From the Toolbox (if not visible, go to View > Toolbox), drag a ListBox control onto the newly created UserForm.

Also, from the Toolbox, drag two CommandButton controls onto the UserForm. You can name them something like btnPageUp and btnPageDown.

Move the code that populates your ListBox into the UserForm_Initialize event of the UserForm. For example

Add the Page UP/PageDown code to the Commandbuttons on the Userform.

Private Sub btnPageUp_Click()
    Dim lngPageSize As Long
    lngPageSize = 10 ' Or calculate the visible items: Me.ListBox1.ListRows
    If Me.ListBox1.TopIndex - lngPageSize >= 0 Then
        Me.ListBox1.TopIndex = Me.ListBox1.TopIndex - lngPageSize
    Else
        Me.ListBox1.TopIndex = 0 ' Go to the very top
    End If
End Sub

Private Sub btnPageDown_Click()
    Dim lngPageSize As Long
    Dim lngMaxTopIndex As Long
    lngPageSize = 10 ' Or calculate the visible items: Me.ListBox1.ListRows
    ' Calculate the maximum possible TopIndex
    ' This prevents scrolling past the last item
    ' ListCount - lngPageSize is often a good starting point
    ' Adjust based on whether ListRows accurately reflects visible items
    lngMaxTopIndex = Me.ListBox1.ListCount - Me.ListBox1.ListRows
    If lngMaxTopIndex < 0 Then lngMaxTopIndex = 0 ' Handle cases with fewer items than a page
    If Me.ListBox1.TopIndex + lngPageSize <= lngMaxTopIndex Then
        Me.ListBox1.TopIndex = Me.ListBox1.TopIndex + lngPageSize
    Else
        Me.ListBox1.TopIndex = lngMaxTopIndex ' Go to the very bottom
    End If
End Sub

Finally , from a regular module (or a button on your worksheet), you can show the Userform

Sub ShowwMyListboxGorm()
    Userform1.Show ' <=== Change this to the name of your Userform
End Sub

This approach will be much more robust, faster, and give you precise control over the paging without relying on SendKeys.