none
How can I make the dropdown list of a combobox invisible? RRS feed

  • Question

  • Hello,

    I have a combo box which is populated from an array. Once it is populated, I want its dropdown list to appear automatically (instead of having to click on the combo arrow to make it appear).

    This is how I do it:

    Combo1.Dropdown

    But in some circumstances, the dropdown list is a nuisance and pops up in an unexpected place (i.e not directly below the combo).

    Is there a VBA (Excel) command that does the opposite, namely to make the dropdown disappear?

    I found that if I click anywhere on the form, the dropdown list disappears, but I could not produce this result with Sendkeys.

    Thanks for any ideas.

    Leon

    Friday, December 6, 2019 8:40 AM

Answers

  • I'm not sure what SendKeys you tried but this works for me -

    'userform code
    Private Sub UserForm_Click()
        Me.ComboBox1.DropDown
        Application.OnTime Now + TimeSerial(0, 0, 4), "SKEsc"
    End Sub
    
    ' normal module code
    Sub SKEsc()
        SendKeys "{ESC}"
    End Sub
    

    The userform would need still to be the active course to receive SendKeys

    • Marked as answer by Leon Lai Friday, December 6, 2019 10:30 AM
    Friday, December 6, 2019 9:39 AM
    Moderator

All replies

  • I'm not sure what SendKeys you tried but this works for me -

    'userform code
    Private Sub UserForm_Click()
        Me.ComboBox1.DropDown
        Application.OnTime Now + TimeSerial(0, 0, 4), "SKEsc"
    End Sub
    
    ' normal module code
    Sub SKEsc()
        SendKeys "{ESC}"
    End Sub
    

    The userform would need still to be the active course to receive SendKeys

    • Marked as answer by Leon Lai Friday, December 6, 2019 10:30 AM
    Friday, December 6, 2019 9:39 AM
    Moderator
  • Hi, Peter Thornton,

    Your code works fine for me!


    Private Sub opt_Store_Change()
    Call ExcelRangeToArray
    Application.OnTime Now + TimeSerial(0, 0, 4), "SKEsc"
    End Sub

    But just an additional question (wishful thinking  :)

    Instead of hardcoding 4 seconds, is it possible to run SKEsc just when  ExcelRangeToArray finishes to run?

    This would avoid setting a time delay which is too short or too long, because ExcelRangeToArray may take more or less time to finish depending on how big the array is.

    Best Regards,

    Leon

    ----

    Added: I found:                                                                                                          Application.OnTime Now + TimeSerial(0, 0, 0), "SKEsc"   

    I am not sure I am right, but by forcing the program to run SKEsc, we are ensuring the preceding Sub has completed before we run Sendkeys. So, even if we put 0 i/o 4, that does not matter.

     Thanks for your great and useful tip!





    • Edited by Leon Lai Friday, December 6, 2019 11:11 AM
    Friday, December 6, 2019 10:30 AM
  • Normally the onTime macro will not run until all other code has completed, and instead of +TimeSerial(0,0,0) simply 'Now' should work. 

    I only used the OnTime macro for testing, maybe you could call the SendKeys when your code completes, no need for it to be in its own Sub routine.

    I'm not sure what your ExcelToRangeArray is but if it's Arr=rng.Value with a contiguous range, unless a very large range that should be pretty quick.

    Friday, December 6, 2019 12:49 PM
    Moderator