We help IT Professionals succeed at work.

Assistance creating a selection box

rtod2
rtod2 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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?
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("prefix",ChartofAccounts!A2)="""",", "&ChartofAccounts!A2,"")&IF(CELL("prefix",ChartofAccounts!A3)="""",", "&ChartofAccounts!A3,"")&IF(CELL("prefix",ChartofAccounts!A4)="""",", "&ChartofAccounts!A4,"")&IF(CELL("prefix",ChartofAccounts!A5)="""",", "&ChartofAccounts!A5,"")&IF(CELL("prefix",ChartofAccounts!A6)="""",", "&ChartofAccounts!A6,""),", ","",1)

Author

Commented:
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?
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
@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
Most Valuable Expert 2012
Top Expert 2012

Commented:
@ssaqibh - innovative, especially if you only need to do it for one cell.
"No VBA" has its limitations.

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Just describe a bit what you want to do - can you respond to my questions?

Dave

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
>>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

Author

Commented:
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.

??
Most Valuable Expert 2012
Top Expert 2012
Commented:
Ok.  I've created a demonstration workbook and video.

The code is setup to work multi-select on ANY data validation (DV) dropdown created in the workbook.  IF there's a formula above the DV dropdown that is a reference to another cell - e.g., =HorizModel!A2 (reference to A2 on tab HorizModel) then the code takes the result of the dropdown and populates that destination cell.

here's the code that makes it all work:

ThisWorkbook:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Call ServeDropDown(Target)
End Sub

Open in new window


Public Module:
Option Explicit
'Source adapted from...
' Developed by Contextures Inc.
' www.contextures.com
Public Sub ServeDropDown(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim destRng As Range

    If Target.Count > 1 Then GoTo exitHandler
    
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    
    If rngDV Is Nothing Then GoTo exitHandler
    
    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
      Application.EnableEvents = False
      newVal = Target.Value
      Application.Undo
      oldVal = Target.Value
      Target.Value = newVal
      If True Then 'Target.Column = 1 - set to TRUE to test ALL DV lists
        If oldVal = vbNullString Then
          'do nothing
          ElseIf newVal = vbNullString Then
          'do nothing
          Else
            Target.Value = oldVal & ", " & newVal

        End If
        On Error Resume Next
        'check the address above the DV list
        Set destRng = Target.Offset(-1, 0)
        If Err.Number = 0 Then 'found the address!
            Range(Target.Offset(-1, 0).Formula).Value = Target.Value
        End If
      End If
    End If
    
exitHandler:
      Application.EnableEvents = True
End Sub

Open in new window


See attached demonstration file.

Here's the link to the screencast:
http://screencast.com/t/29crC3pM

  Hope this helps!


Dave
Selection-Box-r2.xlsm