1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Activex ComboBox displaying dropdown sporadically

Discussion in 'VBA Macros' started by Shaveta, Sep 12, 2018.

  1. Shaveta

    Shaveta New Member

    Messages:
    4
    Hello there!
    Thanks for reading this.
    I am using activex comboboxes for the first time in my worksheet and I am facing a couple of issues with the same. I have attached screen shots to better explain the issue.
    1. I am using two combo boxes both having searchable dynamic lists. When I type anything in combo box 1, the list matching the text that I input appears in the drop down as below:

    upload_2018-9-12_16-28-18.png

    However, once I have selected an item in combo box 1, ideally it should remain passive until it's left idle. However, in my sheet when I try to fill anything in combo box 2, the drop down list in combo box 1 gets active and to extract drop down list in combo box 2 I have to first click on combo box 1. Then only I am able to get the drop down list for combo box 2 and that too only if I press the down arrow.


    upload_2018-9-12_16-33-16.png

    This issue is not limited to the combo box selection. Even if I press the buttons or switch the tab or do anything really, the drop down list (with only one item in drop down) keeps on occurring. I have no clue how to resolve this issue so that the sheet can become user friendly. I am using following code in the "view code" page of this sheet

    Code (vb):

    Private Sub ComboBox1_change()

    Dim sht1 As Worksheet
    Set sht1 = Worksheets("Entry Post")
    If ThisWorkbook.ActiveSheet.Name = sht1.Name Then

    ComboBox1.ListFillRange = "DynamicList1"
    Me.ComboBox1.DropDown
    Else
    Exit Sub
    End If


    End Sub

    Private Sub ComboBox2_change()

    Dim sht1 As Worksheet
    Set sht1 = Worksheets("Entry Post")
    If ThisWorkbook.ActiveSheet.Name = sht1.Name Then

    ComboBox2.ListFillRange = "DynamicList2"
    Me.ComboBox2.DropDown
    Else
    Exit Sub
    End If

    End Sub
     
    2. I am using a button on the press of which all the data input in this sheet is posted to another worksheet to sort of make a report of all the entries. While going through step by step execution of my macro associated with the button, I noticed that the private sub Combo Box_change event of the two combo boxes gets triggered repeatedly (even when the input field has nothing to do with the combo box) which results in some delay in the execution of macro. Is it normal with active x combo box?

    3. Also, is there any way that I could jump from one input cell/ object to another (from top to bottom) using tab or some such key?

    I would really appreciate if I could get some help on these.

    Thanks
    Shaveta
    Last edited: Sep 12, 2018
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,057
    Hi, welcome to the forum.

    It's bit hard to help you without sample workbook. Can you upload sample where your issue is replicated? Note that you can desensitize the data (while keeping data structure the same).
  3. Shaveta

    Shaveta New Member

    Messages:
    4
    Hey Chichiro,

    Thanks for replying. I am enclosing the sample file that contains all combo boxes and buttons that I am using for this file. If you could troubleshoot it, it will be a great help to me. :)

    Attached Files:

  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,057
    Try changing your code to...
    Code (vb):
    Sub ComboBox1_change()

    Dim sht1 As Worksheet
    Set sht1 = Worksheets("Entry Post")
    If ThisWorkbook.ActiveSheet.Name = sht1.Name Then
        ComboBox1.ListFillRange = "DynamicList1"
    Else
    Exit Sub
    End If


    End Sub

    Sub ComboBox2_change()

    Dim sht1 As Worksheet
    Set sht1 = Worksheets("Entry Post")
    If ThisWorkbook.ActiveSheet.Name = sht1.Name Then
        ComboBox2.ListFillRange = "DynamicList2"
        ComboBox2.DropDown
    Else
    Exit Sub
    End If

    End Sub
  5. Shaveta

    Shaveta New Member

    Messages:
    4
    Thanks for the advice. This definitely solves the issue of drop down appearing again and again despite making the sheet a little less user friendly.

    It would be a great help if you could give some suggestions on the other two issues that I mentioned in my query. :)
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,057
    Try the attached. Made some changes.

    2. removed .DrowDown part of code and made minor change to macro associated with "Post MOC".

    3. You'd need to protect the sheet, only leaving data entry cell(s) unlocked. Then you can use tab to cycle through (I protected the sheet without password).

    Attached Files:

  7. Shaveta

    Shaveta New Member

    Messages:
    4
    I had already removed the .Dropdown part like you suggested earlier for the 1st issue. It took care of the drop down appearing randomly. As for my 2nd query, the macros for combo box are still getting called at every step (checked in step by step execution using F8 key) in "Post MOC" macro, even while picking other cell inputs like Amount or Remarks. I guess that may be just a normal occurrence for a sheet specific code (it's the first time I have coded within a specific sheet).
    Regardless Thanks a lot for your help. I am glad I joined this forum.

Share This Page