Make combobox on worksheet disappear after selection with a mouse click

Mike French
Mike French used Ask the Experts™
on
I have a combobox on a worksheet in Excel. I want the combobox to disappear after I make a selection with the mouse. I am using an activex control on a worksheet. it disappears if i select using the enter or tab key. How can I make it disappear after a mouse click. Tried every way i know.

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Roy CoxGroup Finance Manager

Commented:
This will do that, make sure the object's name is correct, the code should be in the worksheets module
Private Sub ComboBox1_Click()
ActiveSheet.ComboBox1.Visible = False
End Sub

Open in new window


What are you wanting to do this for?

I've added a workbook to demonstrate and one to show how to use similar code to hide and show a ListBox when cells are selected which might help, this example adds the selection to a cell comment.
HideComboxonSheet.xlsm
ListBox-toComment.xlsm
Mike FrenchMississippi Regional Manager

Author

Commented:
Roy,

I appreciate the solution but that won't work because the up and down arrow keys fire that event too. I have those keys disabled in the "key up" event code i am running.
Roy CoxGroup Finance Manager

Commented:
How do you want it to work, the up and down arrow keys just activate the click event
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Mike FrenchMississippi Regional Manager

Author

Commented:
Right now if a selection is made using the mouse the dropdown closes and the item selected is in Blue highlight. if I then hit the enter key the combobox becomes invisible. I have tried using the SendKeys command to simulate an enter or tab key but this doesn't work. if you arrow up or down to make a selection and then press the enter or tab key everything works fine. I just don't want to have to hit the enter key after making a selection with the mouse. If I put the code you suggested in the click event, it will fire, close and hide the combobox if someone is trying to use the arrow keys to make a selection because, the click event fires before any of the "Key" events.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please see this article of mine called The magical floating ActiveX control. In it are two combobox example that you might be able use to get some tips.
Roy CoxGroup Finance Manager

Commented:
Can you attach your workbook?
Mississippi Regional Manager
Commented:
Hey guys,

Thanks for your help. I found this code that captures the index value. Works perfectly now.

Private Sub ComboBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If ComboBox1.TopIndex > -1 Then
        Dim curIndex As Integer
        curIndex = ComboBox1.TopIndex + Application.WorksheetFunction.RoundDown(Y / 13.5, 0)
        curValue = ComboBox1.List(curIndex)
    End If
End Sub

Open in new window

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