VBA Excel Combobox: drop-down list scrolling issue

Question

I am running 32-bit Excel 2010. I have created multiple ActiveX Control combo boxes and they all have numbers of entries in their drop-down lists. The thing is that instead of using mouse click to scroll the list, I want to use the mouse scroll to scroll the list, but it actually doesn't work. When I scroll inside of the list, it scrolls the whole list down instead of the content in it. So does anyone know how to add this feature to it?

Solution

I used this method to stop the list detaching from the combo box and moving down the sheet with the mouse scroll. It actually disables the mouse scroll, but you can still move the mouse to select an item, and operaton the right scroll bar if it appears.

1. Select the row(s) where you have placed the ActiveX combo Box and the sheet
2. Type a named range in the Formula Bar, and press enter. eg: "rngJobRoleCombo"
3. Right click on the control in Development mode, and select "View Code"

4. Select the control's GotFocus event

   Private Sub cboJobRole_GotFocus()
       Me.ScrollArea = Range("rngJobRoleCombos").Address  
   End Sub
   

5. Select the controls LostFocus event

   Private Sub cboJobRole_LostFocus()
       Me.ScrollArea = ""
   End Sub
   

This limits the mouse scroll to the cell range address of the worksheet while the control is in focus.

Comments

[Chris]

Hi Neil, this is almost what I am looking for. I too am using an active X combobox. I tried to use the code you listed and it prevents anything from scrolling while the combobox is selected. It's nice that the page doesn't scroll while on the combobox, but the combobox will still not scroll through the list either. One of my boxes is called cboSites, so where you have cboJobRole in the Private Sub part, I just replaced it with cboSites. Is there something else that I need to do?

[Chris]

I think the problem with the code is you are specifying the scroll area to be on a row. Which the ComboBox is on a row, but not part of the row or affiliated with the row in anyway. If the combobox was actually a dropdown in a cell, then I think this would work, but since it is an object that is moveable and can be placed anywhere, the defined "ScrollArea" doesn't associate with what to scroll on. That is my logic, but if it truly works for you then please let me know what I am doing wrong.

[Mike PG]

Thumbs up from me. It doesn't address the issue of scrolling the combo box, but it does solve the user experience problem associated with being unable to scroll. (assuming you are VERY careful about unlocking the scrollarea! hah. That would cause some problems)