• 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...

  • 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

Shaveta

New Member
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:
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:
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).
 
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. :)
 

Attachments

  • Template-Sample.xlsm
    50.4 KB · Views: 11
Try changing your code to...
Code:
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
 
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. :)
 
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).
 

Attachments

  • Template-Sample.xlsm
    50.1 KB · Views: 20
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.
 
Back
Top