Mousehover in listview VBA

Question

i want to select the listitem by hovering the mouse not by clicking.. how to achieve in vba..

i saw a code in a forum using vb.net

Private Sub ListView1_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseMove
    Dim itm As ListViewItem
    itm = Me.ListView1.GetItemAt(e.X, e.Y)
    If Not itm Is Nothing Then
        MessageBox.Show(itm.Text)
    End If
    itm = Nothing
End Sub

i have this also.. but this doesnt go to other row item.. always selecting the first item.

Private Sub ListView1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
    Dim itm As ListItem
    Me.ListView1.MultiSelect = False
    
    Set itm = Me.ListView1.HitTest(x, y)
    If Not itm Is Nothing Then
        itm.Selected = True
    End If
End Sub

Answer 1

As I said in my comment, it is a matter of conversion between what Excel unit 'offers' (pixels) and what a list view needs (twips). The working solution will be the next:

1. Please, copy the next API functions on top of the form code module (in the declarations area):

Option Explicit

Private Declare PtrSafe Function GetDC Lib "user32" _
                           (ByVal hwnd As Long) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" _
            (ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function ReleaseDC Lib "user32" _
                        (ByVal hwnd As Long, ByVal hDC As Long) As Long

2. Use the next modified event:

Private Sub ListView1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
                ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
  Dim itm As MSComctlLib.ListItem
    Me.ListView1.SelectedItem.Selected = False ' unselect a previous selected subitem
    
    ConvertPixelsToTwips x, y         'make the necessary units conversion
    Set itm = ListView1.HitTest(x, y) 'set the object using the converted coordinates
    If Not itm Is Nothing Then
        itm.Selected = True
    End If
End Sub

3. Copy the next function, too:

Private Sub ConvertPixelsToTwips(ByRef x As stdole.OLE_XPOS_PIXELS, _
                                     ByRef y As stdole.OLE_YPOS_PIXELS)
    Dim hDC As Long, RetVal As Long, TwipsPerPixelX As Long, TwipsPerPixelY As Long
    Const LOGPIXELSX = 88
    Const LOGPIXELSY = 90
    Const TWIPSPERINCH = 1440
 
    hDC = GetDC(0)
    TwipsPerPixelX = TWIPSPERINCH / GetDeviceCaps(hDC, LOGPIXELSX)
    TwipsPerPixelY = TWIPSPERINCH / GetDeviceCaps(hDC, LOGPIXELSY)
    RetVal = ReleaseDC(0, hDC)
    x = x * TwipsPerPixelX:  y = y * TwipsPerPixelY    
End Sub

I am not the 'father' of the above function. I found the bases on the internet, some years before. I remember that I modified something, but I do not remember what...

Please, try the proposed solution and send some feedback.