Assistance creating a selection box
I need assistance in creating a multiple selection box for A2 in first tab of the attached sheet, but without code if possible. Assistance is greatly appreciated.
Selection-Box.xlsx
Selection-Box.xlsx
ASKER
Close but not exactly what I was asking for. I need to be able to make multiple selections and have those selections show up beside eachother, separated by commas, in a single cell, without additional code.
rtod2, you might benefit from this Contextures article:
http://blog.contextures.com/archives/2011/09/09/excel-drop-down-multiple-select-or-remove/
I know you asked for no additional code, but worth a look. This is a data validation drop-down, that allows you to select one, then dropdown again and select another, etc. Even with other controls, similar code would need to be provided to do this, I believe.
See attached, I've had for a while and available from Contextures. It has a lot of examples, but the tab called SameCell has the capability you're asking for, albeit with code behind the sheet.
I checked the multiselect listbox & combobox (the only non-3rd party controls that allow multiple selections) and they are not designed to deposit results on the page in a linked cell, unless its in single select mode. MultiSelect in this case also requires code.
Here's a response that matches your question with a list box:
http://www.excelforum.com/excel-programming/586505-combo-box-multi-select-into-1-cell.html
Answer from mr Excel MVP - No, sorry - code required.
Here's a solution that matches your question with a combo box:
http://www.excelforum.com/excel-programming/586505-combo-box-multi-select-into-1-cell.html <- code required.
So, you can use data validation approach via contextures, or combobox/listbox approach (both codesets would mimic my last post).
Please advise your choice, should you choose, and I'm happy to assist incorporating into your work product.
Cheers,
Dave
DataValMultiSelect.xls
http://blog.contextures.com/archives/2011/09/09/excel-drop-down-multiple-select-or-remove/
I know you asked for no additional code, but worth a look. This is a data validation drop-down, that allows you to select one, then dropdown again and select another, etc. Even with other controls, similar code would need to be provided to do this, I believe.
See attached, I've had for a while and available from Contextures. It has a lot of examples, but the tab called SameCell has the capability you're asking for, albeit with code behind the sheet.
I checked the multiselect listbox & combobox (the only non-3rd party controls that allow multiple selections) and they are not designed to deposit results on the page in a linked cell, unless its in single select mode. MultiSelect in this case also requires code.
Here's a response that matches your question with a list box:
http://www.excelforum.com/excel-programming/586505-combo-box-multi-select-into-1-cell.html
Answer from mr Excel MVP - No, sorry - code required.
Here's a solution that matches your question with a combo box:
http://www.excelforum.com/excel-programming/586505-combo-box-multi-select-into-1-cell.html <- code required.
So, you can use data validation approach via contextures, or combobox/listbox approach (both codesets would mimic my last post).
Please advise your choice, should you choose, and I'm happy to assist incorporating into your work product.
Cheers,
Dave
DataValMultiSelect.xls
ASKER
dlmilie,
Thank you. That's a whole lot to dig through. I checked your attached .xls file and the first tab has what I am looking for. It is difficult for me to tell how to move those cells into my spreadsheet?
Thank you. That's a whole lot to dig through. I checked your attached .xls file and the first tab has what I am looking for. It is difficult for me to tell how to move those cells into my spreadsheet?
No VBA at all.
Enter this formula in HorizModel. To select the cells simply right-align the cells and press F9. All right-aligned cells would be displayed.
=SUBSTITUTE(IF(CELL("prefi
Enter this formula in HorizModel. To select the cells simply right-align the cells and press F9. All right-aligned cells would be displayed.
=SUBSTITUTE(IF(CELL("prefi
ASKER
Thank you sir for the no VBA instructions.
I'm a little confused though. Can you show me, perhaps using a tool like Jing at http://www.techsmith.com/jing-features.html?
I'm a little confused though. Can you show me, perhaps using a tool like Jing at http://www.techsmith.com/jing-features.html?
That link is not working. What does it do?
Here is the file.
The cells which are right-aligned will be shown in the HorizModel sheet after you press F9
Copy-of-Selection-Box.xlsx
The cells which are right-aligned will be shown in the HorizModel sheet after you press F9
Copy-of-Selection-Box.xlsx
@rtod2 - are you interested in my solution?
I added the solution to your workbook, with the code in the HorizModel sheet. I highlighted rows 2-31 and added drop down data validation to those rows in column 1.
See attached - You now have multi-select capability as promised.
I have to go celebrate with my son his birthday, but will check back in a few.
Cheers
Dave
Selection-Box-r1.xlsm
I added the solution to your workbook, with the code in the HorizModel sheet. I highlighted rows 2-31 and added drop down data validation to those rows in column 1.
See attached - You now have multi-select capability as promised.
I have to go celebrate with my son his birthday, but will check back in a few.
Cheers
Dave
Selection-Box-r1.xlsm
@ssaqibh - innovative, especially if you only need to do it for one cell.
"No VBA" has its limitations.
ASKER
Dave,
Thanks for your attachment. I still need to move it into my other sheet and can't do that. Please advise.
Best so far in Post ID 37846603
Thanks for your attachment. I still need to move it into my other sheet and can't do that. Please advise.
Best so far in Post ID 37846603
No prob. You want the drop down in the other sheet and output on a2 only of the first sheet?
Give me a few to get back home
Dave
Give me a few to get back home
Dave
ASKER
I want to be able to move it around from sheet to sheet.
I need to be able to change the location of the data cells and the chart of accounts cells.
I need to be able to change the location of the data cells and the chart of accounts cells.
Please clarify:
What do you want to move around? What is "it"?
What is a data cell?
Will there only be one set of chart of accounts cells? A named range handles that location.
Where do you want the drop down box? What should it be pointing to (re: where is the list going to be)? And, where do you want the results of the drop down box selections to go?
Dave
What do you want to move around? What is "it"?
What is a data cell?
Will there only be one set of chart of accounts cells? A named range handles that location.
Where do you want the drop down box? What should it be pointing to (re: where is the list going to be)? And, where do you want the results of the drop down box selections to go?
Dave
ASKER
The reason that I was looking for a no-code solution is so that I could save the line, and modify the parameters as needed, depending on where I wanted it.
Just describe a bit what you want to do - can you respond to my questions?
Dave
Dave
ASKER
Dave, Thank you sir!
I want to be able to add items to a selection list and select more than one items at a time.
The caveat here is that I want to be able to change where I put the selection list column, and where I put the output column.
Anything could change:
The name of the sheet could change.
The name of the tab could change.
The name of the table could change.
The columns used for each could change.
I want to be able to add items to a selection list and select more than one items at a time.
The caveat here is that I want to be able to change where I put the selection list column, and where I put the output column.
Anything could change:
The name of the sheet could change.
The name of the tab could change.
The name of the table could change.
The columns used for each could change.
Ok - maybe this is what you want. For ANY data validation list in this workbook that you create, you have this multi-select capability.
Just create a data validation list (let me know if you need assistance with that) and set the range to ANY range that has a list of values (or just add a list of values).
When you use the drop down for that data validation list, the app provides the multi-select with comma separated results.
Is this what you're looking for?
Dave
Selection-Box-r2.xlsm
Just create a data validation list (let me know if you need assistance with that) and set the range to ANY range that has a list of values (or just add a list of values).
When you use the drop down for that data validation list, the app provides the multi-select with comma separated results.
Is this what you're looking for?
Dave
Selection-Box-r2.xlsm
ASKER
I just tested that last one you sent and the drop-downs don't work in it like they did in your first one.
Even if they did though, what I am looking for are some instructions (specific steps) on how to create that without using code.
Even if they did though, what I am looking for are some instructions (specific steps) on how to create that without using code.
>>I just tested that last one you sent and the drop-downs don't work in it like they did in your first one.
As I stated, you'd need to create a data validation list anywhere in the workbook to test it. If you remain interested, then do so or ask for an example. I said to ask and I would assist.
>>Even if they did though, what I am looking for are some instructions (specific steps) on how to create that without using code.
Other than what Saqib provided, and as I posted initially, there is no other NO-CODE solution for what you want - that allows multiple selections, with a linked cell at another location in the workbook.
You would need a control - FORMS or ACTIVEX - solution for that. The ones that come with your installation definitely do not provide this solution (re: the links I posted on my initial post).
I'll stop providing alternatives as I don't want to spin my wheels, nor spin yours.
I had figured how to give you this, with some rules:
>>Anything could change:
>>The name of the sheet could change.
>> The name of the tab could change.
>> The name of the table could change.
>> The columns used for each could change.
And the solution I had just posted did all this, except put the result in the specified destination which is what I was just working on. However, I'm stopping on the very good chance that you are no longer interested in a coded solution that does this.
My thought was to specify the destination just above the DV list, so when you made changes, the code put the values where you wanted them to go.
Please confirm this assumption - you don't care to see any more VBA alternatives.
Cheers,
Dave
As I stated, you'd need to create a data validation list anywhere in the workbook to test it. If you remain interested, then do so or ask for an example. I said to ask and I would assist.
>>Even if they did though, what I am looking for are some instructions (specific steps) on how to create that without using code.
Other than what Saqib provided, and as I posted initially, there is no other NO-CODE solution for what you want - that allows multiple selections, with a linked cell at another location in the workbook.
You would need a control - FORMS or ACTIVEX - solution for that. The ones that come with your installation definitely do not provide this solution (re: the links I posted on my initial post).
I'll stop providing alternatives as I don't want to spin my wheels, nor spin yours.
I had figured how to give you this, with some rules:
>>Anything could change:
>>The name of the sheet could change.
>> The name of the tab could change.
>> The name of the table could change.
>> The columns used for each could change.
And the solution I had just posted did all this, except put the result in the specified destination which is what I was just working on. However, I'm stopping on the very good chance that you are no longer interested in a coded solution that does this.
My thought was to specify the destination just above the DV list, so when you made changes, the code put the values where you wanted them to go.
Please confirm this assumption - you don't care to see any more VBA alternatives.
Cheers,
Dave
ASKER
Dave, Thank you sir,
I'm interested in your solution or I would not have inquired.
Could you create a video of what you mean perhaps http://www.techsmith.com/jing-uses.html
1. I open a new sheet.
2. I create a selection list in one column.
3. I pull comma, separated data into another column.
??
I'm interested in your solution or I would not have inquired.
Could you create a video of what you mean perhaps http://www.techsmith.com/jing-uses.html
1. I open a new sheet.
2. I create a selection list in one column.
3. I pull comma, separated data into another column.
??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.ozgrid.com/download/listbox.zip