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 Replieshow 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 RelatedI 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] .......
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 RelatedOn 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 RelatedI 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?
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..............................
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.
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 RelatedI 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).
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 RelatedI 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 RelatedIam 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.
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.
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
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??
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 RelatedI 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 RelatedI 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 RelatedI 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).
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
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.
VB:
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] .....
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
Is it possible to make multiple selections from the drop down box when using an autofilter?
View 2 Replies View RelatedIs 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
I have created a named range that selects about 30 different cells (non-contiguous) and I am trying to copy over all the formulas and paste with values. However excel is not letting me to do this. It says this command cannot be applied to multiple selections. Can I get around this?
View 3 Replies View RelatedI'm trying to create a macro that will copy and then paste a range of cells to another worksheet but in one row. The cells that I would like to copy are in 3 different rows. They are B7:F7, C12:D12, C17:E17. Now I've managed to find a macro through this forum that will paste the particular cells but they I can't seem to find a way to record a macro that will paste them in one row. In addition to that I also tried copying and pasting each of the ranges of cells individually it kind of worked but it came up with an error box.
The problem is, even if I can find a macro to paste those cells it has to be able to paste them underneath the previous row. So e.g it should paste the cells in row 17 and then when i press the command button again it should paste them to row 18.
Below is an IF CASE VB code i was using yesterday. I am coming to a dilemma. There can be multiple columns on this one particular spreadsheet that will have different selections from the dropdown that was created. For example in column B "VIRTUAL" could be selected which will hide the rows specified in the code below, but in column C "PHYSICAL" might be selected which requires other rows to be displayed but the rows for the selection in column B need to stay displayed. I am thinking the best way to do this might be to color code cels within each column based upon selections but how can a cel be color coded based upon a specific selection from the dropdown?
View 10 Replies View RelatedThe code below seems perfectly fine to me, but for some reason, my range ends up being every row, even if the first cell is an error. Also I get a error on the line "Range(Rng).Select": Run-time error '1004': Method 'Range' of object'_Global' failed.
View 2 Replies View RelatedIs there a way (with a macro?) to select all rows within a certain range (e.g. from row 3 to the end of the sheet) SEPARATELY, just as selecting them manually with ctrl+select?
Like in my example sheet;
I would use the AutoFilter on row 2 to show only the items with prices equal to 30. Now I want to select the entire rows (name, amount & price) of these two items, so that when I turn of the AutoFilter the two rows would still remain selected.