Drop Combo Box

Screen shot

This is the Event tab of the Property Sheet for a bound combo box. As a general rule, it is a good idea to call code from VBA instead of putting it on the property sheet. I make an exception to this for combo boxes since Access requires you to click on that tiny arrow to drop a list, or use the keyboard (Alt-DownArrow or F4). So I generally call these functions for every combo box except if the combo is the first tab stop -- in that case, I only use Mouse Up and not Got Focus.

On Got Focus: for bound controls, it is nice to drop the list automatically when the control gets the focus if there is no value yet.

On Mouse Up: if the user clicks in the control anywhere, the list will be dropped even if it already has a value. This makes the target much bigger, so it takes less time to click and drop the list.

Logic

This code works on the active control. If it is not a combo box, there will be an error because only combos have a DropDown method.

Screen.ActiveControl.Dropdown drops the list for the active control on the screen, regardless of where it it is positioned on forms and subforms.

These two "subs" are defined as functions so they can be called from the property sheet. This is usually the only time I use Byte (it is small!) for a return value data type ... to indicate that I don't want the return value; just defining a Function since a Sub can't be referenced on the property sheet.

Code

'*************** Code Start *****************************************************
' Purpose  : Drop the list for a combo box.
' Author   : crystal (strive4peace)
' Return   : Byte (not used)
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'-------------------------------------------------------------------------------
'                              DropMe'-------------------------------------------------------------------------------
' 
Function DropMe( _
   ) As Byte
' crystal (strive4peace)
   'usually used on the MouseUp event of a Combo Box
   'so you can click anywhere and drop the list
   'instead of just on the arrow.
   'On Property Sheet:
   '=DropMe())
   
   On Error Resume Next
   Screen.ActiveControl.Dropdown

End Function

'-------------------------------------------------------------------------------
'                              DropMeIfNull'-------------------------------------------------------------------------------
' 
Function DropMeIfNull( _
   ) As Byte

' crystal (strive4peace)

   'usually used on the GotFocus event of a Combo Box
   'so if there is nothing filled out yet, the list will drop
   'Do NOT use on the first control in the tab order
   'because the list might not appear where you expect it to be.
   'On Property Sheet:
   '=DropMeIfNull()
   
   On Error Resume Next
   With Screen.ActiveControl
      If IsNull(.Value) Then .Dropdown
   End With
   
End Function   
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

VBA > Control > Drop list

Screen shot

Examples

=DropMe()

=DropMeIfNull()

Call DropMe()

Logic

Code

' DropMe

' DropMeIfNull

Share

Reference