VBA Excel Combobox: drop-down list scrolling issue

问题:

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?

回答1:

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.