Series Of Dependent Comboboxes In Userform That Show Unique Values Of Dynamic Tables
Mar 23, 2014
This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.
The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.
Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.
I have three ComboBoxes. I need the choice of the first ComboBox to detirmine what is shown in the second and the choice in the second to detirmine what is shown in the third. The lists will be growing as users add items.
I have a list of purchase order line items. Each Purchase order could have multiple line items, so the list could have many occurances of the same purchase order number. I have designed a pivot table which summaries line items by ordering area (one of the fields in the list).
This works fine but what I would also like to check is the number of purchase orders by area. How can I acheive this? I would prefer not to use any helper columns but will adapt my application if this is needed.
I am trying to find unique values on one page and copy/paste them on another page (all within one workbook). I tried to use advanced filter, but ran on some problems: i) it does not copy values on to the new page ii) it is not dynamic.
my information is entered on a daily basis, which is why I need the filter (or code) to be dynamic. The information, however, is not stored, but rather generates a daily invoice, this is why I want find only unique values to appear on a new page (invoice).
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
For input I have 2 columns: A---B 1---5 3---4 ----- 2---- I need for output column C: C 1 2 3 4 5 It's possible to do that using one or more function ?
I am having some difficulty trying to implement the following. Lets say I have a reference list
X 1 X 2 X 3 Y 4 Z 5 Z 6
I want to have two drop downs on my worksheet. One that filters by x,y,z and then based on the option i select there only give me the corresponding values in the second drop down.
Eg if i select X in my drop down I only want 1,2,3 as options in my second drop down. Or if I select Y only the value 4.
I have two comboboxes on a userform, they both get there list from the same formula. What I am trying to do is have the second combobox have it's selection preset based on the selection in combobox 1.
ie Combobox1 = 6:00 AM when you click on the dropdown for combobox2 i would like 6:00 AM to be the first selection possible, but I dont want it displayed in the box unless it is selected.
Dim timdat1(1 To 85) For i = 1 To 85 timdat1(i) = Format(TimeSerial(5, (i + 1) * 15, 0) - Int(TimeSerial(5, (i + 1) * 15, 0)), "h:mm AM/PM") Next i combobox1.List = timdat1 combobox2.List = timdat1 i am at a loss for where to go from here
I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
I have a userform that has a text box. If user puts a number in it and click on proceed the userform must expand and display that many comboboxes. for e.g. if user inputs 8 and then click on proceed then there should be 8 comboboxes on the form. Is it possible to do?
I have a worksheet "Master Log" with a UserForm "UserForm2" set up that has 4 comboboxes and 2 text entries. I already have the filtered unique values for each combobox sent to columns "O" thru "R" and they are dynamic ranges. I have the code to populate one of the comboboxes in the userform but cant figure out how to modify the code to have all four populated without getting ambiguous entry errors. And also, do I have to initiate the form for each combobox? Below is the code Im using for a single combobox.
Private Sub UserForm_Initialize() Dim MyUniqueList As Variant, i As Long With Me.ListBox1 .Clear ' clear the listbox content MyUniqueList = UniqueItemList(Range("o4:o100"), True) For i = 1 To UBound(MyUniqueList) .AddItem MyUniqueList(i) Next i .ListIndex = 0 ' select the first item End With End Sub
Private Function UniqueItemList(InputRange As Range, _ HorizontalList As Boolean) As Variant Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant Application.Volatile On Error Resume Next For Each cl In InputRange If cl.Formula <> "" Then cUnique.Add cl.Value, CStr(cl.Value) End If Next cl UniqueItemList = "" If cUnique.Count > 0 Then Redim uList(1 To cUnique.Count) For i = 1 To cUnique.Count uList(i) = cUnique(i) Next i UniqueItemList = uList If Not HorizontalList Then UniqueItemList = _ Application.WorksheetFunction.Transpose(UniqueItemList) End If End If On Error Goto 0 End Function
The spreadsheet is essentially a VBA GUI that validates information entered before writing it to a sheet in the workbook. There is a button on the first sheet that opens the GUI, and when the workbook is first used the GUI opens and runs fine. After adding a few rows using the GUI, saving the workbook, and then reopening it, attempting to open the GUI by clicking on the button will cause Excel to crash (and no errors are given). To compound the problem, it is not possible to find the issue by using the debugger, as the GUI runs fine as soon as VisualBasic is opened. I've tried to narrow it down by using MsgBoxes to find the approximate location where the form crashes, and it seems to happen when the .ListIndex property of a ComboBox is set inside the UserForm_Initialise method. I've played around for days trying to narrow it down further, removing .ListIndex statements as much as possible without breaking the entire thing.
I Have a sheet with 4 activex comboboxes and 3 text boxes. If the right item is selected in the second combo box a user form opens up. That has 4 text boxes. It has a command button titled ok that takes the information from the 4 text boxes and puts them in a sheet called data.
Code: Private Sub cmbOK_Click() With Worksheets("Data").Range("A1") .Offset(1, 8).Value = Me.txtFirm.Value
[Code]....
I need a way for the above code to run when the ok button on the user form is clicked.
I have a data dump which is a series of tables, the layout and headers of the tables are fairly fixed however the number of rows in each table is constantly changing. What i would like to do is return the table header, and the average turnaround for each table, preferably without amending the tables. Attached is a sample, these tables marked task C1 task C2 etc are the table headers and in the cell next to Average turnaround is the value i would like to return. I would also like to return the bottom value of each table in column C so at the end it should look like:
I do not want to amend or adjust the table as this sheet continues on for 3000 more tables, which is the reason i am looking for a VB lookup type function.
I would like To display product information in a multi column listbox. Column 1 is a product category and Column 2 is the product description. Now for the dynamic part. I would like to have a combobox loaded with non duplicated values of column 1 of the listbox. When "~Caulking~" is selected from the combobox. The listbox is to display only the "~Caulking~"products and their description. And having a value of "~All Items~" placed into the combobox will display All products in the listbox.
The sheet that will contain the raw data source can/will have items added to inceacing the row count. Also the sheet will be left in an unknown sort by column state. I thought of taking the raw data source and copying it to a new sheet and splitting the data into various columns, Eg 1 column for "~Caulking~" another for "~All Items~"... and then removing the sheet on userfrom completion. Or possably using collections or scripting dictonary or anythink that works well.
I have a Listbox1 in which I was able to populate it with unique items (from column B in Programs worksheet) using a collection (that was a good achievement for me!)
Now, I want to populate a the Listbox2 with items (not repeated) from column C, BUT only if the correspondent value on column B is equal to listbox1.value.
I need to create a 2-stage dependent drop down list.
Stage 1:
In cell G7 (on a tab called 'Data'), I have created a drop down list based on a range name called 'Region' .
Stage 2:
In cell G8, I need to create a drop down list which is dependent on the value chosen in G7.
Problem is, I will need to create a dynamic range name here which needs to vlookup or match the value from G7 in a dynamic list of data (the data is on different tab called 'RCA Data' and is spread over columns A:E with row A for headings - the data will go down an undetermined number of rows)
Every time the value in G7 matches a value in the 'RCA data' (the potential matches are stored in column
D) I need the corresponding cell value in column C to be added to the dynamic list. This list then needs to be available to choose from a drop down list in G8.
I am attempting to create a form in excel with dynamic data validation fields. What has me stumped is that I want to create multiple complex dependencies (for instance, if you select something from a drop-down list in the first field, that then gives you a specific range in the second field. Then based on what you placed in the second field it gives you a specific range in the third field.). What I can't seem to work out is the formula for making this work.
Is this impossible? Also sometimes the third field my be blank.
Here is an example of what I am trying to accomplish
I need the drop down menu to update when the user adds a new entry. For example, if the user decides to add companies to the financial table, no matter how many new rows they add, our drop down menu at the top will update to include the new the entries. I want this to be the case in every table. How do I do this?
Within a userform, I want to populate five combo boxes and with the values of 1 - 10. I looked through several excel websites and found several solutions. However, I could not figure out how to adopt their solutions to my specific case.
My naming convention for the combo boxes are: cmboPeriod1, cmboPeriod2, ... , cmboPeriod5.
[Code].........
I know my with statement is incorrect. I know the compiler will automatically think 'cmboPeriod' is a variable. I just don't know how to get it 'cmboPeriod & j' to do what I really intend for it to do.
I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
I am hoping to have, on another sheet, a series of dependent drop boxes- Year, Quarter, Month, SKU- which after selecting then displays 'dataA' & 'dataB'.
So you could select- List 1: 2013 ----> List 2: Q1 ----> List 3: Jan ----> SD-21.................and then 13% and 0.05% are displayed.
The data will be continuously added to and so needs to be based on a dynamic data range.
I know that a pivot table is the perfect way to do this, however my bosses have requested that I do not use that format so it is easy for all staff to access.
So I think I need to construct some sort of dashboard sheet, I have experimented with OFFSET from other posts, but so far have had no joy.
I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.
The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->
Joe A 1001 Joe A 1002 Joe A 1003 Jill B 1001 Jill B 1002 Jill B 1003
To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.
My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?
Code: Sub AccessList() Sheets("requests").Select Dim rng As Range, c As Range Dim rng1 As Range, c1 As Range Dim rng2 As Range, c2 As Range
I have a graph that auto updates but sometimes there is only 1 series and sometimes there is 2,3,4, or 5 series. Is there a way to have the graph update accordingly? and only have have the series that are valid be in the key on the graph. Currently the graph keeps all the series in the key even when there isn't any data for it.
Please see attachment. Use sheet 2 for the graph. and use the drop down menu to select catagory. this ia very quick mock up of the concept.
