Mouse down event to select item in combo box not working correctly

Mike French
Mike French used Ask the Experts™
on
I am using an ActiveX combo box on a worksheet. I am trying to select an item with a mouse. I have found the code below and it does work. However, unless you place the mouse pointer on the very bottom of the item highlighted in blue, it will select the item above it. Is there another way to do this? or is there a way to adjust the code to make this function better?

Private Sub TempCombo_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
  
   If TempCombo.TopIndex > -1 Then
        Dim curIndex As Integer
        curIndex = TempCombo.TopIndex + Application.WorksheetFunction.RoundDown(y / 13.5, 0)
        CurValue = TempCombo.list(curIndex)
   End If
  
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This will select the next entry in the combobox.

Private Sub TempCombo_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)

    With TempCombo
        If .ListIndex > -1 Then
            .ListIndex = .ListIndex + Application.WorksheetFunction.RoundDown(y / 13.5, 0)
        End If
   End With
  
End Sub

Open in new window

Mike FrenchMississippi Regional Manager

Author

Commented:
Well, I tried using your solution but I get no value whatsoever into my variable. See the code below. You essentially changed the "TopIndex" for "ListIndex". What am I missing here?

    If TempCombo.ListIndex > -1 Then
        Dim CurIndex As Integer
        CurIndex = TempCombo.ListIndex + Application.WorksheetFunction.RoundDown(y / 13.5, 0)
        CurValue = TempCombo.list(CurIndex)
    End If

Open in new window

Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
That looks like your original code rather than what I posted in my previous post. Did you actually use my code?
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Mississippi Regional Manager
Commented:
Martin,

Thanks for your help here. Interestingly enough (and I don't know why this would matter), when I initialized the combo box setting it to the target cell, I was setting the .LinkedCell to the target.address. Removing that code made the mouse click select the product. The other thing was that I had my "listrows" set to 12. Although it worked, for some reason the further down the list  the selection was to the bottom of the list, the lower the mouse pointer had to be or it would select the item above it. With the top item in the list, the pointer could be at the top of highlighted line and it would work. The 12th item in the list would not select properly unless you had the tip of the mouse on the very bottom of the blue line. I solved this by changing the code from "Rounddown" to just "Round" at it works flawlessly.

Again, that for your help!
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Just for my benefit, does this work the way you want?
29167723.xlsm
Mike FrenchMississippi Regional Manager

Author

Commented:
Martin,

I tried your worksheet and I can make it work as well!

Thanks,
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
"make it work" implies to me that you had to change the code. Is that correct?
Mike FrenchMississippi Regional Manager

Author

Commented:
I had to put the "On error resume next" into it because i kept getting errors when i hovered over it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial