Multiple Selections From Listbox To A Single Cell?

Aug 9, 2013

how do I save multiple items selected from a userform listbox to a single cell on a worksheets? How do I have those values display in a listbox on a userform for editing data sets?

View 2 Replies


ListBox: Allow Multiple Selections

Oct 9, 2006

I'm trying to see if it's possible to create a list box where I can select multiple values. For example, if a create a list box with the alues, "ant", "dog", and "cat". Is it possible for me to give the user the option to choose, "ant" and "cat"?

View 2 Replies View Related

Extracting Multiple Selections From Multi Column Listbox

Apr 1, 2014

I have a multi selection listbox that has 6 columns located on the "Form" worksheet. Trying to extract the data from the selected lines (and all columns for the selected line) and copy to another worksheet (ExtractedData). My code so far only works to extract the multiple selections for the first column. Not sure how to have it include all columns. Ideally would like to have the six columns to be extracted and placed in separate cells on the ExtractedData worksheet. Here is what I have so far:

SelCnt = 0
With Worksheets("Form").ListBox3
For i = 0 To .ListCount - 1
If .Selected(i) Then
SelCnt = SelCnt + 1

[Code] .......

View 3 Replies View Related

Excel 2007 :: Write Selections From Listbox To Cell?

Oct 9, 2011

When I attempt to extrapolate and modify the code I get nothing but error messages. I've also looked through my two Excel 2007 books (each of them 1,000+ pages long) and there is no mention of this subject anywhere.

I am running Excel 2007. I have a multiselect listbox in a worksheet (not a user form). I want a user to be to be able to select multiple items from that listbox and when the the user moves out of the listbox (i.e., when the focus is no longer on the listbox), I want the selections to be copied into a cell on the same worksheet with each of the selections separated by a comma.

So for example, if the listbox (named "ListBox1") contained the following items...


...and the user selected both "North" and "West" from the listbox, the following text would get automatically written into cell A2 in the same worksheet after the user move outside of the listbox...

North, West

I know enough VBA to be dangerous (usually just tweaking code written by the macro recorder), but this one is eluding me. My two questions are:

1. What is the VBA code required to give me the result I'm looking for?
2. How would that code be called/where does it reside in the workbook? For example would I put the code in a macro and associate that macro to the listbox object??

View 6 Replies View Related

Listbox Selections

Apr 19, 2006

I have a userform with multiple buttons and a listbox. When any button is clicked on, a listbox is created. The user selects various items on the listbox. Then the user clicks another button and selects other items based on a new list. If user goes back to click the original button, the original list shows up, but the selected items are not highlighted. Is there any way to keep those original selections highlighted (selected)?

View 9 Replies View Related

Excel 2010 :: Add Single Record To Multiple Column Listbox In A Loop From Array

Mar 19, 2014


I have an array PeopleList(6,320) that contains

PersonID, FirstName, LastName, Email, Phone, Notes

What I'm trying to do. I have two separate requirements:

(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()

I have two errors:

Error 1 in UserForm_Initialize()

The listbox contents need transposing! It is displaying as

1 2 3 4 5
Tom Ben Heidi Julie Mark
Smith Jones Evans Simpson Petersen
02071001022 02071001026 02071001027 02071001028 02071001029

When it should be displaying as
1 Tom Smith 02071001022 Friend
2 Ben Jones 02071001026

Is there a way to transpose the array?

Error 2 in txtSearchTerm_Change()

I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:

For i = 0 To UBound(SearchList) If InStr(1, SearchList(i), SearchTerm) 0 Then
With lstPeople
For j = 0 To UBound(PeopleList, 1)
.List(c, j).Value = PeopleList(j, i)

[Code] ......

How do I add a record to the listbox????

Full code for reference:

Option Explicit

Private PeopleList As Variant
Private SearchList As Variant
Private Sub UserForm_Initialize()

[Code] .......

View 5 Replies View Related

Listbox Multiselect Selections

May 2, 2006

On a multiselect listbox, is there any way the user can select a "block" of selections, instead of having to select them one by one? The list box I have has about 100 choices and it would be possible to select blocks of choices at a time. This is similiar to MS Outlook email where you can push the shift key to isolate a block of emails to move to a different folder.

View 2 Replies View Related

Remembering Listbox Selections

Jul 31, 2006

I have a workbook with 3 listboxes: Listbox1, Listbox2 and Listbox3. Selecting an item in Listbox1 will query a database which populates a hidden worksheet. Listbox2 then populates itself by drawing on entries in the hidden worksheet. Selecting an item in Listbox2 populates Listbox3 in the same way.

At the moment I have the spreadsheet so that nothing is cleared when I close or open the workbook, allowing the listboxes to remember what they were populated with when I closed the workbook. However, the listboxes will not remember what was selected when I closed it. Is this something which I can easily fix with a listbox property? If not, what is the easiest way for each listbox to remember what was selected when I closed it?

View 6 Replies View Related

Uncheck ListBox Selections

Dec 13, 2006

I have a form with 3 checkboxes. When any of the 3 checkboxes are checked, a set number of items are selected from a listbox. If a checkbox is checked, and then a new checkbox is checked, the listbox selection is cleared and selected with the new values from the new checkbox.

Problem: How can I code this so that if I check a checkbox and then deselect that same checkbox, my selected listbox items are cleared? Only when I select a different checkbox are they then cleared and repopulated.

Private Sub CheckBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim lngitem As Long
For lngitem = 0 To ListBox1.ListCount - 1
ListBox1.Selected(lngitem) = False
CheckBox1.Value = False
End Sub..............................

View 2 Replies View Related

Listbox Selections Into Worksheet

Jun 21, 2007

Here is what I'm trying to create: I've put a command button on my worksheet that pops up a user form with a list box, an "insert" command button, and a "close" command button. I want to allow a user to make multiple selections in the list box, click the "insert" button, and have the selections inserted into the worksheet at a specific cell and fill down. I'd also like for it to allow the user to re-open the user form and add to the list after closing it.

I've hap-hazardly set up the list box for multi-select, but I'd like to see how it's "supposed" to be done! Also, as it stands, my list box selections will populate any cell that happens to be active at the time the initial command button is clicked. I've searched this forum and found that folks have posted similar questions. Those who replied posted a link, which I tried to follow but the page that followed could not find the file.

View 5 Replies View Related

MultiSelect Listbox Selections To Cells

Jul 23, 2009

I have a MultiSelect Listbox with two columns of data and I want to enter the listbox row data into different cells of a sheet. For example, the selected listbox first column data to be entered to Sheet1, activecell and rows below (for other multiple selections), and the selected listbox second column data to be entered to Sheet1, activecell(0, 2) and rows below (for multiple selections).

View 2 Replies View Related

Storing ListBox Mulitselect Selections

Nov 30, 2006

I am writing a macro that allows a user to select which fields they would like to copy from over 100 workbooks. I am using a multiselect ListBox on a userform that I populate using the "additem" approach. There are around 20 fields for the user to choose and each represents a column in the worksheet (all 100 worksheet are in the same format).

I need to use the user selection to identify which columns to pull from all worksheets.

How can I identify the selection the user made and incorrporate that in a copy range type of code. I planned to "Unload" the form after the user hit the "Run" (command) button (not sure if this will matter).

View 9 Replies View Related

MultiSelect Listbox: Counting Selections

Aug 31, 2006

I've got multiselect option 2 enabled: fmMultiSelectExtended and I want to be able to count up the total number of rows the user has selected.

View 9 Replies View Related

Keeping Listbox Selections After Calculation

Mar 17, 2007

I have been using VBA for 2 weeks and have run into a problem I can't find an answer to from searching various sites. I have a listbox with two columns (one name, one numbers). I have two buttons, one that puts the selections onto a sheet and then calculates the associated numbers and then updates a label with the sum of the selections. If they accept the selections, they can then press submit to record the data in the sheet. My problem is that after they press the calculate sum button the selections on the list dissapear, I need them to remain as they may need to change them based on the sum amount. How can I get the listbox to get the sum as necessary (and display within the userform), but not lose the selections in the process.

View 7 Replies View Related

Autofilter Data Based On Listbox Selections

Jan 7, 2013

Iam trying to auto filter a data range based on criteria passed from a list box selections.I want the auto filter to be filtering column 2 of the data range based on ALL the items SELECTED from a multi-select list box (named listbox2) at the click of a button. Here is what i currently have:


Sub Cmd1_Click()
Application.ScreenUpdating = False
For i = 0 To ListBox2.ListCount - 1
If Me.ListBox2.Selected(i) Then
Range("A3:C600").AutoFilter Field:=2, Criteria1:=ListBox2.List(i), Operator:=xlFilterValues


It works, only that it filters the data by ONLY the last selected value of the list(i.e only one value item in the list). I want it to loop through ALL the selections, applying filter on column 2 based on EACH of the selections on the list box.

View 9 Replies View Related

Multi-Select Listbox Selections To Different Columns

Feb 11, 2014

I am currently setting up a userform where one of the options is for an error code (chosen from a listbox) but the user has to be able to select multiple codes. I have the below working if it is NOT multi select, works a dream and goes in the right column.

If ListBox2.Text = "A1" Then
LastRow.Offset(1, 20).Value = "x"
ElseIf ListBox2.Text = "A2" Then
LastRow.Offset(1, 21).Value = "x"

But as soon as I set it to mutliselect and choose say A1 and A2 neither one writes, I imagine because Listbox2.text is now A1,A2 and not just one or the other.

View 9 Replies View Related

Populate Array From Mutiple Listbox Selections

Aug 26, 2006

Trying to load an array from user selected items in a listbox

My code returns Type Mismatch error

I don't know why as a I did not declare type for the array I thought treated as variant so should accept any values?


arrSelected(intI) = .Selected(intI)

Private Sub btnOK_Click()
Dim arrSelected
Dim intI As Integer
With Me.lstAccounts
For intI = 0 To .ListCount - 1
If .Selected(intI) Then
arrSelected(intI) = .Selected(intI)
End If
Next intI
End With

View 3 Replies View Related

Show/Display Selections In MultiSelect ListBox Control

Sep 26, 2006

Ive made a worksheet with a userform and ListBox. The listbox is filled with names. If you click on the last button a msgbox with your selected names is displayed. If you select the button without a selection from the listbox a message pop ups to warn that no selection is made. If you select any name afterwards and click on the button the message still pops up unless you select the first entry from the list ("natalie") then the code is right executed. I'm struggeling with the code for several days. I wonder if by any change somebody wants to correct the code. (I've uploaded my sheet)

View 2 Replies View Related

Reloading Selections In Multi Select Listbox Control

Sep 28, 2006

I have a multiselect listbox in a worksheet that the user can select from. When the file is saved, closed and reopened, the user selections disappear. Is there a way of reloading the previous selections when the file is reopened? I have used the listbox from the control toolbox and have Excel 2003. My VB experience is very limited!!

View 8 Replies View Related

Save Listbox Selection And Repopulate Selections When Opening Workbook?

Apr 15, 2014

I have a workbook that has 2 worksheets (Sheet1 and Sheet2). Each sheet has 2 checkboxes and a listbox that has 6 columns. Everything works great until I save and close the workbook. When I reopen, all the selections for the listboxes are gone, however the checkboxes are checked as expected. Why aren't the listbox selections being saved? How do I get them to save? I found something similar in a previous post but it referred to a userform which I am not using.

View 6 Replies View Related

Userform Listbox That Lists Data Based On Radio Button Selections

Mar 14, 2009

I have a worksheet (attached) that lists various clients in columan C. Column E lists whether each of the clients listed in Column C are 'Existing Business' or 'New Business'. I require a user form that has three radio buttons (one to select 'New Business', another for 'Existing Business' & one for 'All').

When Selecting a radio button, (e.g. 'Existing Business'), I need all clients listed in Column C of the worksheet that also have 'Existing Business' in Column E to be listed in a ListBox on the UserForm (with the second radio button allowing the text box to list 'New Business' and the third to list both Existing and New).

View 3 Replies View Related

Choosing Parent Selection From Listbox And Have It Automatically Select Children Selections

Apr 22, 2013

I have a listbox with a state and below it several cities. For example:

New York
New York - New York City
New York - Nassau County
New York - Westchester
New Jersey
New Jersey - Newark
New Jersey - Monroe County
New Jersey - Passaic

I would like to select the state and have it auto select all of the related cities including the state. So if I select New York,

It would look like this, where all are highlighted (as if selected). I coudln't find the highlighter on the thread tool bar:

New York
New York - New York City
New York - Nassau County
New York - Westchester

View 9 Replies View Related

Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format

Mar 21, 2008

I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View 9 Replies View Related

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)

Jan 7, 2009

If I have, in one cell (call it D1):


and in a lookup table on another sheet:
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...



View 9 Replies View Related

Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:


Benefit Type
Delivered or Enabled

Saving of $4M over 24 months.


I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.


Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled


So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

View 1 Replies View Related

Splitting Multiple Entries In Single Cell Into Multiple Columns

Jan 15, 2013

I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows

Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID

Next Line down is the data (Each row in a single cell)
Enabled 50 TEST SITE 1
Enabled 50 TEST SITE 2
Disabled 50 TEST SITE 3

How I could achieve this as I have a number of projects where this would become useful

I know you can use delimiters but with spaces between the values I just can't fathom a way forward.

View 12 Replies View Related

Swap 2 Mouse Click Selections After Resizing Selections

Jul 7, 2013

I am trying to take two random Mouse click selections and swap a set of ranges associated with the cells that are selected. For example if the user clicks on A1 it will resize(3,22) and store the selection as a variable to be swapped with another selection. Here is what I have so far but I keep getting a object required error on rngEmp1.

Sub SwapGroup()
If Selection.Cells.Count < 8 Then
MsgBox "Please Select two Groups to swap. Press and hold 'Ctrl' in between your selections", , "Swap Groups"

[Code] .....

View 4 Replies View Related

Pasting Multiple Selections Next To Each Other

Jun 13, 2013

What I'm trying to do is to select multiple groups of cells (say 5x4) which are below each other, but then paste them next to each other

For example, copy cells A1:E4, A8:E11, A15:E18 and paste them to A20:E23, A23:E26, A27:E26

View 2 Replies View Related

Multiple Selections In Autofilter

Feb 4, 2008

Is it possible to make multiple selections from the drop down box when using an autofilter?

View 2 Replies View Related

Print Area - Multiple Selections

Apr 22, 2009

Is there a way of selecting several print areas on several worksheets and printing it on ONE page? Selections will not be the same size.

Also is there a way to lock your printareas? I would like to save the spreadsheet and open it up next month and the printareas are still there ready for me to print. It seems to reset after i have printed it

View 3 Replies View Related

Copyrights 2005-15, All rights reserved