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.
VB:
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
ADVERTISEMENT
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
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:
Sheets("Form").Select
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
Jun 30, 2014
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
View 5 Replies
View Related
Jun 12, 2009
Whats the VBA code for transfering selections on a multi select list box to a page.
ex. the list box has selections from 1 to 20 and I want to select 3,4, and 5
then on the page I want it to say 3,4,5
View 9 Replies
View Related
Oct 30, 2012
I have a multiselect listbox with values that gets populated from a sql statement, and I would like to get is the first or second index from the selected item. I know how to get the listindex from a combobox by using:
VB:
cbnumber.List(.ListIndex, 0)
How can I loop through and get the 1st index number for the selected items only from the listbox? I want to pass this index number to another sql statement.
View 5 Replies
View Related
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
Jan 19, 2009
I have a mulit-extended listbox, a textbox and a button on a form.
I have populated the listbox with a list of files from a directory no problems.
First Part (Single Selection):
When I click on one/single filename in the list I want:
1. The textbox to be populated with the name I have selected
2. When I click the button (With the single filename selected, I want to call a function passing the filename I have selected.
The function could contain a msgbox(selecteditemname) for now.
Second Part (Multi Selection):
When I multi select filenames from the list I want:
1. The textbox to be populated with "Multiple Files Selected"
2. When I click the same button as before (With multiple filenames selected, I want to call a different function and pass each filename individually in may be a for loop)
The function could contain the loop and with each filename in the loop msgbox(selecteditemname)
I have tried the above and noticed that the listbox1.value property for multi listboxes dont seem to work and I am not really sure how to distinguish between one selection or multiselection to get a button to pass off to two separate functions.
View 9 Replies
View Related
Aug 5, 2009
Is it possible to have some VBA in Sheet1:
1. That when a user click on a cell in a particular column within a specific sheet:
1.1 That they are presented with a multi select listbox (Showing 20 items stored in a column in Sheet2 )
1.2 And when the items are selected that the items are automatically written in the same cell that was clicked in Sheet1 with a new line break in between each item?
View 9 Replies
View Related
May 20, 2014
I want to add a msgbox with a no selection error to a multi select listbox I have. For a dropdown I just use "if .ListIndex =-1" but listindex doesn't seem to work. I'm playing with .selectedindex but it's not playing ball
View 4 Replies
View Related
Oct 25, 2009
i have a multi select listbox that i want to be able to remove the selected items in one hit.
i have not been able to convert code for removing single selected items and could not find a working example.
this is probley the closest though cos its removing items it plays havoc with the listcount
View 7 Replies
View Related
Mar 18, 2008
I am trying to create a data entry form with a text field and a listbox. The list box is populated by another sheet and the text box will be used to enter names. I need to be able to enter a new name, select multiple options from the list box and then click a button to add this info to a sheet in excel.
I have read about changing the list options to allow this etc - the problem is I can't get the results to go back into excel - all I get is a name with nothing from the listbox.
View 4 Replies
View Related
Aug 14, 2014
I am working on a macro, which asks the required field from multi select list box.
User can select required field, for example some user may be interested in
Region ID, Circle, BSC ID, Cell ID, Sector ID, City/Town.
Some user may be interested in Circle, BSC ID, Cell ID, Sector ID, City/Town.
Some user may be interested in BSC ID, Cell ID, Sector ID.
In sort, Selection on a multi select list box copy and paste that field from any folder.
That folder may have any location, and contains file which have.
Date
Vendor
Region ID
Circle
BSC ID
[Code] .....
View 1 Replies
View Related
Sep 26, 2006
changing two variables a various amount of times and running the same procedure and copying the resutls into another sheet. Seems like a perfect place for a macro. However, these variables can be chosen from a list that the user wants. So why not build in a listbox for each one. Now I have two listboxes one for variable A and one for variable B.
The procedure in theory goes something like this we change variable A from the base case and then run the procedure for variable B, get the results, then run the scenario again but changing only variable B abnd repeat. Then once, all of the variable B scenarios are done, I want to change the variable A and then repeat and so forth.
That is the background and my main problem at this point, is that have these values in two listboxes, I know how to do the for each loops and such, however, I do not know how to do them for values in the listbox.
How do I identify the values selected in the respective listboxes and then pull them so I only use them for the for each loop?
View 7 Replies
View Related
Nov 26, 2008
i'm having a problem with a script I have (script is below). What i'm doing is running a script that displays files in a certain folder, those files are then displayed on sheet1 column O and are linked so when they are clicked upon, the file will open. I also have a combo box with a input range of $O$O. That puts the values in column O in the combo box. Now here is a problem and request.
Problem first, when I select the file names from the combo box, they don't open the file, all it does is display the file name in combo box and that's it. how to I get it to open up the file? Now for a request, I have another sheet that is almost exactly as the one i described above. The only thing different I want is when someone clicks on a file name from the combo box, instead of it automatically linking to the file, I want them to click on a button to display the file.
Sub HyperlinkXLSFiles()
ActiveSheet.Unprotect
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "c:Sec"
.FileType = msoFileTypeExcelWorkbooks
' .Filename = " Book*.xls"..................
View 2 Replies
View Related
Feb 13, 2008
I created a userform for budgets. Currently the way that the user selects the year for a particular line item is a combobox. The combobox is filled from a textbox, where the user inputs the project duration. For example if the user put five years into the textbox, the combobox would have years 1,2,3,4,5, and a default of all years. I want to change the combobox to a multi-selection List Box. I changed the sub that loads the current combobox to this:
Public Sub ComboLoad(cboExpense As ComboBox, cboJob As ComboBox, _
ltAp As MSForms. ListBox)
Dim i As Integer
ltAp.Clear
If Len(UserForm2.txtYears) = 0 Or Len(UserForm2.txtYears) = "" Then
ltAp.AddItem ("All Years")
Else
For i = 1 To UserForm2.txtYears
ltAp.AddItem i
Next i
ltAp.AddItem ("All Years")
End If
ltAp.Text = "All Years"
'ltAp.value = "All Years"
I used to be able to select a value for the combobox, (the default of all years), but I get an error if I include that part of the code now. I wanted to know how I can make this selection by code. Also, the bigger question for me is how do I deal with multiple selections that may or may not be sequential. As an example, for the travel section, there may be a particular expense that will occur in yrs. 1,3,5. I found some info about referring to the position in the array, but it seems like this will constantly change. I could imagine doing case scenarios, but it seems like there would be too many potential possibilities to cover.
View 5 Replies
View Related
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
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
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
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
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
Next
CheckBox1.Value = False
End Sub..............................
View 2 Replies
View Related
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
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
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
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
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
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:
VB:
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
[Code]...
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
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
View Related
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?
Debug:
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
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...
North
East
South
West
...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
