Results 1 to 3 of 3

Closing activeX combobox dropdown after running macro

Threaded View

  1. #1
    Registered User
    Join Date
    01-21-2021
    Location
    Manchester
    MS-Off Ver
    365
    Posts
    3

    Closing activeX combobox dropdown after running macro

    I've tried everything.

    I have a combobox that is used to search a list of items and a macro that reads the value of the combobox and changes some things in the spreadsheet.
    The problem is, after the macro has finished and has cleared everything ready for the next input, the dropdown for the combobox opens again and will only close if you select the box again. It also stays in the same place on the screen if you move the window around or change sheets in the workbook.

    I've tried setting the value to null, setting the list width to itself, auto selecting a different cell, telling it to only open the dropdown if there is text in the box, changing the properties of the combobox, and a bunch of other stuff that didn't work. All the links are purple.

    The only two things that have "worked" are:

    Selecting the combobox at the end of my macro, which does get rid of the drop down but also breaks excel, so whenever you click on anything (cells, combobox) it copies? it but a few pixels down
    and i have to re start excel for it to work again.

    The other thing that works is removing .dropdown from the comboboxes code but this means that it wont show the search results unless you click on the dropdown arrow and that's not what I want.

    Any suggestions would be very much appreciated.

    Here's the main bit of code for the combobox search, the two quoted out .dropdowns seem to be causing the problem.

    Private Sub ComboBox2_Change()
    
        Dim i As Long
        Dim searcher As String
        Dim searchWords As Variant
        Dim element As Variant
        Dim regexOne As RegExp
        
    searcher = ComboBox2.Value
    searchWords = Split(searcher, " ")
    Set regexOne = New RegExp
    
    regexOne.Pattern = ""
    
        If Not IsArrow Then
           With Me.ComboBox2
                .List = Worksheets("Stock List").Range("A2", Worksheets("Stock List").Cells(Rows.Count, "A").End(xlUp)).Value
                .ListRows = Application.WorksheetFunction.Min(6, .ListCount)
                '.DropDown
            End With
    
    With regexOne
        .IgnoreCase = True
        .Global = True
    For m = LBound(searchWords) To UBound(searchWords) Step 1
        For i = Me.ComboBox2.ListCount - 1 To 0 Step -1
            .Pattern = searchWords(m)
            If .Test(Me.ComboBox2.List(i)) = False Then Me.ComboBox2.RemoveItem i
            Next
            Next
            'Me.ComboBox2.DropDown
    End With
    End If
               
    End Sub
    If it helps, I use:
    ThisWorkbook.Sheets("Main").ComboBox2.ListIndex = -1
    In my button click macro to clear the combobox, this also took me an age to find.
    Last edited by strnrrr; 02-21-2022 at 05:49 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] ActiveX combobox dropdown list not displayed when used in multiple columns
    By dentler in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2021, 10:31 PM
  2. [SOLVED] Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/
    By bongo_bg in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-25-2018, 02:03 AM
  3. running macro in another workbook on closing down
    By Andy Swain in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-10-2018, 08:37 AM
  4. ActiveX ComboBox DropDown
    By EssoExplJoe in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2017, 09:21 AM
  5. ActiveX Combobox dropdown 'reset' when repopulating
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-27-2017, 06:01 AM
  6. Macro assign to ActiveX Combobox
    By kangsongwu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2014, 02:56 AM
  7. [SOLVED] Running a macro Upon Closing
    By Confused Excel Fan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-29-2005, 08:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1