Two Comboboxes VBA Userform - One Controls the Other - Excel VBA Is Fun
HTML-код
- Опубликовано: 6 сен 2024
- 📊 Free Workbooks: www.excelvbais...
🥷Join Excel Ninja Pro: www.excelvbais... Months FREE On Annual Plan Auto Applied)
🥷Excel Ninjas FB Group: www.excelvbais... (Free downloads, Trainings, Live Q&A and more)
Fantastic Developer Tools:
🔒 Transform Any Excel File Into A Locked EXE: www.excelvbais... (25% off with code ‘25OFF’)
🟡 Create Custom Installers: www.excelvbais...
👋 Business Inquiries, Consulting, Comments, etc: www.excelvbais...
Instead of using offset you can use the table function. Still dynamic and saved me some time.
Dan, You are a God send. I'm a newbie to VBA and have been working on this code for a couple of days. My wife had suggested for me to make the secondary combobox dependent to the other, instead of just listing all the divisions in one pull down. As a newbie I struggled but was amazed on how close I did come to making it happen. If it wasn't for you and your series of tutorials, I'd be lost. I had her watch with me and she thought the world of you....nice work btw! I can now continue onto the elaborate user form I'm designing. It may be a bit out of my league (me being a newbie and all), but you have given this man some hope. Thanks again for all that you do, you are definitely "paying it forward"..have a blessed day!
NJ Pete, I'm so glad I could help. Hi to the wife and fam! I'll keep bringing the lessons, keep learning buddy. You can do anything you set your mind to. When your program becomes a huge success and you make your first kazillion dollars, consider making a donation to my church "Father's Arms" here: www.paypal.com/us/cgi-bin/webscr?cmd=_flow&SESSION=SLaiZr_xFpBmgECiNHbXwyOgueM4-X0zhbxpx0y8wsuFbqPe-9leIpZ-goi&dispatch=5885d80a13c0db1f8e263663d3faee8d7ff5e1e81f2ed97dd1e90bd72966c40c OR your own church OR help a poor person somewhere that you meet. lol thanks!
My goodness- this was incredible! I spent hours searching VBA forums and didn't find anything nearly as clear and effective as this. Thank you, thank you, thank you.
I have a userform with 2 combo boxes and the first will give options from Sheet1 Table1 A2:A25. Then say the selected the option from A3. Now in the second combo box I need the corresponding data from Sheet1 Table1 B3:F3. I really appreciate your videos and I could really use your help! Thanks so much.
I was compelled to write this comment. This video really helped me out. I was going nuts trying to figure out how to do this. You have a very clear straightforward style. Very easy to understand. Thanks for helping me out.
This is awesome. It might be just what I need. Here's the problem. I'm building an incident reporting spreadsheet for our hospital system. I would like to have exactly what you have done here but with the potential to autofill the dependent combobox if the room numbers are from Hospital A fall within a specific range. For example: They would enter Hospital A, room 105. The Unit combobox would auto populate with ICU. 2 of our hospitals have discreet room numbers, the third has several units with room 2. So in the unit combobox, it would show all available units at that specific hospital.
None of this would be necessary if the docs weren't so lazy. The nursed have no problem filling in the combobox at the first two hospitals. Grrrr
I hope you can help.
I search on this subject for two days before I find what you was published,thank you so much
Hey Dan, Thanks so much for posting the video. When create Dynamic ranges I just use the data itself (without the header row) so i dont need to then minus out the header row. Ex. "=Offset('Sheet1'$A$2,0,0,CountA('Sheet1'$A$2:$A$100),1)". This might be a bit easier. Thanks for the video. Keep up the great work.
I've tried so many methods from VLookup, case, application wrksheet function, ect., to no avail. Essentially this is my scenario:
For combobox 3: When combobox 2 (which lists surgeon types and is on sheet 2) displays a category (say osteopathic), then go to sheet 3 (which lists the surgeons' names), then look in the column "A" for all cells containing "os" (for osteopathic surgeons as other surgeon types are coded in the same column) and show the list of names from column "b" that have "os" before them in column "a". This result should fill combobox 3.
That's what I'm trying to achieve
THANK YOU I've spent 5 hours trying to figure out how to do this and it finally worked!!!
thanks a lot for vids..but i have a question about 3 depended comboboxes..for example :
-First Combo box is about monitor (Panasonic and LG)
-Second Combobox is about the size of monitors
-Third ist about the weigh of Monitors
how should i do it?
Thanks for saving me a lot of time, I was using a AddItem property with in a For loop to load my comboboxes, but, thanks to you, not any more... And the way you use the dynamic name is awesome, never imagine you could do that. Thanks again.
Did you have a previous video that explains more about the "Row Source" formula you created during this video (at around 2:15) to make the dynamic ranges? You also used an Offset function. I'd like to learn more about Row Source and the Offset function and you do a great job explaining! Thanks!
IS THERE ANY WAY TO TRANSFER DATA FROM STAAD WHICH IS A STRUCTURAL ANALYSIS SOFTWARE TO EXCEL USING VB MACROS
Hi Sir, can u teach how to create a check voucher form, printable and saveable. Thanks.
Hi,
I have a piece of code9below) that finds the last row in a data sheet and then adds values from the controls on the user form ( all controls a named Reg 1-10 except for a combo box I have named cbo name.
'change the number for the number of controls on the userform
cNum = 10
'add the data to the selected worksheet
Set nextrow = Sheets(sht).Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
how can I concantanate on the data sheet two of the controls values (cbobox and reg)onto one line in the cell ie : MaterialType with Material Description ie :Fitting - Pipe elbow
Very well explained in simple and unambiguous way , Thank you very much
thanks Jayesh!
May I ask a quite the same problem, I have a table consisting of company code and documents numbers. In column A the list of company code which could be repeated depends on the number of documents posted. Column B is for the document numbers which has unique value.. I want to have a combobox1 which displays the list of the company codes, and a combobox2 which displays a dynamic list depends on the chosen company code which picks up every document numbers pertaining to the company code chosen..i hope I have expalined it clear..please help!
This is a great video explaining cascading. However, I am trying to do a cascade of about three levels down. Using your example, I want to be able to select Engineering - Mechanical - John, Ken or Mary . How can i achieve this? Thanks in advance.
Hi Dan , thank you for all your tutorial I'm very thankful to you, they helped me very much. I hope is not much to ask you a quick question. I tried to control two comboboxes together, but whenever I have too many blank cells in my list I don't get the full items in the list, fet of them seem missing. any suggestion. ? Thank you in advance .
I am using the MAC version of Excel and I don't find the option "RowSource". Can you tell me how can I get this feature?
Help me. how can I create four ComboBoxes VBA Userform One controls the rest three in Excel vba? thank,
Thank you... This is a really helpful tutorial... Well explained...
I have a problem here anyway... Hope you can help?
How do i tackle a problem where my named range has got spaces or it named as underscore by default?
This is so helpful! I'm having a fair bit of trouble with writing the macros to do something similar in a userform but instead of using dynamic lists, I used tables. I have a list of government departments and separate lists of the sub-departments that sit under those departments. I've set them all up as tables. I entered the table reference for the departments into the first combo box as the row.source (which works), then did a macro very similar to yours for the second combo box but it's blank - nothing in the drop down. Any ideas why? Maybe I need to specify each table as a dim?
Hello Dan. Is there a way to fill a label box based on TWO dependable combobox selections?
Here is what I have. Each value in a Combobox1 has its own unique range that is being populated in a Combobox2 (dependable combo boxes). I used “Select Case” and “RowSource” method to accomplish that. Now, each value in a Combobox2 has it is own unique description which I need to populate in a label box. For example: if combobox1 = “GEML01” then Combobox2= {0%, 50%,80%,100%} and then, based on Combobox2 selection, a Label box will be populated with the unique description related to specific percentage.
I’ve been struggling with this for a while. Hoping that you can help me.
this is great! thank you so much!
Dan,
It works perfectly, but I cannot make it work if my combo boxes are in a spreadsheet (using ListFillRange) instead of a user form. Any suggestions?
this is great, thank you very much. Do you think it would be difficult to amend the code for the First ComboBox to "read" the word from another cell in another tab? How can i amend Case formala to read the correct project name?
Very good. Now how do I convert this to a form to email to user to fill out and return to me.
Sandra Tibbs Here is one really good example of using email with Excel. Hope this helps some How to Email Your Current Workbook in Excel VBA
Is the code the same if you are using the comboboxes in the spreadsheet rather than user forms?
What if you had a third combobox for the names of the medical surgeons on a separate worksheet? How do you reference the third work sheet to populate the third drop down with the list of names for medical surgeons?
Should be just the name of the named range, or try sheet3!medsurgeons if the plain named range aka medsurgeons doesn't work. What have you tried?
Dan
You won't believe this Dan, but I think I found my solution from your tuttorial, "How to Filter Combobox From Another Combobox - Cascading Comboboxes." It's the filtering function (for comboboxes 1 & 2) that I think I'm trying to achieve. Gonna give it a shot and let you know how it works.
OH, by the way, I saw it on your tutorial, "How to Filter Combobox From Another Combobox - Cascading Comboboxes"
Great stuff, I was looking for this for a long time :-) Thanks a lot
Thanks for your VBA series. Regarding the dependent Combobox, your code is useful if we have a few and limited number of lists from which user should chose in Combo 2. What if my Combo 2 list is based on a table with many rows (more than 100) each of which provides data for a Combo 2 list depending on the user's selection in Combo 1. The problem is also more complicated when the table in which the Combo 2 lists are added/updated is regularly modified and in continuous increase. Your code will have to be reedited each time to add a new list in Combo 2. My question is: is there any code that would imitate the "INDIRECT" function when used to generate the list in Combo 2 in Data Validation?
Thank you for sharing precious knowledge with us
So very welcome, Jitender!
Thanks a lot for all of your lessons it has been a great great source. However I have a question. In your "Data entry a breeze with user forms" video you make us fill combo boxes via looping through on initiation;
for each x in [namedrange]
me.combobox.additem x
next x
and now we find out you can do the same in properties menu with just filling in row source. what's the difference?
thanks
thank you so much ive been looking for this
+isaiah otieno You're very welcome!!
Smart stuff Dan. Thanks for uploading this.
Hello, how does this work if the ranges are on a different worksheet but in the same workbook?
it should work the same. When you name your range it should have the sheet associated with it. Let me know if you have any more questions
thank you.Nice work.
Hi shamserpulami, You're welcome!
Dan, Im trying to do this but with data across different sheets. For example a selection on the first box (contains sheet names) will trigger an event directed at that specific sheet and will define a range in that sheet. However the definition of the Dane is giving me problems. You think I could get some help?
Jesus Espiga Can you paste your code?
thanks
Dan
ExcelVbaIsFun
Thats really useful. Thank you very much for posting this.
I have a small question: I am using this in a worksheet and its working fine. Only problem is once I select an option in the second combo box it doesnt allow me to click on the links which are in the same sheet unless I click somewhere in the sheet. What should I do to make excel select the first cell (A1), once I select any option in the second combo box.
"From c to shining c" lol
Hey Dan, why not just the following:
in combobox1 create a Change Event with the code:
me.combobox.2.rowsource= me.combobox1
Excellent observation, Claudio! That would work in this case, but I wanted people to also know how to customize this, not to limit themselves to items in the first list as named ranges. I didn't think to do it that way also. Thanks!!! Dan
ExcelVbaIsFun thanks Dan.....
thank you very much for the precious informations
Thanks Dan! much appreciated!!!
Very NICE! Thank you!
Thanks, so glad it helped, Mr. UMTS!
excellent tutorial many thanks.
Any idea how I can do this in word?
why do i get this strange error "we found a problem with this formula", after typing everything exactly as you did in 'refers to' field when creating new name ? someone help pls
+Emo Penguin figured that out... How do I make this to work with comboboxes in workbook, instead of userform? Cause i always get error 438
Did you ever figure this out, i'm having the same issue
How did you solve this please?
Tanks Dan!
Is there a way to do this with multiple combo boxes? for example cmbbox 1 controls 2, cmbbox 3 is controlled by what you selected in cmbbox 1 & 2? I'd appreciate your help, thank you
Dear Jesus Espiga, not sure what specifically you want to do. Do you have a specific example to post? Dan
Dan, what i'm trying to do is the following:
I have four combo boxes. The first combo box will dictate the options on the second one, second one on the third and so on. I'm able to do that just right but I have the following problem.
I'm able to remove duplicates for the first combo box when I initialize the form, but for the other three combo boxes I'm not able to remove them. Is there a way to repeat removing duplicates in cascading combo boxes?
Could you tell it what you want to be in there using the .additem method based on what's filled in there? Either way it will be tricky because it sounds very specific. But anythings possible when you believe!
ExcelVbaIsFun this is what i have for my program:
Private Sub UserForm_Initialize()
Dim TheAccesory As Range
Dim colList As Collection
Dim i As Long, j As Long
lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set colList = New Collection
With Worksheets("Sheet1")
For i = 2 To lastrow
On Error Resume Next
colList.Add .Cells(i, 1).Value, CStr(.Cells(i, 1))
Next i
For j = 1 To colList.Count
Me.comboType.AddItem colList(j)
Next j
End With
End Sub
' this takes care of removing duplicates in the first combo box.
Private Sub comboType_Change()
Dim colList As Collection
Dim i As Long, j As Long
myval = Me.comboType.Value
lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Me.comboDiameter.Clear
For x = 2 To lastrow
If myval = ThisWorkbook.Sheets("Sheet1").Cells(x, 1) Then
Me.comboDiameter.AddItem ThisWorkbook.Sheets("Sheet1").Cells(x, 2)
End If
Next x
End Sub
This populates the following combobox. The issue i'm having is duplicating the action in the first script throughout the rest of the code. I can't put it in the initialize script, because one could make an error then.
Do you know where you could attempt to write that script?
Thanks
I'm still lost just trying to read this from a smart phone. Can you submit a sample workbook at www.excelvbaisfun.com/contact.html ?
Hello, I have one question for you, I am wondering if you have something that can help me on this. I have some userforms to add records, update records and report for a project.
The project has 5 columns, the column A: Country, B: State, C: Province D: Actions, E: % progress
As you can imagine the A, B, C, D and even E column could have many rows with the same value, example:
Country State Province Actions % progress
Mexico Jalisco Guadalajara Increment Sales 10%
Mexico Jalisco Guadalajara Increment Marketing 25%
Mexico Jalisco Zapopan Start marketing 60%
Mexico Jalisco Zapopan Look for new opportunities 15%
Mexico Jalisco Zapopan Establish a new branch 5%
Mexico Colima Manzanillo Look for New salespeople 10%
Mexico Zacatecas Zacatecas Establish contacts 0%
Mexico Zacatecas Fresnillo Increment sales 15%
Mexico Zacatecas Fresnillo Look for new suppliers 25%
Costa Rica Alajuela Zarcero Establish contacts 40%
Costa Rica Alajuela Zarcero Increment sales 50%
..............
The db (a table in excel is the DB) is growing and growing. The actions in the userform of adding new records are:
Country:
State:
Province:
Actions: Here is the problem, how to add several actions? if I put in the userform 5 textbox maybe the user would like to add 6 or 7 or 8 or more for that specific Country, State, Province.
The same problem for the update record, if I want to retrieve the Actions taken on Mexico Zapopan I know I would need 4 textbox to show the actions but what about if I have 8, 10, 15, or more actions...
I imagine adding textboxes on the fly??
Thanks a lot
Guadalupe Torres 1. you could have them load up one at a time 2. you could have them fill up a temp listbox a few at a time and have that populate to the db when they're done or 3. yes, you should be able to add textboxes programatically but it's no walk in the park. OR 4. if you're dead set on using a gazillion tbs then perhaps you could have the extra ones be already there but hidden (textbox14.visible = false) until you're ready to reveal ( if they need another one ) (textbox14.visible = true) .
thanks for all of your videos, they have all been very use full. As is this one but how could you make the first combobox dynamic as in if i added a new category how would i add the new code with out having to use vba editer. if that makes any sence. thanks again
Thanks A LOT!
Heey what if we write me.combobox2.rowsource = me.combobox1? Wont it work???
I'm copy/pasting my response to Claudio's identical idea below. Good idea Giorgi!!
"Excellent observation, Claudio! That would work in this case, but I wanted people to also know how to customize this, not to limit themselves to items in the first list as named ranges. I didn't think to do it that way also. Thanks!!! Dan"
Bravo That's I needed
Awesome!
so much help
Thanks sooooooooooooo much ~~~~~!!!!
muhteşem olmuş.