Help with Hittest in Listview

[KenMillard]

Excel 2003

I have a macro that uses the hittest method to return an item from the listview. In order for this to work, I need to multiple the x and y values by a certain number. It used to be 15. After changing the resolution (and dpi) of my monitor, 15 no longer works.

I could figure out what the new number is easily enough, what I need to do is determine the correct number programmatically so it will work on multiple machines, regardless of resolution or screen dpi.

Is this possible?

Private Sub ListView_Operations_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
Dim CurrItem As ListItem
Dim PageCount As Long
If Me.ListView_Operations.ListItems.Count = 0 Then Exit Sub
Set CurrItem = ListView_Operations.HitTest(x * 15, y * 15)
PageCount = CurrItem.Tag
Select Case x
    Case 275 To 360 ' From Page Column
 
        If Button = 2 Then ' right click
            If CLng(CurrItem.ListSubItems(1)) > 1 Then
                CurrItem.ListSubItems(1).Text = CurrItem.ListSubItems(1) - 1
            End If
        ElseIf Button = 1 Then ' left click
            If CLng(CurrItem.ListSubItems(1)) < CLng(CurrItem.ListSubItems(2)) Then
                CurrItem.ListSubItems(1).Text = CurrItem.ListSubItems(1) + 1
            End If
        End If
    Case Is > 370 'To Page Column
        If Button = 2 Then ' right click
            If CLng(CurrItem.ListSubItems(2)) > CLng(CurrItem.ListSubItems(1)) Then
                CurrItem.ListSubItems(2).Text = CurrItem.ListSubItems(2) - 1
            End If
        ElseIf Button = 1 Then ' left click
            If CLng(CurrItem.ListSubItems(2)) < PageCount Then
                CurrItem.ListSubItems(2).Text = CurrItem.ListSubItems(2) + 1
            End If
        End If
    End Select
 
End Sub

Once I figure out how to determine the correct multiplier for the hittest, I'll need to change how I determine which column was clicked.

If it helps, the purpose of this is to set the from and to page when printing inspection reports. The list view displays each operation as a list item with a from page column and a to page column. Right clicking decreases the page numbers and left clicking increases the page numbers. If there's a better way to do this, let me know.

Thanks

Ken

 

[KenMillard]

Well...

It looks like I may have finally worked out a solution. It's kinda ugly but it seems to work. I found some information regarding the api call to get the screen dpi at : http://www.informit.com/content/images/0321262506/samplechapter/bullen_ch09.pdf


I put the following code in a public module

Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long
Private Const LOGPIXELSX = 88 'Pixels/inch in X
'A point is defined as 1/72 inches
Private Const POINTS_PER_INCH As Long = 72
'The size of a pixel, in points
Public Function PointsPerPixel() As Double
Dim hDC As Long
Dim lDotsPerInch As Long
hDC = GetDC(0)
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
ReleaseDC 0, hDC
End Function

That function returns the points per pixel. Multiplying this by 20 seems to work no matter what the dpi is. Not sure if the 20 works because it has something to do with twips or if it's just coincidental. Next time I upgrade my computer, it will probably break again.