Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

List Box Selections Outputting Multiple Selections With One Click

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi all,

I have something weird happening to some list boxes located within a form made in the VBE. I have 4 list boxes in total and when I select a value from them, I will have to double click the value just ot highlight it. Also, if I select the 3rd from the top (in two list boxes), multiple selections will be made. The form in question is called frmTool2.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
Disables the "Save As" Feature in Excel
- This macro will disable the Save As feature in excel. This means that a user will not be able to resave the workbook un
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
- List all of the named ranges in a workbook in Excel and the corresponding values stored within every named range. This

Similar Topics







Hi everyone. It's been a while since I last posted on here, which means I've not been doing much Access stuff. However, now I'm back, mucking about with MS Access again for work and rediscovering how annoying Access's error messages are !

So, right now I have a 'menu' form (Frm_Menu) which has a number of List Boxes (Box_OrganisationSelectList and Box_KeyIndSelectList), both allowing multiple selections.

What I want to do is have a bit of code linked to a button so that once the user has made a selection, it opens up table Tbl_HESdata filtered by all their selections.

I can get it working with ONE of the List Boxes allowing multiple selections, using the following:

Code:

Private Sub cmdfilter_Click()

Dim varItem As Variant
Dim strWhere As String

For Each varItem In Box_KeyIndSelectlist.ItemsSelected
strWhere = strWhere & "Tbl_HESdata.keyind =" _
& Chr(39) & Me![Box_KeyIndSelectlist].Column(0, varItem) & Chr(39) & " Or "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "

DoCmd.OpenTable "TBL_HESdata"
DoCmd.ApplyFilter , strWhere
    
End Sub


So this is opening the table and filtering effectively by the first list box (Box_KeyIndSelectList). However, I CAN'T figure out how to add more multi-select list boxes to the code so that they can filter by multiple fields. I've tried a number of ways that look right, but I get a weird and wonderful array of error messages that are suitably cryptic for Access.

Could someone offer some pointers? I'm a relative weakling in terms of VBA code, so anyone who could treat me a little like a 5 year old would be made very welcome...

Also, while we're on the subject, could someone explain whether (and this is probably a complete newbie question) the names "varitem" and "strwhere" are required? Or could I change them to whatever I want?

God, I'm a newbie.


Hi all, I'm familiar w/ named ranges and data validation. i'd like to have a list that would enter multiple selections into a box on a form (several boxes on the form sized to fit 15 names each. each box would have a list of names which i could choose up to 15 names, one at a time). is this possible?
thanks Brian


I have 15 list boxes on my spread sheet. each has the same data selections. I want to be able to incriment a running total in another cell depending on the selection made and somehow have a way to confirm the incriment.

My thought is to have a check box next to each list box that would add to the corrasponding cell when checked (only one time though).

ex:
list box selections - AA, BB, CC
- If BB is selected (and checked), I want cell B26 to be incremented by one
- With 15 boxes I need this action with each one. so if in 4 list boxes BB was selected (and checked) cell B26 would increment by 4.

other cells -

----------A----------B

25------AA ----------3

26------BB----------1

27------CC----------7

This is a bit difficult to explain. If you need further details please let me know.
I hope this is even possible.


I have a form that has a drop down list with up to six possibilities and I would like to be able to pick more than one from this list. How do you set this up to allow mulitiple selections?


....hope I do an adequate job explaining myself here.

I have a spreadsheet with multiple drop down boxes.

The question is this: Is it possible to have one drop down boxes selections
be contingent upon what was chosen in the previous drop down?

For example, the available selections for drop down box #1 are A, B, and C

IF user selects A from drop down box #1, then drop down box #2 has
selections of Red, White, and Blue
IF user selects B from drop down box #1, then drop down box #2 has
selections of Green, Yellow, Black
IF user selects C from drop down box #1, then drop down box #2 has
selections of Orange, Brown, Purple

Haven't quite figured out whether this can be done within the context of
combo boxes.

Any help would be great.

Thanks.

Jim






Is there a simple way to make multiple selections from a list box that are filters for a query behind a report.

I have several list boxes and reports to set up and would like something neat that can be used for all scenarios???

Any help greatly appreciated

Thanks


Hello,

I have created a form in Excel 2003 which has a set of drop-down boxes to allow users to select either RED/GREEN/BLUE for a variety of questions. I now want to add one last question at the bottom: "Clear all selections?" and give a drop-down box with only YES option in it.

Once the user has filled out the all form fields, she may choose to clear all her selections by simply selecting YES against "Clear all selections?"

Now, I believe I'll need to put some sort of formula in each of the RED/GREEN/BLUE drop-down type of boxes which will tell them to clear their selections the moment the user says "YES" to the last question, else retain whatever selection exists in those boxes.

Could someone suggest me a simple formula to achieve this, if at all this is possible to do? I'm not keen on using macros inthis form. Thanks.


I have successfully used VBA to generate queries (write the SQL string) based on SINGLE values from different controls on a form. However, this time I need the WHERE clause to be EXTREMELY dynamic. I have three list boxes that each could be either "*ALL*" or one or more selections in the list box. (I am using UNION for the "*ALL*" in the row source statement). The list boxes are not dependent on each other. Basically I need the list boxes to be "criteria (data) entry" controls that dynamically build the WHERE clause. If I can get that down, I should be okay...


I have created a form and have added some list boxes.

When the user selects the values in the list boxes, they then click a button which adds these selections to a database area in the excel workbook and erase all the data in the form.

This all works fine.

Now I would like to make all the listbox go back to a default value after I click the button.

How can I do that ?

Thank you very much.


I have created a form and have added some list boxes.

When the user selects the values in the list boxes, they then click a button which adds these selections to a database area in the excel workbook and erase all the data in the form.

This all works fine.

Now I would like to make all the listbox go back to a default value after I click the button.

How can I do that ?

I have already tried
Quote:

listbox1.Value = "default"

and it did not work. The VBA returns a message "it is necessary an object" .

Any idea ?

Thank you very much.


Hi,

I haven't used List Boxes much previously but decided to use one in this instance because I wanted the capability to make multiple selections. Searching these forums gave me a pointer towards potential helpful VBA. I used the following in a list box called "ListBox_Analysis_Code":

Code:

Private Sub ListBox_Analysis_Code_Change()

Dim iPtr As Integer
Dim sTemp As String

sTemp = ""
For iPtr = 0 To ListBox_Analysis_Code.ListCount - 1
    If ListBox_Analysis_Code.Selected(iPtr) = True Then sTemp = sTemp & ListBox_Analysis_Code.List(iPtr) & " "
Next iPtr
Range("D25").Value = sTemp
End Sub


The code was borrowed from this thread:
http://www.mrexcel.com/board2/viewto...ple+selections

However I've got a couple of problems that have me stumped and I seem to be suffering from SearchTard symptoms because I can't find much via Google or here on the topic(s)...

Firstly, the VBA dumps the output as a string to cell D25. How can I modify the code to output each value dinstinctly as an array from a prescribed worksheet / cell range instead of a single concatenated string?

Secondly, can I either stop or pause the list box from resetting the selections made or somewhow reload / save them so they can be easily recalled? At the moment it deselects any selections made after a few seconds - useful in some cases but annoying in others (particularly if you are forced to reselect a dozen seperate values on a longish list)

Any assistance appreciated!


Can someone please help me here,Counting total records

I am trying to put a count in textbox5 on a form from this sql code.

SELECT SELECTIONS.Track, SELECTIONS.RNO, SELECTIONS.TAB, SELECTIONS.HORSE, SELECTIONS.Jockey, SELECTIONS.Odds, SELECTIONS.RATING, Ozeform.WNO, Ozeform.[_2ND], Ozeform.[_3RD], Ozeform.NSW_W, Ozeform.NSW_P, Ozeform.NSW2, Ozeform.NSW3
FROM SELECTIONS INNER JOIN Ozeform ON (SELECTIONS.Track = Ozeform.TRACK) AND (SELECTIONS.RNO = Ozeform.RNO)
WHERE (((SELECTIONS.TAB)=[wno]) AND ((Ozeform.RDATE)=[Forms]![Form1]![Combo25])) OR (((SELECTIONS.TAB)=[_2nd])) OR (((SELECTIONS.TAB)=[_3rd]))
ORDER BY SELECTIONS.RcTime;
Appreciate any help here


I need some advice on how to create a BOM form based on list box choices made.
Attached is a blank form i have created that i want bill of materials dropped into.
I was thinking of using dependent list boxes to choose what i want.
Example:
User will choose a base model home in first list box. Then second box with have a list of elevations based on list box one. Then third will have a list all options available for that model home and elevation. If it is possible i would like the third box to allow several selections to be made. If not i will have to make more list boxes for any extra options chosen. Beyond the 2nd list box nothing will be dependent on the previous one.
Now after those choices are made, or even as i am selecting them, i would like data from a range in another workbook to be copied into this blank form. I have one workbook that has all base model homes on individual tabs and then i have a workbook for each model home options with each option on a tab. After each copy i guess it would have to copy data on next available empty row and if i run out of empty rows in the range add new ones in the body of the form. If i can get to a starting point i can add the data needed if someone can help.

Am i asking more out of excel than possible?
Thank you,


I have two multi selection lists, one dependent on the first. A few questions,

How do I filter the second list based on the multiple selections from the first list
How do I then loop through the multiple selections from the second list against the same column on another sheet and sum values

Example:
List One is Org Level 1, and should allow for multiple selection
05
06
10
18
etc..

List Two is Org Level 2 and there is a one to many relationship to Org Level 1 - again, multiple selections allowed here
Org 1 of 05 has Org Level 2 of 0510, 0520, 0540 etc..

I am trying to sum active employee count based on the selected Org Levels - this data looks like the following:

Org Level 1 Column A Org Level 2 Column B Headcount

05 0510 23
05 0520 5
05 0540 4
06 0603 18

And so on...

So I want to loop through the multiple selections made in the second list, and sum headcount

Many Many Thanks!!!!


Is there a way to allow users to make multiple selections from a validation list and have all selections made appear in the cell?


I'm trying to create a form that my Program Administrators (Department Deans) might be able to use for evaluating and observing their faculty. I've created a sheet that has check boxes for most of the selections, but at the end of each section there is a comments area that I've created a listbox that contains some choices. I thought that this would be simple, but has proven to more involved that I thought.

All that I would like to do is to have the selections that are made from the list box populate into the comments cell so that when the sheet is printed the comments can be shown.

Apparently, I might be in need of a simple VB script to put into this. I would have thought that this would have been one of the primary functions of a listbox in the first place to be able to show the selections to be printed. Does anyone have any ideas or something that could help?


I'm trying to create a form that my Program Administrators (Department Deans) might be able to use for evaluating and observing their faculty. I've created a sheet that has check boxes for most of the selections, but at the end of each section there is a comments area that I've created a listbox that contains some choices. I thought that this would be simple, but has proven to more involved that I thought.

All that I would like to do is to have the selections that are made from the list box populate into the comments cell so that when the sheet is printed the comments can be shown.

Apparently, I might be in need of a simple VB script to put into this. I would have thought that this would have been one of the primary functions of a listbox in the first place to be able to show the selections to be printed. Does anyone have any ideas or something that could help?


I have created a user form that has a list box. The list box contains a list of team names in a MultiSelectExtended mode. Once I click on the command button, I would like to hide rows on a different sheet based on the selections in the list box. Basically, if a team is selected, it will not be hidden. Any number of 1 to 98 teams can be selected.

My problem is with knowing how to pass the selections in the list box to a variable and how to use that variable in the VBA code to see if the row in question contains a selected team name or not. I've already set up the command button that starts the action and I've got a few simpler VBA steps already done. The team name would be in column F of the worksheet in question.

This is the first list box I've used so I'm a newbie on it.

Thanks
Brian


Hi All

I'm trying to put a form control combo boxes and data validation in various cells of a spreadsheet. Unfortunately, users with Macs cab see the combo boxes and data validation cells, they can select the drop down arrows and see the selections, but when they want to click on any of the selections it will not transfer it to the cell. Is there any configuration settings I have to do in the excel program for macs

ANY HELP WOULD BE GREAT!!!

Cheers!


Is there a way to assemble a drop list that would allow for multiple selections while holding the control key into a cell set to wrap the responses? I've made quite a few attempts but can't seem to come up with it.


I want the Form to be able to refine the list of selections in one column based upon the selection made in the previous column. Suppose I have a Form with all NFL Teams. Behind the Form I have the data in these three corresponding tables, Conference -contains just NFC or AFC; Division - contains all 8 divisions; Teams - contains all 32 teams. In the first column, Conference, you have two choices NFC or AFC, I select NFC. In the second column, Divisions, now my selections will only be the 4 divisions of the NFC - NFC North, NFC South, NFC East and NFC West, I select NFC South. In the third column, Teams, now my selections will only be the 4 teams of the NFC South - Saints, Panthers, Bucs, Falcons, instead of all 32 teams.
Currently as I tab through the Form, when I get to the third column, Teams, I have to toggle through all 32 teams. Also, once I get to the final column, how can I enable the pick list so that someone could use the up or down arrow to move between choices? For instance, if both New York teams were in the same division, and as I get to the third column Teams, I type "Ne" both teams will appear in the pick list, but to get the team I want I would have to continue typing the entire city "New York" plus the first letter G for Giants or J for Jets to get to the team I want. How do I enable the arrows for selection? Thanks...and of course this is a hypothetical form, my data contains Areas, Districts and then Offices then Managers.


Hi:

I am learning a lot by reading these posts, but I cannot figure this one out.

I have a table (nn rows x 6 columns).
The first column is the ID of the participant. The other five columns are
their selections from a validated drop-down (header row shown below). In each
row a participant selects five names from the same list (the list has about
1,000 names), but has to choose a different name for their five selections.
That part I have error checked.

Participant ID Name1 Name2 Name3 Name4 Name5

What I also need to check (which I cannot figure out) is that no
participants select the same five names from the list. While the five
selections are made from the same (alphabetized) list, they will not
necessarily be made in the same order.

Thanks for your help.



I have a worksheet with a series of listboxes (with the list option =1 to allow multiselection). These boxes are loaded dynamically (not using the linkedcell method).

I can load the boxes just fine, and make selections I need. However, even after the workbook is saved, when I close it, then reopen it, the list boxes are cleared. I can actually see the data for a split second in the listboxes before they are cleared.

I have made sure that I am not using a clear method when the worksheet is opened and I do not have a sheet event to clear them either.

Any ideas as to why list boxes would automatically clear on open of a workbook? Thanks for the help and ideas.

I cannot post the workbook as it contains sensitive account information.


I have a large list box allowing muliple selections that I would like a user to be able to use to build a small record set. How do I build an interface that allows the user to see what selections they have made and make any adds or deletions to that record set before final submission (using the list to drive another process). Is it possible to have the selections feed into some type of window and build a few command buttons to add and delete records from the record set.

Any help is appreciated.


Hello.

I am a newbie with perhaps a very silly first question!

I am attempting to build a report with a graph where rather then use multiple check or option boxes to select and deselect criteria to switch formula on and off to graph, to use a Form control listbox (with selection type as "Multi") so that they can select multiple criteria from there to chart the information.

I have used a listbox from the Developer/Insert/Form Controls menu and have populated it with a list of 14 criteria to select from and checked the Selection Type as "Multi" so that multiple items can be selected to graph and after that this is where I become lost.

How would I go about getting the list box to populate the criteria selected within it into a spreadsheet in the excel workbook where I could then reference formula to the selections and bring back data to graph so I can make my graph dynamic?

Any help much appreciated.

Thanks.