Although you are a little fast (but that is what the pause button is for, right) THANK YOU does not even cover how I am feeling so grateful at the moment. I have watched probably 20 different tutorials today, and yours is the only one that has gotten my anywhere. Thank you for spending the time in making this video.
Yes, Amey, I have an other video teaching on the dynamic ranges, but in this I wanted to get right into how one fills the comboboxes. Thanks! Oh and can't wait to see your projects. Have a great one!! Dan
Thanks, Dan , it was the same thing which I want to tell you and even we can create an expandable range by first creating dynamic name range through offset function and then adding row source into it. I have also done so many projects in excel using vba and shortly I am going to open my youtube channel . I will inform all things about that later. Please do watch. Thanks again
It is possible to do this without using a macro to populate the drop down menus, though it does take up some of the cells in the spreadsheet. For the example try entering the following formulae: C2 : =IF(B2=TRUE,1,0) D2: =COUNTIF($C$2:C2,1) E2: =A2 F2: =1 G2: =IFERROR((VLOOKUP(F2,D:E,2,FALSE)),"") Then hilight C2 to G2 and copy these 5 columns down. Ensuring that column F now shows 1,2,3,4,5... Set column G as the named range and tie this to the combo box. The items shown in the named range will only be those that have a True status Replace the word TRUE in the above (column C) with an absolute cell reference eg. =IF(B2=$I$1,1,0). Try typing FALSE into I1. The drop-down will now show only the values with a status of false. This allows nested drop down menus (ie. a drop down powering the selection criteria of a second drop down).
Is it possible to filter that list based on another dropdown? For example: Drop down box 1 (Type of food): Desert food; Junk food; Dairy Food. ---> Desert food was selected Dropdown box 2 Specific food has only: Skittles; Chocolate --> User is only able to view skittles + Chocolate because they are are the only ones that are categorized as a desert food
Dear sir, I found the explanation interesting, maybe you can help me on the way, if you have a dropdown on your Excel sheet you can also enter the same VBA code or make sure it ends up on a certain cell. Can you do two dropdown with interconnecting, making sure by conditional. I still have many questions regarding VBA, do you want to help me?
I have a print command button on a userform but I don't want the command buttons to show when I print the form. Is that possible and would you consider a training video for this? Great videos, thanks!
Hi Merlin, So are you printing a worksheet or a userform itself? Not sure if I understand. If you have an object on a worksheet, usually you can change the settings so it doesn't appear on printout. Thanks Dan
Hello. Would you please be able to tell me how to list and set custom share point properties for excel files that are on a Share Point using VBA? For example when I created SharePoint library I added custom properties such as Reviewed? , Remediation Complete? and IAM Accepted?. These properties can be set on SharePoint for each file. I want do this using VBA. Thanks!
Hi thanks for the tutorial. 😊 is there a way to search-as-you-type in the combobox? I have a thousand items on the list, and i need the combobox to 'suggest' items regardless if the typed text is on the beginning middle or end of the text on the list. For example: i have items float glass, glass beads, sunglasses, ceramic tiles, abrasive on the list. If i type 'glass' it will show those three items with glass on it. My combobox is searching through the first letter of the string only. I'm a newbie in programming so i have no idea how to do it. Thank you in advance
I have a excel sheet that has two comboboxs. the first one combobox1 loads values from Col A. What I need to do is depending on what the text is in the adjacent Col B for that selection load combobox2 with Col A from a named sheet. So I have 4 items that will load into the first box Apples, Bananas, Pears, Grapes and I have sheets named Apples, Bananas, Pears, Grapes. So If someone picks Apples the second box will be loaded with all the things in the Col A of Apples sheet. I have several versions of attempts I have made but can not get everything to work. Any help will be appreciated.
Hello sir, maybe you can help me, I have worksheets 1,2 and 3 in it is on every data sheet they all look the same, I want to create a user form that can say on top of which sheet I am below I want a name look up below that I want to look up the data in columns 3 and 4 start from row 3 this for all 19 players, can you give me an example, can you help me?
Hi Dan, I have a problem creating a code for attaching photos, i try it but still not a concrete way because i use a lot of active button. I hope you can help me and i really appreciated alot. I have a check list on sheet 1. means lots of raw and column. each raw have an item number 1 up to 100 example. item 1 in range k2 i use to type all the photo number. which 1 up to 8 photo max. and item 2 in range k3 i only have 3 photo, in item 3 in range k4 have 6 photo. and so on. In Sheet 2 i have to attached all the photo with the same designated reference item number simultaneously if you click the button. The photo will be attach with in the certain range of cell. like from A5:F16 only. With in that range the photo will be resize automatedly to fit in it. Example if only 1picture the size is (150x145), if 2picture the dimension is (135x125) and so on up to 8 photo max to be attach in each item number. I hope you can help me to decode a command in one click of a button. Thank you & very appreciated.
Hello, maybe you can help me, I have an Excel workbook, it is for our billiards club. What do I want to do now, I want to see who can still play whom, but there are 3 handicaps, (It is part made in VBA), In the worksheet "MENU" it says on F2 which number (example if F2 on "1 handicap 1 is played), there are 20 players in total (each player has a name), for example "player1", "player2". I want to make a combo box that can do all that, can you help?
Hi Gerard, Sounds complicated. Can you email me more info and screenshots at excelvbaisfun.com/contact ? I should be able to advise or help as time allows. Thanks Dan
I think this is the code I'm looking for but not sure. Say if I select an item from a list in my dropdown list and I want to populate text boxes on the form with the information from the worksheet in the same row but different columns. Exmp. Select Betty from dropdown list then I want the text boxes each to populate with her class, instructor, grade, and GPA at the same time. These are all column headings in the worksheet and the students are in the first column.
Hi sidior12, you could do a vlookup and populate the text boxes that way. If you search my channel for VLOOKUP, you'll see some of these methods. Cheers! Dan
Hi Dan. Tried VLookup but again no luck. I used "case" for the first two list boxes which worked fine, but I keep getting a problem in my last line of code.. That line of code is suppose to point to the separate worksheet that lists the doctors' names in Listbox1 from the subcategory of specialties that are returned in combobox2 (ComSelect). The doctors' names are in sheet "Spinal_docs" cells B2-B83 of the worksheet but nothing happens. My code is below. This last line is wre I'm havng the problem: Private Sub ComSelectCategory_Change() [when select from catagory return values in subcategories] Me.ComSelect = "" Select Case Me.ComSelectCategory Case "Neuro" Me.ComSelect.RowSource = "Neuro" Case "spinal" Me.ComSelect.RowSource = "Spinal" Case "Ocular" Me.ComSelect.RowSource = "Ocular" Case "Geriat" Me.ComSelect.RowSource = "Geriat" Case "Pied" Me.ComSelect.RowSource = "Peid" [below is the separate sheet and names of spinal doctors that I'm tring to return in Llistbox1] If Me.ComSelect.RowSource = "Spinal" Then Me.ListBox1 = Sheets("Spinal_docs")!$b$2:$b$83
sidior12 You're missing the END SELECT for your select case. You also need an END IF for your IF THEN statement. Also, instead of Sheets("Spinal_docs")!$b$2:$b$83 try Sheets("Spinal_docs").range("$b$2:$b$83") Thanks!! Dan
Although you are a little fast (but that is what the pause button is for, right) THANK YOU does not even cover how I am feeling so grateful at the moment. I have watched probably 20 different tutorials today, and yours is the only one that has gotten my anywhere. Thank you for spending the time in making this video.
Yes, Amey, I have an other video teaching on the dynamic ranges, but in this I wanted to get right into how one fills the comboboxes. Thanks! Oh and can't wait to see your projects. Have a great one!! Dan
Never learned so much staff about Excel in 15 min! Amazing. Thx you a lot
Thanks so much Dan for uploading this! Super helpful for a newbie like me!!!
Hi Adrienne Hong, You're so welcome!
Thanks, Dan , it was the same thing which I want to tell you and even we can create an expandable range by first creating dynamic name range through offset function and then adding row source into it. I have also done so many projects in excel using vba and shortly I am going to open my youtube channel . I will inform all things about that later. Please do watch. Thanks again
It is possible to do this without using a macro to populate the drop down menus, though it does take up some of the cells in the spreadsheet. For the example try entering the following formulae:
C2 : =IF(B2=TRUE,1,0)
D2: =COUNTIF($C$2:C2,1)
E2: =A2
F2: =1
G2: =IFERROR((VLOOKUP(F2,D:E,2,FALSE)),"")
Then hilight C2 to G2 and copy these 5 columns down. Ensuring that column F now shows 1,2,3,4,5...
Set column G as the named range and tie this to the combo box. The items shown in the named range will only be those that have a True status
Replace the word TRUE in the above (column C) with an absolute cell reference eg. =IF(B2=$I$1,1,0). Try typing FALSE into I1. The drop-down will now show only the values with a status of false.
This allows nested drop down menus (ie. a drop down powering the selection criteria of a second drop down).
"...and, of course, Skittles." Thanks Daniel for another great tutorial! I always learn a lot from your videos.
Another exciting top-notch tutorial by Daniel..Thanks genius.
Thank you and thanks for the comments and the like!
Great, this solved (one of) my problems. Thanks!
Thanks Dan for uploading this video!
Excelent work ,thank you very much for sharing ..thanks a lot
Great job Dan. Thanks.
I'm really glad you enjoyed this! Thanks, Dan.
I'm glad you like. Thanks Joe!
how could you make things so simple!!!!
excellent!
Thank you for this video.
Dan, great video, I was looking for a tip like this to improve a program I am working on. Keep the good work!!
Very helpful, thank you!
مرسی
very good lesson thanks for your help
wojciech765 Awesome thanks!!
Good work, Tranks.
Thanks!
hey dan! thanks for all the video, how do i submit my questions regarding a project i am doing?
Is it possible to filter that list based on another dropdown?
For example:
Drop down box 1 (Type of food): Desert food; Junk food; Dairy Food.
---> Desert food was selected
Dropdown box 2 Specific food has only: Skittles; Chocolate
--> User is only able to view skittles + Chocolate because they are are the only ones that are categorized as a desert food
Dear sir, I found the explanation interesting, maybe you can help me on the way, if you have a dropdown on your Excel sheet you can also enter the same VBA code or make sure it ends up on a certain cell. Can you do two dropdown with interconnecting, making sure by conditional. I still have many questions regarding VBA, do you want to help me?
I have a print command button on a userform but I don't want the command buttons to show when I print the form. Is that possible and would you consider a training video for this? Great videos, thanks!
Hi Merlin,
So are you printing a worksheet or a userform itself? Not sure if I understand. If you have an object on a worksheet, usually you can change the settings so it doesn't appear on printout.
Thanks
Dan
ExcelVbaIsFun, I am printing the userform itself.
Thank You!!!
Thanks brother... but mine doesn't get loaded with dynamic named range, it only accepts fix data just like you did, why ???
Hello. Would you please be able to tell me how to list and set custom share point properties for excel files that are on a Share Point using VBA? For example when I created SharePoint library I added custom properties such as Reviewed? , Remediation Complete? and IAM Accepted?. These properties can be set on SharePoint for each file. I want do this using VBA. Thanks!
Thanks...
Hi thanks for the tutorial. 😊 is there a way to search-as-you-type in the combobox? I have a thousand items on the list, and i need the combobox to 'suggest' items regardless if the typed text is on the beginning middle or end of the text on the list.
For example: i have items float glass, glass beads, sunglasses, ceramic tiles, abrasive on the list. If i type 'glass' it will show those three items with glass on it.
My combobox is searching through the first letter of the string only. I'm a newbie in programming so i have no idea how to do it. Thank you in advance
Thanks for it, but i don't understand how the vbform can fill combobox from excel worksheet
I have a excel sheet that has two comboboxs. the first one combobox1 loads values from Col A. What I need to do is depending on what the text is in the adjacent Col B for that selection load combobox2 with Col A from a named sheet. So I have 4 items that will load into the first box Apples, Bananas, Pears, Grapes and I have sheets named Apples, Bananas, Pears, Grapes. So If someone picks Apples the second box will be loaded with all the things in the Col A of Apples sheet. I have several versions of attempts I have made but can not get everything to work. Any help will be appreciated.
Hello sir, maybe you can help me, I have worksheets 1,2 and 3 in it is on every data sheet they all look the same, I want to create a user form that can say on top of which sheet I am below I want a name look up below that I want to look up the data in columns 3 and 4 start from row 3 this for all 19 players, can you give me an example, can you help me?
i need to populate specific cell locations based off a combobox drop down population. can you help?
Hi Dan, I have a problem creating a code for attaching photos, i try it but still not a concrete way because i use a lot of active button. I hope you can help me and i really appreciated alot. I have a check list on sheet 1. means lots of raw and column. each raw have an item number 1 up to 100 example. item 1 in range k2 i use to type all the photo number. which 1 up to 8 photo max. and item 2 in range k3 i only have 3 photo, in item 3 in range k4 have 6 photo. and so on. In Sheet 2 i have to attached all the photo with the same designated reference item number simultaneously if you click the button. The photo will be attach with in the certain range of cell. like from A5:F16 only. With in that range the photo will be resize automatedly to fit in it. Example if only 1picture the size is (150x145), if 2picture the dimension is (135x125) and so on up to 8 photo max to be attach in each item number. I hope you can help me to decode a command in one click of a button. Thank you & very appreciated.
how to create searchable dropdown list in userform....i have many data in row 1 in my worksheet, its time consuming to select. plz help
Gostaria de saber como se aplica o resultado da pesquisa da comboboxe a uma céluda do excel.
Obrigado
Hey for some reason my properties window doesn't have a rowsource property? is there another way to achieve the same result?
R3aktt I have the same problem. Maybe is because I have Mac version?
Have you PC or Mac?
Hey Dan what event is the best to use to fill an activex combobox dynamically using a .additem vba code?
Pankaj Nepal Any event should do fine. Click, double click, mousemove, userform initialize - they're all good depending on when and where!
Hello, maybe you can help me, I have an Excel workbook, it is for our billiards club. What do I want to do now, I want to see who can still play whom, but there are 3 handicaps, (It is part made in VBA), In the worksheet "MENU" it says on F2 which number (example if F2 on "1 handicap 1 is played), there are 20 players in total (each player has a name), for example "player1", "player2". I want to make a combo box that can do all that, can you help?
Hi Gerard,
Sounds complicated. Can you email me more info and screenshots at excelvbaisfun.com/contact ? I should be able to advise or help as time allows.
Thanks
Dan
How do you add a combobox list from another worksheet?
I think this is the code I'm looking for but not sure. Say if I select an item from a list in my dropdown list and I want to populate text boxes on the form with the information from the worksheet in the same row but different columns. Exmp. Select Betty from dropdown list then I want the text boxes each to populate with her class, instructor, grade, and GPA at the same time. These are all column headings in the worksheet and the students are in the first column.
Hi sidior12, you could do a vlookup and populate the text boxes that way.
If you search my channel for VLOOKUP, you'll see some of these methods.
Cheers!
Dan
Hi Dan.
Tried VLookup but again no luck. I used "case" for the first two list boxes which worked fine, but I keep getting a problem in my last line of code.. That line of code is suppose to point to the separate worksheet that lists the doctors' names in Listbox1 from the subcategory of specialties that are returned in combobox2 (ComSelect). The doctors' names are in sheet "Spinal_docs" cells B2-B83 of the worksheet but nothing happens. My code is below. This last line is wre I'm havng the problem:
Private Sub ComSelectCategory_Change()
[when select from catagory return values in subcategories]
Me.ComSelect = ""
Select Case Me.ComSelectCategory
Case "Neuro"
Me.ComSelect.RowSource = "Neuro"
Case "spinal"
Me.ComSelect.RowSource = "Spinal"
Case "Ocular"
Me.ComSelect.RowSource = "Ocular"
Case "Geriat"
Me.ComSelect.RowSource = "Geriat"
Case "Pied"
Me.ComSelect.RowSource = "Peid"
[below is the separate sheet and names of spinal doctors that I'm tring to return in Llistbox1]
If Me.ComSelect.RowSource = "Spinal" Then
Me.ListBox1 = Sheets("Spinal_docs")!$b$2:$b$83
sidior12 You're missing the END SELECT for your select case. You also need an END IF for your IF THEN statement.
Also, instead of Sheets("Spinal_docs")!$b$2:$b$83
try
Sheets("Spinal_docs").range("$b$2:$b$83")
Thanks!!
Dan
Hey man, good video. Your outro is too loud though.
Can I possibly email you a combo box problem I have? If yes, please let me know whats the best email to get you on. Many thanks
course you can, paulkent71@gmail.com
Someone knows how to reefer to a VBA dropdown menu value in a cell function ?
Theme song and microphone volume are unbelievably unmatched. Them song almost busts speakers.LOL try to adjust if possible.