Page up Page down in a list box

[TonC]

Hello,
I've got a question about page up and page down in a listbox. I would like to use one button for page up en one button for page down. and when I press one of these 2 buttons,
the record which is selected must be highlighted in the listbox and in my combo box with input.
Note: all the records must be showed, so no filter.

Now I trying to this with selection boxes by the using following code:

Private Sub OptionButton112_Click()
    Me.Keuzelijst12.SetFocus
    If Me.OptionButton112.Value = True Then
       SendKeys "Fn+{PGDN}"
       SendKeys "Fn+{PGDN}"
       SendKeys "Fn+{PGDN}"
   End If
End Sub

This code set 3 pages down. But you understand if I have 10 pages in the listbox, I have to use 10 times SendKeys "Fn+{PGDN}" in my code. Its working nice but not practical.
I searched the internet, I could not find a topic about pDown and Pup in a listbox.
I hope you can help.

Its raining in the Netherlands, so this is a good day to solve this issue.

Greetings,
TonC

[Aussiebear]

Does this work for you?

' Assuming you have a UserForm named UserForm1 with a ListBox named ListBox1
' and two CommandButtons named btnPageUp and btnPageDown

Private Sub btnPageUp_Click()
    Dim lngPageSize As Long
    Dim lngNewTopIndex As Long
    ' Determine the page size (e.g., number of visible items)
    ' This might require some trial and error or calculation based on font size and listbox height
    ' For simplicity, let's assume a fixed page size for now    lngPageSize = 10 
    ' Adjust as needed
    ' Calculate the new TopIndex for Page Up
    lngNewTopIndex = Me.ListBox1.TopIndex - lngPageSize
    ' Ensure we don't go below 0
    If lngNewTopIndex < 0 Then
        lngNewTopIndex = 0
    End If
    ' Apply the new TopIndex
    Me.ListBox1.TopIndex = lngNewTopIndex
End Sub

Private Sub btnPageDown_Click()    Dim 
    lngPageSize As Long
    Dim lngNewTopIndex As Long
    Dim lngLastPossibleTopIndex As Long
    ' Determine the page size    lngPageSize = 10 
    ' Adjust as needed
    ' Calculate the new TopIndex for Page Down
    lngNewTopIndex = Me.ListBox1.TopIndex + lngPageSize
    ' Calculate the maximum possible TopIndex to avoid going past the end
    ' ListCount - 1 is the last item index
    ' Subtracting (lngPageSize - 1) helps ensure the last page shows full content if possible
    lngLastPossibleTopIndex = Me.ListBox1.ListCount - Me.ListBox1.ListRows 
    ' ListRows is often useful here
    If lngLastPossibleTopIndex < 0 Then 
        lngLastPossibleTopIndex = 0 
        ' Handle empty listbox
        If lngNewTopIndex > lngLastPossibleTopIndex Then
            lngNewTopIndex = lngLastPossibleTopIndex
        End If
    End If
    ' Apply the new TopIndex
    Me.ListBox1.TopIndex = lngNewTopIndex
End Sub

[TonC]

hello Aussiebear,
I used your code thanks in advance. I replaced the names from the listbox into my own name.
There was a error message "could not find method or member" it was by

.TopIndex

TonC

[TonC]

Hello Aussiebear,
Has this code anything to do with it ?

Private Sub Keuzelijst12_GotFocus()
    With Me.Keuzelijst12
        .ListIndex = 0
        .SetFocus
    End With
End Sub

[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.

[Gasman]

I took this to be an Access form?

Do yourself a favour. Give your controls meaningful names Control112 is not going to mean anything to anyone, a few months down the road.

ListIndex is -1 when nothing is selected. Make sure something is selected before calling this function.
Here is ChatGPT offering

Private Sub PageDownListBox(lst As ListBox)
    Dim pageSize As Integer
    Dim newIndex As Integer    
    ' Estimate the number of items visible at once (page size)
    pageSize = 10 ' Change this based on your ListBox height
    ' Calculate the new index
    newIndex = lst.ListIndex + pageSize
    ' Prevent overflow
    If newIndex >= lst.ListCount Then
        newIndex = lst.ListCount - 1
    End If
    ' Set new index
    If newIndex >= 0 Then
        lst.ListIndex = newIndex
    End If
End Sub

Private Sub btnPageDown_Click()
    PageDownListBox Me.lstMyList
End Sub

Private Sub PageUpListBox(lst As ListBox)
    Dim pageSize As Integer
    Dim newIndex As Integer
    pageSize = 10 ' Adjust as needed
    newIndex = lst.ListIndex - pageSize
    If newIndex < 0 Then
        newIndex = 0
    End If
    If lst.ListCount > 0 Then
        lst.ListIndex = newIndex
    End If
End Sub

[arnelgp]

you can also use API to scroll the listbox.