0
Thanks

A few words of thanks would be greatly appreciated.

VBA / VB6 - Force a ComboBox to open


Excel when combined with VBA, forms a powerful tool to create customized and automated data analysis solutions. VBA has built-in controls like ComboBox which allow users to select from an already populated list or supply their own input. One can link two ComboBoxes by using the SetFocus method in a custom code. After that, the user can run another code which uses the keycode returning from one ComboBox to initialise the other one. This feature helps the programmer to set up a code in such a way that once a selection is made in ComboBox1, it causes the second ComboBox to open automatically.


To force a ComboBox to open there is the following function ...
ComboBox1.DropDown


But...for that we have the ComboBox focus, and if the command from another control it will not open.

To open another ComboBox you should use a trick here:
  • Place 2 ComboBox on a worksheet or a UserForm and name them respectively as ComboBox1 and CombBox2
  • Use the following code to link the two ComboBox:


Private Sub ComboBox1_Change()       
'========== Combo sur une feuille =========    1       
    'ComboBox2.Activate       
'========== Combo sur un UserForm ou VB6 ========= 2       
    'ComboBox2.SetFocus       
'==========================================       
    SendKeys "^(F4)"       
End Sub       
        
Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)       
    If KeyCode = 16 Then       
        ComboBox2.DropDown       
    End If       
End Sub      



By clearing line 1 or 2 depending on the location of the ComboBox.
Result: when you make a selection on a ComboBox 1, the second one opens automatically.

Download sample for Excel 97 - 2003

0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

0 Comments