Dear Sir, First I thankful to you for send Inventory Managment for practice , in that practice I serch verious Utube channel for serching dependent combobox but I spent 5 hours but not good for me then in meanwhile I look Fully Dynamic List in Combobox of VBA that I am surprise in few code I have done my problem with many facilities, for this I am highly oblize . thanks again.
Thanks for sharing your knowledge with everyone, Can you kindly enplane make video how to add Columns manually, e.g if i just want to add Column D, g, or Z. thank you
Thanks so much for this tutorial. It worked almost perfectly for me, but in my second combobox, the last rows value for selected column does not show. Does anyone know how to fix this?
Excellent work @PK: An Excel Expert, i want to extract data based on dependent drop down. i have prepared simmillar dash board using simple slicer, but i want to make a user form and extract data while i am gradually applying the filters with dependent dropdown. please help me with some guidance. an explaination would be more than i could ask. NIce work sir.
This video is very useful but when I m using this in my existing user form its showing unable to get the match property of the worksheet function class. Independent data is showing in combobox but when I using submit button this time the error is coming .can u suggest me for the solution.
Hi An Excel Expert, i learned a lot and i am following your tutorials, just curious whats the formula because at 9:50 the video got blurry hope you can help me or any one can help me with this. ty
wow your teaching is best! but i need some help of you how to code in vba not i worksheet this code please tell me you captured on worksheet but i want to code in vba.............eg:- combobox1 ("subject").........Dynamicaly in combobox2 = Biology.....physics.....etc
Thank you for the very useful tutorial sir. May I ask how can I add a Clear button to the userform for the dynamic list in combobox? I seem to have an error with the Match property of the worksheet function when I use my usual code for Clear button. Thank you sir
Please answer me I got an error 1004 c=application.worksheetfunction,match(me.combobx1.value,sh.range("1:1"),0) geta an error saying that "unable to get the match property of the worksheet function class"
take out the ".worksheetfunction."; just using "application.Match". This changes the error type from 1004 to 13. Error 13 is a type mismatch. This happens because you declared variables as integers, but when the values are assigned/used, they may be Text. (Dim i As Integer. This later can change to a text in 'Me.ComboBox2.AddItem') As a work-around, add a 'On Error Resume Next' in the second part of the code Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Setup Table") Dim i As Integer Dim n As Integer On Error Resume Next n = Application.Match(Me.ComboBox1.Value, sh.Range("1:1"), 0) Me.ComboBox2.Clear For i = 2 To Application.CountA(sh.Cells(1, n).EntireColumn) Me.ComboBox2.AddItem sh.Cells(i, n).Value Next i
hello, wornderful tutorial.. Love your command over Excel and VBA. I have one question. What, if i want to display the first item of the 2nd category list depending on the the main category chosen above? kindly explain.
Please help me on this: What about the ComboBox on the worksheet? How can I simply make a dynamic range and then populate the ComboBox with it? Without a For Loop , of course
*_Dear Sir, I am Md. Alamgir Kabir from Bangladesh. Thank you so much for your valuable tutorial. It's the same as my requirements, but I have a small query, How to add a unique item to this Combobox. Please help me_*
My range is in a column, not in a row. It begins in E8. I added a declartion or RNG as Range, Set rng = Range(Range("E8"), Range("E8").End(xlDown)) I kept Me.ComboBox1.Clear I rewrote the next line as For i = 8 To Application.WorksheetFunction.CountA(ActiveSheet.Rows(8)) I changed the 1 to a 5 in this line of code. Me.ComboBox1.AddItem sh.Cells(i, 5).Value Here's the full code. I hope it helps you set the range if it is other than 1:1 Private Sub UserForm_Activate() Dim sh As Worksheet, rng As Range Dim i As Integer Set sh = ThisWorkbook.Sheets("Sheet1") Set rng = Range(Range("E8"), Range("E8").End(xlDown)) Me.ComboBox1.Clear For i = 8 To Application.WorksheetFunction.CountA(ActiveSheet.Rows(8)) Me.ComboBox1.AddItem sh.Cells(i, 5).Value Next i End Sub
Hi. I used your changed code to my case. My range is also in column but start in B7. I changed yu code to: Private Sub UserForm_Activate() Dim sh As Worksheet, rng As Range Dim i As Integer Set sh = ThisWorkbook.Sheets("Urlopy") Set rng = Range(Range("b7"), Range("b7").End(xlDown)) Me.ComboBox1.Clear For i = 7 To Application.WorksheetFunction.CountA(ActiveSheet.Rows(7)) Me.ComboBox1.AddItem sh.Cells(i, 2).Value Next i all is fine, but I have more than 30 rows. This code shows me only 30 firsts rows. Last ten aren't visibility! How solved it? Any ideas? Ps. You have declared : rng As Range -> Set rng = Range(Range("b7"), Range("b7").End(xlDown)) Why? Where is it in solution?
Great lesson. However, I continue to have a runtime error '1004'. The overall function works properly until I added in the code for clearing the fields. When I remove the code starting at 7:41 everything works great. I guess I will need to remove that existing combo box and add in a new one and resubmit the code. Overall....very helpful
Hi PK, good job, but can you do the same and eliminate the duplicates , exp: if the month or the year is repeated twice or more, it won't be shown on the comboBox, Thanks
please help me "run time error 5" invalid procedure call or argument *** For i = 2 To Application.WorksheetFunction.CountA(sh.Cells("1,n").EntireColumn)
I have 2 Dropdown List with the same options (Included and Excluded) in different sheets. If I select " Included" in one dropdown list, it should also select "Included" on another dropdown list and vice versa. I am new in coding please help me out to crack this.
hi thank you so much great video im having a run time error with this code n = Application.WorksheetFunction.Match(Me.txtSupervisor.Value, sh.Range("1:1"), 0)
Hi Good morning sir I have made one searchable drop down list. My question is if my data is X workbook and I want to create searchable drop down list in Y workbook. is it possible and how to do that? Can you help me on that
Friend, can we have something similar to that about having a plan for a call center where employees can schedule their breaks and lunch and personal? or do you have a link for such a video? appreciate your assistance, thank you.
thank you very much, hope to get a notification once it is done, i believe its beneficial for a lot of people as well, keep it up, your doing a great job!!!
Sir I have an excel sheet, column A has dates and column B to J has other data. I want to create a combobox which shows those months which first date to end date available in Column A . Thanks in advance Please help me
hi, did you get solution to this? can you plz explain what do you mean by "which shows those months which first date to end date available in Column A"
Great tutorlia. However my c=application.worksheetfunction,match(me.combobx1.value,sh.range("1:1"),0) geta an error saying that "unable to get the match property of the worksheet function class" I don't understand as I have completly copied your code.
Sir, can we will do this condition on sheet ActiveX control combo box on sheet without user form in excel please help and give me sample file on my email id
Boss, just now tried again. In excel at form level, i used code "ComboBox17.Text = ComboBox17.ListIndex(0) and getting "Type mismatch error" Data is populated absolutely correct. Just want to select and display first item in list of combobox17
Thank you for the very useful tutorial sir. May I ask how can I add a Clear button to the userform for the dynamic list in combobox? I seem to have an error with the Match property of the worksheet function when I use my usual code for Clear button. Thank you sir
![PASULOL รามเกียรติ์ ตอนที่ 4 นนทกพบรัก [Ramakien Ep.4: Nonthok in love]](http://i.ytimg.com/vi/tMUQgmZzf_A/mqdefault.jpg)
Dear Sir, First I thankful to you for send Inventory Managment for practice , in that practice I serch verious Utube channel for serching dependent combobox but I spent 5 hours but not good for me then in meanwhile I look Fully Dynamic List in Combobox of VBA that I am surprise in few code I have done my problem with many facilities, for this I am highly oblize . thanks again.
much respect for your effort to explain this example. Thank you. It is exaclty what i needed.
Regards from Romania
So nice of you
Awesome videos...Your VBA tutorial videos is one of the best I have seen so far...well done and God bless you Sir
Thank you very much!
Finally I found a video with instructions that really works.
Thanks for your valuable feedback
Me to 1 month to find it thank you man
I am an Arab and this video helped me a lot .. Thank you
Thanks for your valuable feedback
Thanks . This is the video what I exactly want.... really it's very helpful
Thanks for your valuable feedback
You are good 👍🏻 thanks 🙏
Thanks for your valuable feedback🙏
Thank you, Very informative
Most welcome
Sir, really you are great!
Thanks for your valuable feedback
Another excellent class. Thank you.
Thanks for your valuable feedback
Sooooooo useful... Tanks a lot brother ...
Great instructions...very understandable.
Thanks for your valuable feedback
Thank you very much sir for this relevant video to my work can you plz teach us how to view data from excel sheet according to 3 or 4 combobox filter.
Thank you for this video.
My pleasure!
Thanks for good video
Thanks for your valuable feedback
Thank you man for this video
Welcome
Perfect! Is it possible to have a combobox as category 1, category 2, category 3?
Nice work!
Thanks for your valuable feedback
great work thanks
Thanks for your valuable feedback
Thanks for sharing your knowledge with everyone, Can you kindly enplane make video how to add Columns manually, e.g if i just want to add Column D, g, or Z. thank you
Good lesson to me, Thank a lot
Thanks for your valuable feedback
Thank You!
Thanks for watching
Bravo!
Thanks for your valuable feedback
Nice Video
Thanks, Really helpfull :)
Thanks for your valuable feedback
Thanks so much for this tutorial. It worked almost perfectly for me, but in my second combobox, the last rows value for selected column does not show. Does anyone know how to fix this?
Thanks a lot!!!!!
Thanks for watching
Very nice
Thanx
Most welcome
Thanks ... Thanks.
Thanks for watching
Thanks ... Thanks ... Thanks ...
Thanks a lot!
Thanks for watching
Thank you, it helped a lot...
Whether we can do it if multiple sheets are present
Thanks
nice
Thanks for your valuable feedback
Excellent work @PK: An Excel Expert, i want to extract data based on dependent drop down. i have prepared simmillar dash board using simple slicer, but i want to make a user form and extract data while i am gradually applying the filters with dependent dropdown. please help me with some guidance. an explaination would be more than i could ask. NIce work sir.
nice one
Thanks for your valuable feedback
This video is very useful but when I m using this in my existing user form its showing unable to get the match property of the worksheet function class. Independent data is showing in combobox but when I using submit button this time the error is coming .can u suggest me for the solution.
Thank you so much for this wonderful video, can I apply this my another userform, please guide me.
Hi An Excel Expert, i learned a lot and i am following your tutorials, just curious whats the formula because at 9:50 the video got blurry hope you can help me or any one can help me with this. ty
wow your teaching is best! but i need some help of you
how to code in vba not i worksheet this code please tell me you captured on worksheet but i want to code in vba.............eg:- combobox1 ("subject").........Dynamicaly in combobox2 = Biology.....physics.....etc
Hello sir
Sir please upload a video that's also contains the add items to dependent dropdown list.
Sir show row wise dynamically
Sir if I write the first letter of any month or year will the drop-down list show only those items that start with the letter I have entered?
Thank you for the very useful tutorial sir. May I ask how can I add a Clear button to the userform for the dynamic list in combobox? I seem to have an error with the Match property of the worksheet function when I use my usual code for Clear button. Thank you sir
Please answer me I got an error 1004
c=application.worksheetfunction,match(me.combobx1.value,sh.range("1:1"),0)
geta an error saying that "unable to get the match property of the worksheet function class"
take out the ".worksheetfunction."; just using "application.Match". This changes the error type from 1004 to 13.
Error 13 is a type mismatch. This happens because you declared variables as integers, but when the values are assigned/used, they may be Text. (Dim i As Integer. This later can change to a text in 'Me.ComboBox2.AddItem')
As a work-around, add a 'On Error Resume Next' in the second part of the code
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Setup Table")
Dim i As Integer
Dim n As Integer
On Error Resume Next
n = Application.Match(Me.ComboBox1.Value, sh.Range("1:1"), 0)
Me.ComboBox2.Clear
For i = 2 To Application.CountA(sh.Cells(1, n).EntireColumn)
Me.ComboBox2.AddItem sh.Cells(i, n).Value
Next i
End Sub
hello, wornderful tutorial.. Love your command over Excel and VBA.
I have one question. What, if i want to display the first item of the 2nd category list depending on the the main category chosen above? kindly explain.
Well and good
Thanks for your valuable feedback
Please help me on this:
What about the ComboBox on the worksheet?
How can I simply make a dynamic range and then populate the ComboBox with it?
Without a For Loop , of course
*_Dear Sir, I am Md. Alamgir Kabir from Bangladesh. Thank you so much for your valuable tutorial. It's the same as my requirements, but I have a small query, How to add a unique item to this Combobox. Please help me_*
Thank you so much ! and what about dynamic login form ?
Thanks for watching. Please watch below given video for login form:
th-cam.com/video/n7I5-6fj1kY/w-d-xo.html
@@PKAnExcelExpert thank you sir
@@PKAnExcelExpert Sir I got an error in the function match what is the solution please help me is my final project ?
My range is in a column, not in a row. It begins in E8. I added a declartion or RNG as Range, Set rng = Range(Range("E8"), Range("E8").End(xlDown))
I kept Me.ComboBox1.Clear
I rewrote the next line as
For i = 8 To Application.WorksheetFunction.CountA(ActiveSheet.Rows(8))
I changed the 1 to a 5 in this line of code.
Me.ComboBox1.AddItem sh.Cells(i, 5).Value
Here's the full code. I hope it helps you set the range if it is other than 1:1
Private Sub UserForm_Activate()
Dim sh As Worksheet, rng As Range
Dim i As Integer
Set sh = ThisWorkbook.Sheets("Sheet1")
Set rng = Range(Range("E8"), Range("E8").End(xlDown))
Me.ComboBox1.Clear
For i = 8 To Application.WorksheetFunction.CountA(ActiveSheet.Rows(8))
Me.ComboBox1.AddItem sh.Cells(i, 5).Value
Next i
End Sub
Hi. I used your changed code to my case. My range is also in column but start in B7.
I changed yu code to:
Private Sub UserForm_Activate()
Dim sh As Worksheet, rng As Range
Dim i As Integer
Set sh = ThisWorkbook.Sheets("Urlopy")
Set rng = Range(Range("b7"), Range("b7").End(xlDown))
Me.ComboBox1.Clear
For i = 7 To Application.WorksheetFunction.CountA(ActiveSheet.Rows(7))
Me.ComboBox1.AddItem sh.Cells(i, 2).Value
Next i
all is fine, but I have more than 30 rows. This code shows me only 30 firsts rows. Last ten aren't visibility! How solved it?
Any ideas?
Ps. You have declared : rng As Range -> Set rng = Range(Range("b7"), Range("b7").End(xlDown))
Why? Where is it in solution?
Great lesson. However, I continue to have a runtime error '1004'. The overall function works properly until I added in the code for clearing the fields. When I remove the code starting at 7:41 everything works great. I guess I will need to remove that existing combo box and add in a new one and resubmit the code. Overall....very helpful
Hi Chester, How did you fix this problem? I am facing this issue right now with the clear button and I do not know what to do. Thanks
Hi Chester, is the issue resolved? Do you mind to share the solution?
Hi PK, good job, but can you do the same and eliminate the duplicates , exp: if the month or the year is repeated twice or more, it won't be shown on the comboBox, Thanks
please help me "run time error 5" invalid procedure call or argument ***
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells("1,n").EntireColumn)
Thank you for your replay, it was my mistake
How to vaildation combobox value if when change the value by keypress
I have 2 Dropdown List with the same options (Included and Excluded) in different sheets. If I select " Included" in one dropdown list, it should also select "Included" on another dropdown list and vice versa. I am new in coding please help me out to crack this.
Sir I want edit form by name search box
Sir please tell me best book for VBA for beginners
Sir how to be same condition apply ActiveX control without userform how to be possible
Dear Sir, how can we code if we have more sub categories?
i use thi script,but when i input an error occur, can you help me?
sir please tell me how to make Double dynamic list in Combobox with coding please sir reply kar na !
hi thank you so much great video
im having a run time error with this code
n = Application.WorksheetFunction.Match(Me.txtSupervisor.Value, sh.Range("1:1"), 0)
I have same problem
Hi Good morning sir
I have made one searchable drop down list.
My question is if my data is X workbook and I want to create searchable drop down list in Y workbook. is it possible and how to do that?
Can you help me on that
Hi Kurban,
I will try to make such video very soon.
hi, friend, can you help me to design the data entry form for me how much your charges
I can help you during payment contact. (You can mail me : engrmamun66@gmail.com) with your requirement.
can yo give solution to show it on user form as list instead of showing it on excel sheet
Friend, can we have something similar to that about having a plan for a call center where employees can schedule their breaks and lunch and personal? or do you have a link for such a video?
appreciate your assistance, thank you.
Hi Clovis,
I don't have such video ready as of now but I will definitely try to make such video very soon.
Thanks for watching
thank you very much, hope to get a notification once it is done, i believe its beneficial for a lot of people as well, keep it up, your doing a great job!!!
Sir
I have an excel sheet, column A has dates and column B to J has other data. I want to create a combobox which shows those months which first date to end date available in Column A .
Thanks in advance
Please help me
hi, did you get solution to this? can you plz explain what do you mean by "which shows those months which first date to end date available in Column A"
Great tutorlia. However my c=application.worksheetfunction,match(me.combobx1.value,sh.range("1:1"),0)
geta an error saying that "unable to get the match property of the worksheet function class"
I don't understand as I have completly copied your code.
Did you find the answer ?
I am also observed same error . please help me
how can I do this in python?
Sir excel main MISS expert person Kon hota hai. Or isme Kiya Kiya aata hai excel ka
What is abilabil?
but after running the combo box how to enter data in excel sheet ???
You call a procedure that contains everything you want to do
Sir, can we will do this condition on sheet ActiveX control combo box on sheet without user form in excel please help and give me sample file on my email id
unable to get the mach property of the WorksheetFunction class, how can I solve that please
how?? please.. anyone? followed above but encountered error.. help
Hi,
Please send me your excel file along with the error snapshot on info@pk-anexcelexpert.com.
@@PKAnExcelExpert same problem
Sir without combo box how to done
you re the bt
Plz bro reply me
Seems, SIR dont like or dont get time to REPLY here :)
Hi Amir, sorry for the late reply. You can use listindex property of combobox. It starts from 0.
@@PKAnExcelExpert Thanks a lot dear. I tried it on form level but not working.
Boss, just now tried again. In excel at form level, i used code "ComboBox17.Text = ComboBox17.ListIndex(0) and getting "Type mismatch error"
Data is populated absolutely correct. Just want to select and display first item in list of combobox17
Thank you for the very useful tutorial sir. May I ask how can I add a Clear button to the userform for the dynamic list in combobox? I seem to have an error with the Match property of the worksheet function when I use my usual code for Clear button. Thank you sir
Same here... Getting error same error.. compile error
Same here..any resolution please
Thanks... Thanks... Thanks.
Thanks for watching