I have a list of departments on "Employee Data" sheet on column A and list of staff names on column B. It looks like this:
DEPT FULL NAME
pdi Bob Tan
pdi John Christian
aftersales_bikes Jim Yeo
I have 24 departments in total and 300 over employees in mt employee data sheet.
On "Jan - June 2014 Training Hours" sheet, when employees attend training courses, I will have to update what courses they went for, no of hours etc.
My problem is:
I would like to select the department (using combo box for the auto complete function) and when department is selected, the staffs in that particular department will show. I have attached the file for your understanding. Currently, I am using data validation with indirect function in that file but would like to switch to combo box.
I have a form that an administrator will open to search for items needing approved (ie. status is "Submitted" and they'll be going in to confirmit). The datasource is a table. For these purposes I'll arbitrarily call it Table1 for ease. There are multiple columns in the table, and I'll set the column width to "0" for those I don't want displayed, so we'll call the columns to be displayed Col2, Col3, and Col22. There are two dependents, because Col2 is the Division, of which there are 3 (Central, East, and West). The administator will select their Division and the associated ListBox will display results for all locations having a item status of "Submitted". Col3 will be the branch name, and Col25 with be a total $ amount of the order. Cbo1 (ComboBox1) will house the Private Sub Change() for the macro after a division has been selected.
Thus far I have thought of using a loop such as:
Code: Private Sub ComboBox1_Change() For n = 1 to ws.ListObjects(1).DataBodyRange.Rows.CountIf ws.ListObjects(1).DataBodyRange.Cells(n,2) = Me.ComboBox1.Value and ws.ListObjects(1).DataBodyRange.Cells(n,25) Like "*Submitted*" ThenMe.ListBox1.AddItem = If ws.ListObjects(1).DataBodyRange.Rows(n).Value2End IfNext n End Sub
I've got a mismatch in the the Value2 type for the .AddItem. Not sure how else to approach though.
I created a UserForm then linked ComboBox1 to range A2:A, TextBox2 to range E2:E, and ComboBox3 to range M2:M of the same worksheet, named Sheet3. The row contents in Column A, Column E, and Column M are associated. Therefore, when the UserForm is active I want to be able to select a row from Column A in ComboBox1 and have the UserForm pull the contents from the same row of Column E into TextBox2, and Column M into ComboBox3. Here is what I have so far, but its not quite doing it.
Code: Private Sub UserForm_Initialize() Sheets("Sheet3").Activate Dim ColARange As Range
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.
I'm still curious about the sample from Contextures, http://www.contextures.com/excelfiles.html (DV0032 - Dependent Data Validation Combobox)
I have made a modification from its file (sample is attached). First drop list can be autocomplete, but the second cannot. For the comparing, I made row 2 in ValidationSample sheet is my modification, and row 3 is the original.
I'm trying to create a database where when 3 dependent drop down list selections are combined, information is displayed.
I've tried VLOOKUP and INDEX/MATCH but to no avail.
Attached is the spreadsheet and what I am trying to accomplish. On the first sheet ("budget form") is a table named "Event Costs - room hire". I have created three drop down lists that interact based on what you select. Some rooms offer different services and so are dependent on the selection.
However, I want the final box to display the price depending on what is selected in the previous 3 lists. The amounts and a table I have created is in the "Product Database" sheet and the table is labelled, "Room Hire Control C".
What I am looking to do is this... (using Excel 2007)
I need to have more dependent lists based on the selection in Column A. (My column B is already set up and works perfectly using INDIRECT)
For example: Column F would be a dependent list of colors based on the sales rep chosen in Column A Column I would be a dependent list of managers based on the sales rep chosen in Column A Column M would be a dependent list of part numbers based on the rep chosen in column A
without using VBA? (If I HAD to - okay, the other person maintaining the sheet would be lost....) I haven't been able to find anything close to what I want to do online - maybe I am just not using the correct terms.
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
I have a userform used to input data. I contains textboxes and comboboxes and a command button that is clicked on to record the data onto a worksheet.
What I am looking for is a combobox that is linked to a list of clients (column A of worksheet "Clients").
I would like the user to click on the combobox and select a client name. If the client is new then I would like the user to be able to type in the new name and, then have that name available in the "Clients" names that appear the next time the user clients on the combobox.
I have it almost working, but not quite.
I have a defined name on the "Clients" worksheet as follows:
On another worksheet, I have multiple comboboxes from the Controls toolbar in a column, each having possible selections are based on the data in a column 1 above (ie. A,B,C). When a selection is made in any of these comboboxes, cells to the right of the combobox get populated with the corresponding remaining data from the above cell range - ie. when A is selected, 1,2,3 appears to the right of the combobox, when B is selected, 4,5,6, etc. I want to be able to make selections from within combo boxes, not only by using the combo box dropdown and select mechanism, but also, if possible, by pasting a cell range whose data matches one of the existing selections available in the combobox definition. In other words, if I have a single column cell range from somewhere with the following data in 1 row:...............
I am programming some kind of database in excel and using comboboxes as drop down lists to enter some of the information in the DB. My problem is that when the file is saved, closed and then opened again, all of the comboboxes have lost their selection. I think that using the ControlSource might help but for some reason I can't find the proper syntaxe to make it works. Actually I am getting a Run time error 438: This object doesn't support this property or method.
I have included the relevant portion of my code below. Is the ControlSource property going to solve my problem? If so, what is the syntax I must use? If not, what can I do to make sure that my ComboBoxes are going to keep their selection?
ActiveSheet. OLEObjects.Add(ClassType:="Forms. ComboBox.1", Link:=False, _ DisplayAsIcon:=False, Left:=hori_offset - 220, Top:=vert_offset + 78, _ Width:=180, Height:=24.75).Select With ActiveSheet.OLEObjects("ComboBox1").Object .Font.Size = 14 .Font.Bold = True .Style = fmStyleDropDownList 'Use drop-down list .BoundColumn = 0 'Combo box values are ListIndex values End With With ActiveSheet.Shapes("ComboBox1") .OLEFormat.Object.ControlSource = "Q1" .OLEFormat.Object.ListFillRange = "M1:M8" End With
There are two Combo boxes in the form. ComboBox1 is being populated from a named range - "ParticipantName". (Located in Sheet1!C2:C500) ComboBox2 has been populated with "No" and "Yes" with additem in Form1 Initialize.
There are 3 (Three) Command Buttons - "Save", "Cancel" and "Close".
"Cancel" shall clear data in both Combo Boxes. It is done. "Close" shall unload the Form. It is also done.
My problem is with the "Save" button. The user will select a name in the ComboBox1 and Select "Yes" or "No" in ComboBox2. Then if the user press "Save"; only the selected data of ComboBox2 will be written in the 26th Column (Column "Z") of Sheet1 in respective Row of the name selected in the ComboBox1.
I need to link the textboxes of a form to the appropriate cells associated to the selection made by a combobox. And in the process I need to be able to Edit one of those Textboxes on the fly while the rest will be locked to the user.
Not sure if the editing of the Notes section can be real time of if it must be updated through a button.
In this file, I have a Work Summary worksheet in which I'm extracting some data from the other worksheets to "summarize" all jobs into one nice work summary log On Basis of Average of data from 3 other different sheets A,B,C
The following form works great to if onyl formula calling is applied
I'm using two comboboxes to simplify data query of a database: the first narrows the query by displaying employee names, the second narrowing the selection further by displaying the equipment that employee owns. I am able to populate both comboboxes fine.
The first combo box for employee names:
Code:
Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("User&EquipOverview")
[Code]....
Where I am running into trouble is that I want the selection made with the equipment name combobox (ComboDevice) to populate several textboxes with values related to that specific selection in the database. That is, the combobox population needs to have the cell location included as information to pull the correct information.
Each employee has several pieces of similarly named equipment, so it's not so easy as to search the initial list for the value of the combobox selection.
I have got 8 sheets in a workbook having the following names first one is Main,and others are Aw,Nzm,Mhk,Gul,Qta,and Hdr.
I want a ComboBox in every sheet displaying names of all sheets in combobox and when i select a name of sheet from combobox it goes to that sheet after hidding sheet where selection was made and running macro codes which i have developed for sheet. I want only one sheet display on the screen when one selection is made other one is invisible.
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.
can i use a combo box with a dropbuttonclick to run the macro only when the arrow is selected . The problem I am having is the macro will run on the selection of the arrow which is whatt I want but it also run when I make a selection it the combo box
I have a computer build worksheet I use to configure the cost of my notebooks.
There are 2 worksheets, on one I have all my data seperated in 2 colums. One column is the name say Intel Core-Duo 2500 2.0 Ghz, and next to it the price, say $300.
The other worksheet is the actual form for "building" my systems. Right now I am using combo boxes that feed their output to another cell, then a 3rd cell with complex nested IF statements to retrieve the data from the 1st worksheet.
This way I have the form look like 1. Combobox selection: Intel Core-Duo 2500 2.0 Ghz 2. It feeds a second cell its value 1 (if this is the first value in the list) 3. 3rd Cell reads second cell value with a complex nested IF statement: 1 and goes back to the data sheet and reads the price from the second column: $300
This is a total pain in the butt when I want to add products or change their order in the data sheet. Can someone point me to a direction that this could be made easier, a good book on the subject and what I should be looking for and whatever syntax I need.
Here is the file: [url] You need to have iterations set for the sheet to work properly because there are circular references to calculate percentages of the total for shipping insurance paypal fees and ebay fees etc.
I have created a userform to keep track of "Customer Call Cycle". This is what I have:
1) I have 3 Sales Reps with 50 Customers each. 2) Each customer has multiple contact persons
I have a userform with 2 combo boxex, 1 list box, 1 textbox and 2 buttons.
I want to be able to select Sales Rep from the 1st combobox which will automatically populate the second combobox with customer names related to that sales rep.
and when I select a customer name from 2nd combobox, I want all the contact persons in the database that are related to that customer name to populate in the listbox.
I want to dynamically list items in combobox placed in excel sheet. I have a list of 10 values in a column in excel sheet. I have placed two comboboxes in the same sheet. Now depending on the first combobox selected value i want to list either all or only few values from the available 10 values.
I have a workbook with two sheets. The idea behind the workbook is an Interview Guide to be used just before an Interview. For now my problem is this.
Sheet 2 "Competencies" is just data. It stores competencies with their associated definitions and questions.
Sheet 1 "Control Page" is the sheet where the questions will eventually go. The user (Interviewer) will input data on the first two pages which will include name of candidate, date of interview etc. but they will also select 5 Competencies from the already existing drop down menus on page two. From there as the selections are made I wish for a code to copy the corresponding definition on sheet 'Competencies" to cells lower down in sheet "Control page".
I don't see a place to upload a file as I have a sample of the sheet ready to go.
My data resides in a workbook in sheet2 and sheet3. I have 4 comboboxs refering to different columns in sheet2 and sheet3. Basically when the user selects all the four comboboxs, the criteria should be satisfied and the results in terms of rows matching those values should be shown in the textboxs below.
Each three of the textboxs refers to individual columns in sheet2 and sheet3. All I want to do is simple search criteria on both sheet 2 and sheet3 and put the results in textboxs.
I have populated my comboboxs but dont know the macro to do the search results .
my userform,which has 4 comboboxs
combobox1 = sheet2 column C combobox2 = sheet2 column BL combobox3 = sheet3 column K combobox1 = sheet3 column F
The results which satisfy the above 4 criteria should come from ,when user presses POP UP DETAILS BUTTON,
textbox1= sheet3 column C textbox2= sheet3 column N textbox3= sheet2 column T
Actually I have many records which satisfy the above 4 criteria, dont know how to display them whether in textbox or any another method.
I require is on combobox selection to bring in the data "type" from collumn ag and put it in collumn "c",i then want to get rest of data from the row but get me started I will try and do the other data,do not know how to do formulas ,started first with userforms and would now like to learn formulas
