Excel VBA Forms Part 6 - ComboBox Controls

Thanks for all you do!!!

I scoured the internet for HOURS yesterday trying to understand all of the jargon involved in populating lists for combo boxes, and it would have been SO much easier if I had just clicked on this video. Thank you so much for making these!

Is there a way to use range.advancedfilter to populate the combo box list with distinct values?

its giving some strange error numbers, am not able to find the issue, is it a bug or something? any advise guys

Private Sub USARATING_Click()
category.Clear
category.RowSource = Sheet1.Range("USA").Value
End Sub

and the debug is pointing to category.clear, i name my combobox category

THANKS ITS GREAT
I HAVE QUERY WHICH EXPLAIN BELWO, THIS VIDEO SUGGEST BY @Make It Happen With Excel BUT I NOT SEE IN VIDEO WHAT I NEED
ACTUALLY I NEED ONE COMBOBOX SHOW ONLY EMPLOYEE NAME WHICH IS IN ACCOUNTS DEPARTMENT
AND ITS ALSO DYNAMATICALLY
EMPLOYE NAME DEPARTMENT
ABC IT
DEF ACOUNTS
GHI HR
JKL HR
MNO ACCOUNTS
PQR ACCOUNTS
STU IT
VWX ACCOUNTS
YZ ACCOUNTS
ACCEPT MY APPRICIATION AN ADVANCE

Thank you very much for your fantastic tutorials, Andrew.

30 minutes of your video saves hundreds of hours of exasperating google searches, frustrated face-palming and strenuous rote learning. I am consistently applying the techniques I learned here and still continuing with my learning.

I have a few queries related to the comboboxes. I have accomplished a few of the objectives but I am not sure whether those are the best methods. Could you please review and give your comments.

Query 1: I want to start typing in FilmCertificates combobox and immediately the suggestions for auto complete should appear inside the combobox. I accomplished it the following way. Please advice if there are better methods.
Private Sub FilmCertificate_BeforeUpdate (ByVal Cancel as MSForms.ReturnBoolean)


Dim inlist as Boolean, i as long
For i = 0 to FilmCertificate.ListCount-1
If FilmCertificate.List(i) = FilmCertificate.Value Then inlist = True
Next I
If inlist or FilmCertificate.Value = Empty Then
FilmCertificate.BackColor = rgbPink
FilmCertificate.SelStart = 0
FilmCertificate.SelLength = Len(FilmCertificate.Value)
Cancel = True
End If
End Sub

Query 2: Imagine FilmCertificate combobox is at the top, above the FilmName textbox. The FilmName textbox remains disabled. My aim is to enable typing in FilmName textbox only if I choose something in the FilmCertificate combobox and tab away. If I try as follows, the cursor skips the FilmName textbox and sets focus in FilmGross textbox
Private Sub FilmCertificate_BeforeUpdate (ByVal Cancel as MSForms.ReturnBoolean)
FilmName.Enabled = True
End Sub

However I was able to accomplish this through KeyDown event as follows. Again, I am not sure if this is the best method. Please advice whether there are better methods.
Private Sub FilmCertificate_KeyDown (ByVal KeyCode as MSForms.ReturnInteger, ByVal Shift as Integer)
If KeyCode = 9 Then FilmName.Enabled = True
End Sub

Query 3: UNSOLVED. I am helping someone who uses Arabic language on user forms. FilmCertifcate has its dropdown arrow on the right. When the language is English, the text is left aligned and dropdown arrow is on the right. For Arabic, this has to be the opposite.i.e. dropdown has to be on the left side and the text has to be typed from left to right. Is there a way to achieve this?

Hi Andrew, could you please create a new video tutorial on how to create a searchable combobox from the keywords the user entered and suggest searched results to display a dropdown list with the combobox_change event in the VBA userform. Thank you very much.

Hi Andrew, your tutorials are the best of any subject I have experienced, simply amazing, I find myself going back time after time. I can't thank you enough :-)

I came across your channel today and the content is super awesome thanks for this by heart. Thank you so much

This is awesome. Andrew your work is brilliant. I have a question.
For the loop to work over one frame you have entered the code. For each ctl in Filmdetailsframe.controls, however say if you had 3 frames and you only wanted to loop over two frames. You would avoid me.control, however how would you add the additional frame control.
Any help appreciated. Mark.

A few years ago I used WiseOwlTutorials for learning SQL. I thought it was fantastic. Needed to brush up on some VBA and found them again. Wonderful training., Thanks very much!

Thanks for your videos.. one question, can we pass one object from a sub procedure or function to a combo box?? TIA.

Thanks a lot for this, Your channel is the absolute best.

Does a combo box accept only vertical lists I tried a horizontal one and it only shows the first item

Always great to view your videos; clear and concise.
One quick question: I am attempting to format my combo box value and want to center horizontally and vertically. I was able to do the firs but not the latter (it is at the top of field, not centered); I checked in the properties section, and haven't found anything relevant. Do you have a trick to perform that by any chance? Thank you so much!

Thank you for your great videos!  I am trying to use a userform where the day, month and year are selected from combo boxes and then the data on the sheet is searched for between the dates selected. How would I go about doing this? I have got the combo-boxes set up and the code to search between specific dates but I can't get it to search using information the user has selected

Andrew, best learning venue I've seen for VBA.

How would one go about adding information to different lists based on the selection in a combo box. I would like to use the same form to populate the lists. For example; if the user selects Blue in the combo box, the information added in the form would be entered in the Blue list. Greeen, the green list, etc.

Private Sub certificatecombobox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If certificatecombobox.Value = "" Then
certificatecombobox.BackColor = rgbPink
labelcertificate.Caption = " Choose Certificate"
Cancel = True
End If
End Sub

why It does not work?

Can you make the videos 16:9?

Hi Andrew,

Thanks for form Tutorials, more videos form Tutorials pls :D

i have a problem regarding last part of the video
ActiveCell.Offset(0, 0).Value = cboEquipmentDetails.Column(0, cboEquipmentDetails.ListIndex)
ActiveCell.Offset(0, 1).Value = cboEquipmentDetails.Column(1, cboEquipmentDetails.ListIndex)
this code is working we dont have problem with this but what if i forgot to input combobox details

this showing error, after i forgot to input combobox
run-time error '381':
could not get the column property.invalid propery array index

please check. :D TIA

Andrew!!! Can't stop thanking you for the wonderful series on forms.

Thanks.