VBA Userform Listbox not scrolling down to the last item

[RawlinsCross]

Hi folks,

I've research this up the ying-yang and have tried most everything I've found, but still can't seem to fix this problem. When I add items to a userform and add enough so that the scroll bar appears, I can't scroll down and see the last item (or scroll down to the bottom and the bar bounces back up a notch).

Has there been anything added to this solution recently? I've googled it and I get the following strategies.
1. Set .IntegralHeight to False and then to True
2. Reset the .height property in the code
3. Toggle the .MultiSelect property to Single and then to Extended (or to Multi)

Anyhow, none of these seems to have worked for me. Any new advances?

 

[Legacy 456155]

IntegralHeight was what first came to my mind, but you got that covered. What about adding a blank item at the bottom?

 

[RawlinsCross]

Oh hey Tom... yeah, not so crazy about that idea as it seems klunky - but maybe that's what I'll have to do!

 

[RawlinsCross]

Gave it a go - adding a blank works, and works better than I thought. Had to make sure when I added a blank row, that if I add any more real items, I have to remove that blank row first, enter the new item, then enter another blank row. So yes, a tad klunky but effective.

 

[Jaafar Tribak]

I remember experiencing the same issue and I solved it by adjusting the height of the Listbox via the IAccessibility as follows :

The SetIntegralHeight is a custom boolean Property that is supposed to imitate the ListBox native IntegralHeight Property when the latter refuses to work.

Workbook Demo

Option Explicit

Private Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    iData4(0 To 7) As Byte
End Type

#If VBA7 Then
    Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "OLEACC.DLL" (ByVal hwnd As LongPtr, ByVal dwId As Long, ByVal riid As LongPtr, ppvObject As Any) As Long
    Private Declare PtrSafe Function IIDFromString Lib "ole32.dll" (ByVal lpsz As LongPtr, ByVal lpiid As LongPtr) As LongPtr
#Else
    Private Declare Function AccessibleObjectFromWindow Lib "OLEACC.DLL" (ByVal hwnd As Long, ByVal dwId As Long, ByVal riid As Long, ppvObject As Any) As Long
    Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As Long, ByVal lpiid As Long) As Long
#End If



Private Sub UserForm_Initialize()

    Dim i As Long
    
    For i = 1 To 100
        ListBox1.AddItem i
    Next i
    
    SetIntegralHeight(ListBox1) = True

End Sub



Private Property Let SetIntegralHeight(ByVal ListBox As Control, ByVal PropBool As Boolean)

    Const ID_ACCESSIBLE As String = "{618736E0-3C3D-11CF-810C-00AA00389B71}"
    Const OBJID_CLIENT = &HFFFFFFFC
    Const S_OK = &H0&
    
    Dim tGUID(0 To 3) As Long, oAccWindow As IAccessible, oAccClient  As IAccessible
    Dim lListHeight As Long, lItemHeight As Long, lStandardItemHeight As Long, i As Long
    
    If Len(ListBox.Tag) = 0 Then
        ListBox.Tag = ListBox.Height
    End If
    
    If PropBool Then
        Set oAccClient = ListBox
        Set oAccWindow = ListBox
        If IIDFromString(StrPtr(ID_ACCESSIBLE), VarPtr(tGUID(0))) = S_OK Then
            If AccessibleObjectFromWindow(ListBox.[_GethWnd], OBJID_CLIENT, VarPtr(tGUID(0)), oAccClient) = S_OK Then
                oAccClient.accLocation 0&, 0&, 0&, lStandardItemHeight, 1&
                Do
                    oAccWindow.accLocation 0&, 0&, 0&, lListHeight, 0&
                    ListBox.Height = ListBox.Height - 1
                    DoEvents
                Loop Until lListHeight Mod lStandardItemHeight = 0
                ListBox.Height = ListBox.Height + lStandardItemHeight
            End If
        End If
    Else
        ListBox.Height = ListBox.Tag
    End If

End Property