VBA Listview Horizontal ScrollBar Position

Question

I'm hoping that there is a simple answer to this question - but my googling only resulted in C# and VB.net answers which I've failed to translate into straight VBA.

I've got a listview in a userform filled with data in the report format (looks kinda like an Excel sheet). The data is bigger than listview window and so horizontal and vertical scrollbars are required.

I want to be able to perform functions with particular cells in the listview. To do this, I'm using the mouse down event and then checking to see where the user clicked and returning the cell value. I'm using Listview1.HitTest to get the chosen row - but the chosen column is a bit trickier.

The following code captures the column chosen when the horizontal scroll position is the far left:

Private Function GetSelectedCol(listview As Object, x As stdole.OLE_XPOS_PIXELS, lngXPixelsPerInch As Long)
    Dim col As Variant
    Dim colX As Long
    colX = 0
    Dim offset As Long
    offset = GotHorizontalScrollPosition()
    For Each col In listview.columnHeaders
        colX = colX + col.Width * 2
        If x + offset <= colX Then
            GetSelectedCol = col.index - 1
            Exit Function
        End If
    Next col
End Function

The problem is currently my GotHorizontalScrollPosition() is currently a dummy function which returns 0. I've implementing this solution but couldn't get it to work and suspect that it might not work in VBA (or at least is very much beyond my skills).

Is there any VBA solutions to getting the horizontal scrollbar position of the listview?

Or alternatively, is there a different way of getting the column clicked on?

Answer 1

For the life of me, I couldn't find this answer by googling - but I managed to find it using ChatGPT. For once it didn't just make up code!

Public Declare PtrSafe Function GetScrollPos Lib "user32" (ByVal Hwnd As LongPtr, ByVal nBar As Long) As Long

Private Function GetSelectedCol(listview As Object, x As stdole.OLE_XPOS_PIXELS, lngXPixelsPerInch As Long)
    Dim correctedX As Long
    correctedX = x + GetScrollPos(Me.listview.Hwnd, 0)
    
    Dim colX As Long
    colX = 0

    Dim col As Variant
    For Each col In listview.columnHeaders
        colX = colX + col.Width * 2
        If correctedX <= colX Then
            GetSelectedCol = col.index - 1
            Exit Function
        End If
    Next col
End Function