Populating A Combo Box
Oct 13, 2008
I have a combo box in a report that currently updates using this code.
Sub popcombo_Initialize()
With Sheet1.ComboBox1
.AddItem "Budget Card"
.AddItem "Car Insurance"
.AddItem "Charge Card"
.AddItem "Credit Card"
.AddItem "FRS"
.AddItem "Home Insurance"
.AddItem "ISA"
.AddItem "Life"
.AddItem "Other"
.AddItem "Pension"
.AddItem "PEP"
.AddItem "Personal Loan"
.AddItem "Personal Reserve"
.AddItem "Pet Insurance"
.AddItem "Savings Protection Plan"
.AddItem "Travel Insurance"
.AddItem "Travel Money"
.AddItem "Unit Trust"
.AddItem "Wedding Insurance"
End With
End Sub
But some items on this list are missing from month to month and don't need to be included for selection so I need a more advanced version of this that populates from a list in data.
Is there a way for it to look at a column in a data range containing month, then look at the column containing Product and take a definitive list of all products that match a certain month?? So Credit card might be mentioned 300 times for the month of september but it appears only once in the combobox?
View 9 Replies
ADVERTISEMENT
Jun 4, 2009
I tried moving it to a standard module and it will no longer work tells me invalid use of Me. How can I populate these two combo boxes at start up?
View 4 Replies
View Related
Feb 25, 2009
I am trying to find a method of adding a combo box and populate it that works for Excel 2000, 2003 and 2007. The following works for Excel 2007 but did not work on a machine installed with Excel 2003.
With Sheets("Tasks").ComboBox1
.AddItem "By Task Number"
.AddItem "By Date Task Assigned"
.AddItem "By Date Due for Completion"
.AddItem "By Status"
.AddItem "By Status and Date Due for Completion"
End With
View 9 Replies
View Related
Mar 23, 2007
I am trying to write an advanced sorting macro that sorts on more than 3 columns. I need to poulate a combo box with the column headings, so that I can choose which column to sort on first, second etc. Now I am a complete beginner at this sort of thing, but I believe that ranges are required. I have tried using rowsource properties, but the combo box only ever displays one column heading.
View 2 Replies
View Related
Sep 19, 2009
I want to make a combo box in excel that, if i select it the sheet inside the combo box will appear.
Example:
Inside of combox are: Sheet1
Sheet2
Sheet3
If i click combo box and i choose sheet3 the sheet3 will appear.
How can i do this? theirs a macro code to use?
View 9 Replies
View Related
Oct 9, 2008
The first combo box is on a userform so that a subject can be selected
View 3 Replies
View Related
Oct 19, 2008
I have to create two combo boxes and when a user selects a value from one combo box, the values should get changed in the other combo box.
For example : if one combo box has values like "c/c++ programming" "java programming" and so on.. ... i would like to display the authors recommended (corresponding to combo box1) in the combo box 2.
View 6 Replies
View Related
Feb 1, 2007
I would like to create a combo box however the contents of the combo box depends on what i have selected in a previous combo box.
View 2 Replies
View Related
Feb 8, 2007
I have 16 seperate titles and a different figure for each title.
However, these 16 titles are repeated hundreds of times, in different orders, down a column. I need to populate the list with the numbers that belong to each title.
I don't have v look up on my computer. I am useless at excel.
View 11 Replies
View Related
Dec 4, 2006
I have a spreadsheet that continuously is having data input in columns A-F. I want to have a listbox automatically populate each time the worksheet is opened. I would like to have a macro that will search for all rows that have data in columns A-F that DO NOT have data in column L. I would like the macro to populate a listbox when the worksheet is opened with the data from columns A-F of all rows that meet the previously stated criteria.
View 9 Replies
View Related
Jun 1, 2007
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.
Sub get_Locations()
Dim allCells As Range, Cell As Range
Dim noDupes As New Collection
Sheets("Main").Select
' 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)
Next Cell
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
End If
Next j
Next i
' Add items to combobox in their new alphabetical order.
itemIndex = 0
For Each Item In noDupes
UserForm2.ComboBox1.AddItem Item, itemIndex
itemIndex = itemIndex + 1
Next Item
Else ' otherwise there's no list to populate and the default row is 12
endRange = 12
Range("A12").Select
End If
End Sub
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.
View 9 Replies
View Related
Jul 3, 2006
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.
View 6 Replies
View Related
Oct 25, 2006
The students are not being populated on Listbox3 when I select an item in Listbox2. Only the first selection appears and not the others. Thank you in advance. Attached is the image file and the excel file itself.
View 9 Replies
View Related
Jan 7, 2014
Please see the attached sheet, Im trying to get it to populate the first page with the column data in the datasheet1 page by using the dropdown menu in N1. This is for when people ask questions about the data, I can quickly and clearly show them the differences from one month to another
How I would go about doing this?
Digital Delivery reports.xls
View 5 Replies
View Related
Mar 24, 2009
I am looking for an excel calendar that will auto-populate content into it based on a spreadsheet that I fill in.
Maybe I would just need the formula that would be inserted into each calendar day? I'm not sure how you would set this up, but I'm open to suggestions!
I have attached the .xls file for your reference.
For example: The comment "Client 1 - KOM" would populate in a blank calendar in excel on the date 3/23/2009.
View 9 Replies
View Related
Apr 15, 2009
I have a colum with 350 cells in use, each of these cells contain a 3 digit number. Without having to go into each cell and type is there a way i can put the Letter
"R" infront of each of the 3 digit numbers?
View 4 Replies
View Related
Nov 6, 2012
I have an ActiveX Combo box - and Im trying to fill from a 2nd sheet (same workbook). I can get the box to work if the data is on the same sheet but what to I put in the ListFillRange to get it to populate from another sheet ?
View 2 Replies
View Related
Oct 29, 2008
I am trying to total up by a Range of Dates some information from one page to another through VBA. I am having a tough time with this one, I can't seem to get the correct information over to the other page.
I have successfully been able to transfer the data via a series of SUMIF statements, but it really is very inefficient and takes a while when the data repopulated by another program. So I am trying to do it via VBA.
View 5 Replies
View Related
Apr 23, 2009
I am using a excel 97 and have a macro that generated hundreds of work books on a single run. Due to the extensive number of work books, i would like to save all of this into a single folder. I able to create a folder on my macro by using the "MKDIR" command, however I can not seem to save any of my workbooks on that newly created folder, do i have to release that folder first. I get a message saying "Miscrosoft Office Excel cannot access the file (Folder).
The command i am using to save the files:
ActiveWorkbook.SaveAs Filename:=Output_Name, _
FileFormat:=xlNormal
The Output_Name contains the full path including the new folder name and the workbook name. What do you think i am doing wrong or not doing at all?
View 3 Replies
View Related
Oct 6, 2009
I am attaching an example sheet with this. I am trying to populate a range of cells in sheet1(from sheet2) based on value in cell A1 on sheet1. VLOOKUP brings only one cell value, I need to bring in a range of cell values in different cells on sheet1.
View 2 Replies
View Related
Nov 3, 2009
I have a worksheet that I have removed a good number of hyperlinks, using the remove hyperlink function. My problem is that every so often, the hyperlinks are back on the sheet, without seeing any wording for the hyperlink.
Some place, a long time ago, I saw that there was something in the program that either stopped this from happening or turned the function off or a way of restoring removed hyperlinks. Today, I can't find it.
If I run my cursor over the spreadsheet, even over blank cells, I will get the evil finger of fate (so to speak) indicating that there is a hyperlink in that cell and yes, there is. If I click the cell, the site will be activated.
How do I keep these removed hyperlinks from returning.
I've also noticed that at anytime, while working on the sheet, everything flickers just as if the links are being re-populated. After a second or two the flickering stops and yes, the links are back.
View 12 Replies
View Related
May 5, 2007
I am trying to make sheet that will give me averages from different groups within a worksheet. In E64 I would like to have the total that is in I4 unless there is a total in I24 or I44. When I made the worksheet, it figures everything great but when I remove the data on following weeks it messes up the averages.
View 10 Replies
View Related
Dec 19, 2007
I am trying to link one table 1 with another table 2, and these are in different tabs.
In first table (1) I have to do some calculations, and in the second table (2) I have contacts (name, fax and contact name).
So I want from first table once I write the name of Trade (which I did with data validation to pick only the trades that are in table 2 from A1 to A50) to populate table 1 - columns B with Contact Name and Column C with Fax # and all these data should come from table 2.
I tried with Vlookup but the problem is that I can fill only column B and I need to fill column B and C in the same time?
Is this possible in Excel or the only way is to go to MS Access?
View 9 Replies
View Related
Dec 6, 2011
I have an array which I am attempting to use a simple Product function on. If one of the cells does not have a number, but instead contains 'N/A' or some other type of text, how do I get my Product function to result in 'N/A' rather than having the Product just ignore the text.
I cannot attach an XLS file but just pretend Cells A1:H1 read 1, 2, 3, N/A, 5, 6, 7, 8.
View 7 Replies
View Related
Jun 22, 2012
creating / populating a table. In the table below, the left half is the data I currently have. I need to wiggle it around to look like J10.
Basically, I need a formula that I can plug into every cell in Column J that says at the value to the immediate left in Column I. Take this value and look at Column E... then every time there is match, take the value in Column D, and find the next match. Put pipes between each value.
Basically, I am trying to get a row for each parent ID that has all the children in one cell delineated by pipes. I am trying to get a row for each parent ID that has all the children in one cell delineated by pipes.
*
D
E
F
G
H
I
J
7
Spreadsheet I have now
*
*
*
*
[code]....
View 3 Replies
View Related
Apr 23, 2013
A1:A10 contains numbert 1 - 10 (A1 = 1, A2 =2 etc.)
B1:B10 contians letter A - J ( B1= A, B2 = B, B3 = C etc.)
Condition: If the Value of cells in Column A is greater than 1 Then read the adjacent cell's value into an array (eg. MyArray)
I was able to use Dictionary object to read everything and then dump into an temp array
Is it possible to populate the temp array values into spread sheet wiout looping through each item?
The following code simply populate the first item in the temp array into each cell in the the destination
Code:
Sub MyArray_Test()
Dim MyArray As Variant
Dim i As Long
[Code].....
View 2 Replies
View Related
Nov 3, 2013
Ok so I have made a self populating Data Table to show me which employee is on what shift that updates daily from our roster.
This works and it's brilliant, however I want to steam line my finished product further, but haven't been able to find an easy solution.
I have several employees per position and my data table currently self populates so that every employee has their own line. I want to populate it so that all employees with the same position all apear in 1 row across the columns under their current shift.
So this is what my end result currently looks like
Table 1POSITION
SHIFT 1
SHIFT 2
SHIFT 3
POSITION 1
EMPLOYEE 1
POSITION 1
EMPLOYEE 5
[Code] ......
This is how I want it to look.
Table 2POSITION
SHIFT 1
SHIFT 2
SHIFT 3
POSITION 1
EMPLOYEE 1
EMPLOYEE 5
EMPLOYEE 8
[Code] ......
We have upto 250 employees and upto 50 positions so you can understand why I need to stream line the table.
I can not change the roster as it is our orginal data as requires to be set up as it is. When adding a new employee into the roster I must enter them into there designated crew. "inserting a row midway down the original data".
If I use the CONCATENATE formula and add a new employee into the roster "half way down the original data" then it throws out what the CONCATENATE formula is looking up. It looks up the same cell but the names have all moved down by a cell therefore the populated information is wrong.
I am hoping to make my end result look like Table 2 even if it means adding another spread sheet in somewhere between the roster and the end product.
Realisticly I want a Pivot table that produces names not a tally of employees per shift.
View 5 Replies
View Related
Feb 27, 2014
I have two types of lists that has to be populated into comboboxes. First type is in a column with a header - lets say A1:A20. For them I`m using a following script:
Code:
For Each cell In ThisWorkbook.Sheets("NACE").Columns(1). _
Resize(Rows.Count - 1).Offset(1). _
SpecialCells(xlCellTypeConstants)
UF1.industry.AddItem CStr(cell.Value)
Next cell
With them all is fine until I need to start for example from row 26. (lets say range to populate is T26:T32) Then I`m lost. changing Offset(1) to Offset(26) doesn't work and I`m getting an error. That however can be handled with a workaround - creating another list that starts from the top. So I gave up on solving that. I need to populate a list from a range that is a row - B5:B20
I've adjusted the script to:
Code:
For Each cell In ThisWorkbook.Sheets("prod").Rows(5). _
Resize(, Columns.Count - 1).Offset(, 1). _
SpecialCells(xlCellTypeConstants)
UF1.PS.AddItem CStr(cell.Value)
Next cell
Because of some strange reason the script doesn`t see the number 1 in cell B5 and unless there are some more numbers in the following cells, it gives the "no cells found error".
View 9 Replies
View Related
May 18, 2008
I want to populate a drop down with items in an array (aMasterList) which contains a list of arrays (aList1, aList2, etc.). When I select an item from the drop down, I want to populate a List Box on the same form as the drop down with items contained in the corresponding named array. Here’s what I have:
UserForm1
DropDown1
ListBox1
Sub PopulateForm()
Dim aMasterList(), aList1(), aList2(), aList3()
‘Load into DropDown1
aMasterList = Array("aList1", "aList2", "aList3")
‘User selects from drop down and the below items populate ListBox1
aList1 = Array("Green", "Blue", "Yellow")
aList2 = Array("Apple", "Pear", "Grape")
aList3 = Array("Flowers", "Trees", "Bushes")
End Sub
View 9 Replies
View Related
Oct 10, 2008
I need column B to self populate cells with "1" if a specific range of room numbers are entered in column A. Is this possible?
Column A, room numbers from all over hospital.
Column B auto populate "1" if rooms ranging from M570 to M590 in Column A
View 9 Replies
View Related
