Hi all,

I would like to use the following function to resize a listbox.

Is it possible that this can be modified to run in VBA?


Public Function AutoSizeLBHeight(LB As Object) As Boolean
'Source: http://www.freevbcode.com/ShowCode.Asp?ID=614
'PURPOSE: Will automatically set the height of a
'list box based on the number and height of entries

'PARAMETERS: LB = the ListBox control to autosize

'RETURNS: True if successful, false otherwise

'NOTE: LB's parent's (e.g., form, picturebox)
'scalemode must be vbTwips, which is the
'default

If Not TypeOf LB Is ListBox Then Exit Function


On Error GoTo ErrHandler

Dim lItemHeight As Long
Dim lRet As Long
Dim lItems As Long
Dim sngTwips As Single
Dim sngLBHeight As Single

If LB.ListCount = 0 Then
    LB.Height = 125
    AutoSizeLBHeight = True
    
Else
    lItems = LB.ListCount
    
    lItemHeight = SendMessage(LB.hwnd, LB_GETITEMHEIGHT, 0&, 0&)
    If lItemHeight > 0 Then
        sngTwips = lItemHeight * Screen.TwipsPerPixelY
        sngLBHeight = (sngTwips * lItems) + 125
        LB.Height = sngLBHeight
        AutoSizeLBHeight = True
    End If
End If

ErrHandler:
End Function
Code found at: http://www.freevbcode.com/ShowCode.Asp?ID=614
Any help is appreciated.

Thanks,

BDB