Home > Blockchain >  ComboBox open on mouse over event and close after mouse moves off it - in Excel VBA
ComboBox open on mouse over event and close after mouse moves off it - in Excel VBA

Time:07-08

Idea is to have a ComboBox open - when mouse cursor moves over it, without having to click on it, and when mouse moves off it for it to close back down.

Code below seem to be opening it, but with caveats:

a) code keeps firing the Alt Arrow Down while you are thinking what list item to pick - want it to do it just to open the ComboBox and then stop,

b) once you pick the item, it fires Alt Arrow Down on a new active cell, which is not the intent. And that new cell prevents ComboBox from opening on the next mouse over. (*I also have another code, which once list item is picked is finding and selecting corresponding cell in A column)

Private Sub ComboBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ComboBox1.Activate
    SendKeys "%{DOWN}"
End Sub

CodePudding user response:

Some users might find move over solution annoying -- but would be interesting to test it out, if it comes along.

Went with a click anywhere on the Box meantime, as it is better than having to click on an arrow icon only.

Private Sub ComboBox1_GotFocus() 'when clicked on anywhere - not just arrow icon, opens
    SendKeys "%{DOWN}"
End Sub

CodePudding user response:

on your form create TextBox1 and then create ListBox1 on top of that starting at the same x location with the top offset higher by 5 or so points to eliminate flicker. Set the List Box visible value to False. Paste this example code into your form

Private Sub ListBox1_Click()
        TextBox1.Text = ListBox1.Text
End Sub

Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        With ListBox1
                If X < 4 Or X > .Width - 6 Then
                        .Visible = False
                        DoEvents
                        TextBox1.visible = true
                 End If
                If Y < 4 Or Y > .Height - 6 Then
                        .Visible = False
                        DoEvents
                        TextBox1.visible = true
                End If
        End With
End Sub

Private Sub TextBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        With ListBox1
                .Visible = True
                TextBox1.visible = false
                .SetFocus
        End With
End Sub

Private Sub UserForm_Initialize()
        ListBox1.List = Split("A,B,C,D,E", ",")
End Sub

This works pretty well, but sometimes you have to move the mouse back over the list to hide it.

  • Related