Populating Combobox 2 With Items That Match Criteria From Combobox 1
Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.
if column a = bears and column b = colours of bears then
when I select bears in combobox one, combobox 2 would populate with colors of bear.
I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.
I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.
View Complete Thread with Replies
Related Forum Messages:
ComboBox Not Populating
I have a combobox that is not populating correctly. The design of it uses an existing list (which changes every month) to populate the contents of the combobox. I want the user to be able to select a location from the list if it is already on the worksheet, rather than having to key in the location name for every single record. This Sub worked just fine and dandy for me until I deployed the workbook to the end user. Then this particular combobox decided not to work.
I have been over this code many, many times and I am almost positive that it's just a matter of my not being able to see the obvious. Perhaps another set of eyes looking at the code will be able to see what I can not.
Dim allCells As Range, Cell As Range
Dim noDupes As New Collection
' get the first from last row #--the last row is a row of record counts and sums.
endRange = ActiveSheet.UsedRange.Rows.Count - 1
ComboBox1.Clear ' This will clear the combobox.
If endRange 65536 Then ' If the row number is not the end of the worksheet, _
there must be something already on the worksheet, so build the list.
On Error Resume Next
' Get unique values in range by checking each cell in range.
For Each Cell In Range("D12:D" & endRange)
noDupes.Add Cell.Value, CStr(Cell.Value)
On Error GoTo 0
' Sort the collection alphabetically within the combobox
For i = 1 To noDupes.Count - 1
For j = i + 1 To noDupes.Count
If noDupes(i) > noDupes(j) Then
Swap1 = noDupes(i)
Swap2 = noDupes(j)
noDupes.Add Swap1, before:=j
noDupes.Add Swap2, before:=i
noDupes.Remove i + 1
noDupes.Remove j + 1
' Add items to combobox in their new alphabetical order.
itemIndex = 0
For Each Item In noDupes
UserForm2.ComboBox1.AddItem Item, itemIndex
itemIndex = itemIndex + 1
Else ' otherwise there's no list to populate and the default row is 12
endRange = 12
And just for the record, all object references (cell ranges, comboBox, etc.) are valid and correctly named, so I know it's not there. I also recognize that I have overspecified things by using "UserForm2", but I have done that in the hopes of finding the problem. And the code does execute at the proper times, populating "noDupes" correctly. The problem seems to occur near the end, in the block where noDupes is added to ComboBox1.
I have a spreadsheet with loads of different columns on it, I am using a userform to control the spreadsheet. I have one form that allows me to add data to the spreadsheet and another that allows me to search it.
If one of my colums was a list of "counties" what I want to do is to have a combo box that displays all the counties listed so I can pick a specific one and and search it.
This I can do, the problem arises if a new county is added to the spreadsheet it will not be displayed in the combobox because I am using the additem "....." function to populate the box.
Populating A Combobox Manually
I'm sure there is an extremely simple answer to this question, but I can't seem to find it anywhere.
I am adding a combobox to a userform using the controls toolbox. How can I populate the combobox without a macro?
I do not want to add extra code to the UserForm_Initialize() event because this would just add clutter (I have seen numerous examples using .AddItem)
I would like to just change the combobox by hand.
Combobox Not Populating Correctly
I have a userform with a combo box that is populated from a list in a sheet called Clients. The userform is in the same workbook as the Clients sheet. This workbook called "Client List.xls" and is set to be hidden from view. I've created a menu bar icon that loads the "Client List.xls" workbook, and on open runs the initialize procedure. The problem seems to be caused by the "Clients List.xls" being not the active workbook. Is this the case? Is there a way around it? If I unhide the workbook and make it the active workbook, the code works great.
Private Sub UserForm_Initialize()
Dim rngClients As Range
Dim wsSheet As Worksheet
Set wsSheet = Workbooks("Client List.xls").Sheets("Clients")
Set rngClients = Selection
.RowSource = rngClients.Address
.ListIndex = 0
Populating Worksheet Column From Combobox
I have a userform with comboboxes and listboxes etc. The comboboxes have drop down lists which are populated from data in columns from a worksheet.
1.) During the execution of the userform, a value is selected from the combobox, which is place in a column on another worksheet. So far so good. If I select the same value again, nothing shows up in the row below the first selection. If I choose a different value it shows up. I have tried using different properties of the combobox but have not been able to get around this.
2.) I would like, when the excel file is opened that the userform is ready to go, ie enter data. I have tried putting the
Private Sub Workbook_Open()
in "ThisWorkbook" module but have had no success with it.
Populating Combobox Based On Selection Of Another
I have a problem involving two combo boxes in a user form.
I need to populate different “lists” to combo 2, based on a selection in combo 1.
I browsed through the forum but could not find any solution that made sense to me.
Here are the specifics:
Combo 1 (called Cbo_Act) is populated with a list based on a named range in one of the sheets. The range is named “activity” and holds 2 records (“Income” & “Expense”).
This list is loaded as part of the form initialization.
If the user select “Income”, I need combo 2 (called Cbo_Act_Type) to show a list of various income types (derived from a dynamic named range called “Income_type”) and if the user selects “Expense”, I need the same combo box (Cbo_Act_Type) to show a different list, specifically – a list of various expense types (derived from a dynamic named range called “Expense_type”).
I know that this should be with a Cbo_Act_Change () routine, but for the life of me, I can’t figure out how to do it.
Populating Combobox On Userform With A Range
Selection.ListFillRange = "='Sheet1'!$b$15:" & comborange & ""
where comborange = cell reference of the last cell in my range..
I have moved this combo box to a userform now and I'm trying to populate the box via one of the userforms triggers.. My problem is I can't figure out the syntax to select the combobox on the userform.. it doesn't seem to be handled the same way as when it was on the worksheet..
Add Same Items To 10 Different Combobox
The below code shows the way I add items to 10 different comboboxes. However, the items to be added are actual the same.
Is there a way I can simiply it? I am worry that by the end of day, i might have up to 50-60 comboboxes and the code will be a relativity long and not efficient.
Taking into consideration the the comboboxes will be labelled as Jieyi_task_ComboBox1...to 10
Kenny_task_ComboBox1.. to 10
Adding Items To Combobox
In my excel sheet, from B2 to B20 i have datas like,
I want to add this to my combobox1 with out repeation as, 'aaa' should be listed in combobox once. What is the macro for this or we can do it in Combobox properties itself?
Alphabetize Combobox Items
I have a significant number of comboboxes on a multipage userform. Almost all of them are populated dynamically (it takes the user choice from one combobox, looks for that value on the spreadsheet and if it finds it, it takes whats in the next column and adds it to another combobox).
My problem is that all the items in these other comboboxes are not in alphabetical order. I'd like to find some code that will work for all my comboboxes so that I don't have to have the same code over and over again for each field.
I found the code below and that works to alphabetize the dropdown, but I still have to use it over and over again and change it for each fieldname (in red). I tried to use a variant and just make the value the same as each fieldname before it calls the sub and whats in red below was just my variable but that just crashes my excel.
Items From Sheet In Combobox
C Attached file.
I want the items that appear in B9:B5000, automaticaly in Combobox_Category. How can I do this, so that every item stays unique.
example B9 = fat cow and B10= fat cow, fat cow will only appear 1 time in the combobox lista and not twice, how can I organise the combobox list Alphab?
Find Range For Items From An Combobox
I have an workbook with two worksheets: sheet1 and sheet2. In first sheet I have an combobox (with data from sheet2, range A2:end xldown). In second sheet I have:
column A: item
column B: item with range
column C: range
I need to select one item from combobox (for example 3) and after that to select a cell (for example D3).
In sheet2, column A, I need item 3 to be deleted.
In sheet2, column B, I need item 3 to be added.
In sheet2, column C, I need the selected cell (D3) to be added and so on for all the items!
Add Unique Items To ComboBox Control
I have a database of data that looks like this
Centre Name Training Type Expert 1 Expert 2 Expert 3 Expert 4
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre OtherTraining Me
NewCentre NewTraining NewPerson
I am trying to create a user form (in VBA) with a combo box that has each centre name appear only once (despite the fact that in the database each occurs multiple times). When a specific centre is selected, this brings up all the different training types associated with that centre in a separate list box. When that training type is selected, it should bring up all of the experts in that training type (for the specific centre). I should mention that the range is static (though the data is always contiguous) as the database is updated on a going forward basis.
Populate Combobox List With ONLY Unique Items ()
Populate Adv Filter Criteria from Inputbox with valid data
I've set up a macro to extract 3 different sets of data using Advanced Filter (same data range, different criteria & extract ranges)
There are multiple options (different individual dates) to satisfy the criteria (a date) for each of the 3 extracts and I want the user to select the criteria (a specific date) from the range of unique values in the data range upon which the Advanced Filter criteria is going to be applied. (A bit like the effect provided by Autofilter)
My initial attempt was to capture the criteria date from the user and populate a cell, and then have a vlookup function in another cell check if that crieteria value exists in the specified range. I then ran a loop in the VBA code until the value is valid (ie. exists in the range). However, to streamline this I was hoping to allow the user to see a list of the valid (available) dates when they respond to my Inputbox request for the criteria.
The ComboBox I Wait About 1-3 Seconds For About 40 Items In The Worksheet
My question is if it's possible to change the code and make it work faster. When I change the ComboBox I wait about 1-3 seconds for about 40 items in the Worksheet. I still didn't test it with more items. Here's the
Private Sub ComboBox1P2_Change()
If ComboBox1P2.ListIndex = 0 Then
ComboBox2P2.Style = fmStyleDropDownCombo
ComboBox2P2.Value = sign
ComboBox2P2.Enabled = False
ComboBox3P2.Enabled = False
ElseIf ComboBox1P2.ListIndex 0 And ComboBox1P2.ListIndex -1 Then
ComboBox2P2.Style = fmStyleDropDownList
ComboBox2P2.Enabled = True...........
Sort ListBox Or Combobox List Items
The code for sorting a multi-column listbox in Retain Selection After Sorting ListBox was really good, and I've used it a lot in an application I'm building.
What I'm hoping to do is reload a listbox in such a way that the items appear in the same order they were previously in. I'll describe two scenarios:
1. User edits a record
- user sorts listbox
- user selects a record
- user edits record
- listbox reloads, unsorted
2. User adds a record
- user sorts a listbox and sees a record is missing
- user adds the missing record
- listbox reloads, unsorted
ideally the last step for each would be "listbox reloads, sorted" and the user would go on down the list. the tricky part i think is when rows are deleted or added.
i'm starting on a solution, but if there's some existing code that will do this i'd appreciate if someone could point me to it (because, for example, the listbox sorting code i referred to above anticipated things i did not).
Combobox Validation: Only Listed Items Can Be Chosen/Used
I have a Combobox with it's 'RowSource' set to two columns x 1500 rows on a spreadsheet. The Combobox is set to 'fmMatchEntryComplete'. When the user types in invalid text I get an "Invalid Value Property" error. I would like to validate the Combobox so that the user cannot type text other than available in the list, or not allow for the focus to be taken away away unless the item is a match to the list. At present due to the interaction of other controls on the userform, the only way to clear the error is by pressing the 'Escape key'. I have a button designed to reset the 'RowSource' of the Combobox but even after adding a 'MouseMove' event to this button with code
List Box Depending On Combobox And Add Or Delete Items To Range
i had a user form with a Combo box,list box,text box and a command button. I need the code that works upon selecting:
1)An Item from the combo box should display the list in the list box.
eg: If Country is selected from the combobox then the list box should contain all the names of the countries from the country Column
2)An item should be added to the list in the worksheet when an item is entered in the textbox.
Like wise, when an item is selected from the combobox from "Delete Items List" all the items relating to the item selected from the combobox should be displayed in the list box and a choice to delete the items relating to the combobox item should be provided.
Populate Combobox Based On Selection Of Other Combobox
I have a table, headers "FirstName" and "SurName".
Further a Userform with 2 Comboboxes "FirstName" and "SurName"
I'd like to choose the FirstName (say Jack) in the "FirstName" combobox, and based on that get the choice of the Surnames of all my Jacks in the "SurName" combobox.
Actually my sheet has much more fields and comboboxes, but i think my problem is just that I do not find a way to populate them dynamically.
Combobox Match Entry Property
I have a ComboBox (cmbJobNo) in excel which lists a series of numbers. By using the code below other TextBoxes are automatically filled with related text once the ComboBox number is selected.
On typing a number in the ComboBox the number is predicted, which is great except when a number is not sequential. Say the number is 15304 (the next number in list is 15315), when 1530 is entered, 15304 is predicted and VBA jumps to the next data entry Textbox, the data entry person then enters 4 (they type a lot faster than me, without looking), which is incorrect for that box.
VBA does not allow the complete number to be entered. If I turn off MatchEntry the other related textboxes txtClient and txtProject) do not automatically update when number is entered.
What can I do to allow complete number to be entered and related Textboxes updated after number entry?
Private Sub cmbJobNo_Change()
If cmbJobNo.ListIndex > -1 Then
txtClient = Format( Range("Jobs").Cells(cmbJobNo.ListIndex + 1, 2), "0") ...............
Generate Result That Match Combobox Selection
I have 3 sets of table in a sheet, namely, BK,DC & BDM. Each of the table to shows the result (extracted from other sheets) for 12months and the rating that correspondence with the result. There is also 2 combobox and a commandbutton at the bottom of the sheet. The combobox1 is to select the item (BK,DC or BDM) and combobox2 is to select the month. This is because i need to create something whereby, once the item and month is selected, then the user is able to click on the commandbutton and a small window will appear to indicate the year to date result (extracted from the tables)
For example, if the user select BK in combobox1 and April in combobox2, then when they click on the commandbutton, the following happens: -
1. Calculate the average percentage of the performance from Jan - Apr (I'm using the Excel formula for step 1 & 2)
2. Generate Rating
3. Populate the result in a small pop-up window which has the header as per what is selected in combobox1.
Match ListBox/ComboBox Entry As User Types
I have searched the forum but can't find an answer to my problem. I have a list of about 3000 streets, a sample of which follows:
ASHLEY GROVE CT
I know how to populate a List Box, but rather than having to scroll through the entire lot I would like to just have to type in a few letters and the output only display streets that start with only those letters. For example, if I type in AS only the following appear in the listbox.
ASHLEY GROVE CT
Autofilter With UserForm ComboBox & TextBox As Criteria
I have found codes to do this however not one to show if I have varied selections. I have 3 combobox's and 2 textbox's to autofilter data on the same page. can someone tell me where I am going wrong in my code.
Private Sub CommandButton1_Click()
.AutoFilterMode = False
If Me.ComboBox1.Value = True Then
With . Range("B2:S2")
.AutoFilter Field:=2, Criteria1:=Me.ComboBox1.Value
If Me.ComboBox2.Value = True Then
.AutoFilter Field:=4, Criteria1:=Me.ComboBox2.Value
Fill ComboBox With Cells Meeting Criteria
Well, after reading "Best Practices", it looks like I've got lots to learn about Excel and VBA Hope I don't break too many rules. see code below. I have a form that uses a set of dynamically filled combo boxes. The difficulty I'm having is in filling the combo boxes with correct data when updating an existing record. As per the code below, the Activecell. offset(0, #) references in the "if" statement in the For..Next loop process correctly, but Activecell references that fill the combo boxes seem to behave like an absolute references to Row 1, which are the Column Names. I've tried creating an Activecell.offset(0, 0) before every fill combo box statement and then using Activecell.offset(i, #) to reference the relevant cells, but to no avail. Obviously, I'm missing something here...
Private Sub cboUserName_change()
Dim usrnam As String
Dim found As Boolean
Dim i As Integer
Dim intRowCount As Integer
usrnam = cboUserName.Value
found = False
If usrnam <> "" Then
intRowCount = Range("A1"). CurrentRegion.Rows.Count - 1 ................
Vlook Up: Combobox Shows The The First Column (only 1 Of Each) And The Second ComboBox Shows Me The Secondary List
I have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
Lookup (counts The Number Of Items Which Match Criteria Set By The User)
Though i wanted to rid the Na error from it when C is blank
I seen a few ways, And wanted to try the method of using Countif becouse it can be used withen the same cell without using extra cells like another method i seen "=IF(ISNA(A1),0,A1)"
what i know of countif
This function counts the number of items which match criteria set by the user.
so how the heck can the countif function be used to rid the NA of a lookup. i just cant see a relationship between counting a lookup.
Index & Match Multiple Items/Criteria: Finding The Nth Occurrence
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
Using Combobox 1 To Filter Combobox 2
I have 2 comboboxes on a userform. In combobox1 I have a list of years which is pre-populated. In combobox2 i have some data. What i'd like to do is to sort combobox2 using the year selected from combobox1.
I have tried this slightly modified code from a different project, but cant get it to work correctly.
The code below autofilters, and selects the correct range, but will not populate the userform combobox... the highlighted row seems to be the problem:
Dim rngToCopy As Range
.AutoFilterMode = False
.Range("B:B").AutoFilter field:=1, Criteria1:=ComboBox6.Value
On Error Resume Next
Set rngToCopy = .Offset(1, -1).Resize(.Rows.Count - 1).Resize(, 3)
On Error GoTo 0
If rngToCopy Is Nothing Then MsgBox "No projects currently set up for the selected season!...": Exit Sub
.AutoFilterMode = False
In my data, i'd like to return columns A, B and C.
Populating Multiple Cells By Entering In Criteria
I am adding a sheet with a graph I made. There are two sheets, the first with the graph, the second with tables. What I would like to do is populate the graph based off what is entered into the tables. My main goal is to get the cells in the cells in the graph for each day to fill in color between the given time frame entered in on the table. Obviously I would be rounding up or down to make the time end in ":00."
The color I would like to fill the graph with is shown on the graph sheet. Also, if anyone can actually get this to work (I honestly wouldn't know where to start) Is there a way to have multiple colors and a possible overlay of colors?
Lets say on tuesday I have 10:00-15:00, and 12:00-18:00. Can I have two colors populate the cells and a thirs color populate the cells in the overlay which would be 12:00-15:00?
Populating Cell Only If Multiple Criteria Matches ...
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.
The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.
I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:
(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)
I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
VBA - Another ComboBox
I'm populating a combobox with the data in 3 columns.
Question 1: The range has 3 Columns though my combobox is only showing 1 how can I get it to show all 3?
Question 2: I'm sure this would be much better served by a loop, populating the combobox, any examples?
For Each c In Range("DefectID").Rows
Me.Defects.AddItem Cells(c.Row, 3)
I'm having trouble with my combo box which was created using the ActiveX controls. The reason for wanting to do this is I want the combo box to be able to capture the values in range when the spreadsheet is updated without having to manually updated the listfillrange everytime
Code is below, and the red highlighted line is where the error is occuring
Private Sub update1()
Dim i As Long
Dim col As Long
i = 1
col = 1
Do Until Cells(i, col).Value = "Totals"
.ListFillRange Cells(i, col).Value
Add ComboBox Value To Next Available Row
I have some code that finds the first available blank row and then finds the correct column based on the value of "JobBox1" on the userform. Right now if it doesn't find the value of "JobBox1" in my range (row 3) it errors out. What I would like to be able to do is if it doesn't find the value it would add the new value to the first available column in row 3. This is the code I was going to use to find the first available column in row 3.
Set LastCol = Worksheets("Database").Range("IV3").End(xlToLeft).Offset(0, 1)
This is the code I am using to find the row and column and write the value to the cell.
TheRow = Worksheets("Database").Range("a65536").End(xlUp).Row
TheCol = FRange.Find(Me.JobBox1.Value).Column
Worksheets("Database").Cells(TheRow, TheCol).Value = Me.JobBoxHours1.Value
Auto Populating Columns In One Sheet From Another Based On Criteria
I've been trying for some time now to figure out how to have one sheet automatically fill in another based on a larger data sheet. I'm trying to find a way to take a monthly calendar which contains a row for each employee and column for each day and use that to create smaller lists on another sheet. Basically if someone puts in for a day off, or has a medical appointment etc, that person has a coded single letter for that day for tracking and planning purposes. I want to have a second sheet that references those codes and makes individual lists (the following people are on vacation, these people have a medical appointment etc.)
How Do I Initialize A ComboBox?
I figured out how to populate the ComboBox, but I have to manually step through the code to do it. How do I get it to populate the values when the spreadsheet is opened?
Then, what I want to do is have the cell formula depend on one of two selections in the ComboBox.
Here's my code for the box.