Version 1.51           Last Updated 20 July 2022                 Approx 0.5 MB


The ideas for this article were based on two threads started by Access World Forums member GUIDO22:
      •   Listbox Adjust       Question - is multiline possible?       Answer - NO
      •   Listbox Item Tooltip       Question - is a tooltip possible for individual listbox items?       Answer - NO

This article and the attached database shows what might be a good work round for each request by GUIDO22
It is based on the ideas discussed in detail in my earlier article: Accurately Move Forms & Controls

As the mouse is moved over each listbox item the associated image and 'tooltip' are displayed.

NOTE:
It isn’t necessary to click the item to select it.
Code is used to identify the position of the mouse cursor over the listbox and identify the listbox item under the mouse cursor.


The tooltips can be multi-line and optionally use rich text. Both of those are impossible to do directly in the listbox itself

The mouse position on the screen is determined with reference to a GetListboxRowHeight function.
That calculates the exact height of each listbox row depending on the font name, style and size.
The code makes use of an undocumented and hidden VBA function called Wizhook.

See my article:Accurately Move Forms & Controls for further details.

As a result, the correct listbox item is still identified even if the various listbox options are changed:
      •   columns headers on /off
      •   change font name
      •   change font size
      •   italic on / off

For example:


The original version of this app had one important limitation:
The original mouse move code only worked if all listbox items were visible.
If the vertical scrollbar was needed to reach a listbox item, you had to click the item to physically select it

However, this issue has been fixed in the latest version.


A thread started by AccessForums.net member Ron Mittleman appeared to suggest a useful approach:
      •   GET/SET Subform Horizontal Scrollbar VBA

NOTE: See my related article: Synchronise Subform Scrolling for a no code solution to the ideas raised in that thread

The code developed later in that thread was based on an updated version of the Set Get Scrollbar Position code originally written for Access 97 by Access guru Stephen Lebans.

Various modifications were made by Ron Mittleman and myself so that it worked in both 32-bit & 64-bit ACCDB files.
Unfortunately, I was unable to adapt the Get Set Scrollbar Position code to work with a listbox scrollbar.

In the end, the code I used a significantly modified version of code from another example app Listbox Auto1 by Stephen Lebans

Inevitably, the scrollbar code is not as precise at locating the listbox row under the mouse cursor.

However, in my opinion, the functionality is still good enough to allow a scan through all listbox items without needing to physically select an item first

Do try it and let me know what you think! Even better, if you can improve the accuracy of the listbox scroll mouse move code, do let me know!

NOTE: As with all code involving mouse move events, there is some flickering as the mouse is moved.

The code used for the listbox mouse move event and the GetListboxRowHeight function are as follows:

CODE:

Option Compare Database
Option Explicit

Dim LBRH As Long       'listbox row height
Dim n As Integer
Dim intColumnHeads As Integer       'listbox header row
Dim blnVertScroll As Boolean       'vertical scrollbar visible
Dim OldListRow As Integer       'previous listbox row
Dim ListRow As Integer       'current listbox row

'===============================================

Private Sub lstImages_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

'===============================================
'Purpose :       Get listbox row data by moving over record
'Author :       Colin Riddington
'Date :       30 June 2022
'Company :       Mendip Data Systems
'Web :       https://isladogs.co.uk
'Copyright :       May be used in your own applications provided this copyright info remains in place & unaltered

'This function works even for a listbox that DOES have a vertical scroll bar.

'Original code by Colin Riddington - 27 June 2019
'First used in https://www.isladogs.co.uk/move-forms-controls/index.html
'minimal flicker / very accurate BUT that code didn't work if scrollbar used

'Code for use with scrollbar adapted from code by Stephen Lebans
'See https://lebans.com/listboxauto1.htm
'This is much less accurate & has more flickering

'The column the listbox is bound to must not contain duplicate
'values or it won't be able to select the proper row!

'===============================================

On Error Resume Next

'fail safe
If Screen.ActiveControl <> lstImages Then Exit Sub

Dim intTotalRows As Integer
Dim intTotalDisplayedRows As Integer

'check for listbox data
If Me.lstImages.ListCount = 0 Then Exit Sub

'are column headers visible?
'-1 if true, 0 if false so so subtracting it adds 1 if headers shown
intColumnHeads = Me.lstImages.ColumnHeads

'number of visible rows
intTotalDisplayedRows = (Me.lstImages.Height \ LBRH) + intColumnHeads       '-1 if headers on

'total rows
intTotalRows = Me.lstImages.ListCount + intColumnHeads       '-1 if headers on
'Debug.Print intTotalDisplayedRows, intTotalRows

'Is vertical scrollbar visible?
blnVertScroll = intTotalRows > intTotalDisplayedRows
'Debug.Print "Scrollbar " & blnVertScroll

'To avoid distraction, no listbox tooltip if control has focus
Me.lstImages.ControlTipText = ""

Screen.MousePointer = 1       'arrow - reduces flicker

If blnVertScroll Then       'vertical scrollbar visible
      'this section adapted from code by Stephen Lebans
      'all rows can be 'selected' by mouse move though cursor doesn't align perfectly with highlighted row

      '***START PROCESSING***
      'Check incoming mouse x,y values to make sure
      'the numbers are clipped within our established bounds
      If Y < 0 Then Y = 0

      'Calculate which row we are on (approximately)
      ListRow = (intTotalRows / intTotalDisplayedRows) * Y \ LBRH

      'safety check
      If ListRow > intTotalRows Then ListRow = intTotalRows

      If ListRow <> OldListRow Then       'cursor has moved to new row
            Me.lstImages.Selected(ListRow - 1 - intColumnHeads) = True       'highlight on mouse move

            'Reset previous row counter to prevent mouse move flicker whilst on same row
            OldListRow = ListRow
      End If

Else       'no scrollbar visible
            'based on code by Colin Riddington using Wizhook function to get listbox row height (LBRH)
      'always accurate

      ListRow = ((Y - 45) \ LBRH) + 1 + intColumnHeads

      If ListRow <> OldListRow Then       'cursor has moved to new row
            Me.lstImages.Selected(ListRow - 1 - intColumnHeads) = True       'highlight on mouse move

            'Reset previous row counter to prevent mouse move flicker whilst on same row
            OldListRow = ListRow
      End If
End If

'now we know the highlighted row so...
Me.lblFileName.Caption = Nz(Me.lstImages.Column(1) & "." & Me.lstImages.Column(2), "")
Me.Image1.Picture = Nz(Me.lstImages.Column(1), "")

'show image & tooltip if in list
If ListRow > 0 And ListRow <= intTotalRows Then
      Me.lblImage.Visible = True
      Me.Image1.Visible = True
      Me.lblFileName.Visible = True
      Me.txtTip.Visible = True
      Me.txtTip = Nz(DLookup("Tooltip", "tblImages", "ID=" & ListRow), "")
Else
      Me.lblImage.Visible = False
      Me.Image1.Visible = False
      Me.lblFileName.Visible = False
      Me.txtTip.Visible = False
     'deselect all records
     For n = 1 To Me.lstImages.ListCount
           Me.lstImages.Selected(n) = False
      Next
End If

Screen.MousePointer = 0       'restore default cursor

End Sub

'===============================================

Private Function GetListboxRowHeight()

'Colin Riddington - 27 June 2019
'Wizhook calculates the listbox row height perfectly

WizHook.Key = 51488399

Dim lx As Long, ly As Long, Dim LBRH As Integer

With Me.lstImages
      If WizHook.TwipsFromFont(.FontName, .FontSize, .FontWeight, .FontItalic, .FontUnderline, 0, "ABCghj", 0, lx, ly) = True Then
            LBRH = ly + 15       'font height +15 twips (1px space between rows)
      End If
End With

End Function

Click to download the example apps:

Latest version (with scrollbar functionality):               Listbox Tooltip v1.51     Approx 1.1 MB     (zipped)

Previous version (without scrollbar functionality):      Listbox Tooltip v1.2     Approx 1.1 MB     (zipped)


Click below to view a short video (with no audio) demonstrating the example app:

       

A more detailed video explaining how this example app works will be made available on YouTube in the near future.



UPDATE: 16 July 2022
An alternative approach which has similar results is included in another example app on this website.
That example uses the vertical scrollbar to 'select' records in a continuous form without clicking.
      Continuous subform selection using the vertical scrollbar

That approach does NOT involve mouse move events. It is particularly suitable for fast scrolling through a continuous subform containing many records


UPDATE: 20 July 2022
Version 1.51 - Fixed error in lstImages_MouseMove event - thanks to Chris Arnold for letting me know



Colin Riddington           Mendip Data Systems                 Last Updated 20 July 2022

Return to Example Databases Page

Return to Top