create a checkbox inside a dropdown in excel 2010

Question

Need to create a checkbox inside a dropdown in excel 2010. Have already tried creating a list box and selected the multiselectExtended option but that is not serving our purpose.

Sample of the required functionality attached:

Solution 1

Solved!

Check this link for the solution.

You can add an active form list box on the sheet and have the multi-select enabled.

Let me know your thoughts.

Solution 2

I think the only way to do this is to create a custom dialog. I hope the following is clear enough to do so.

1. Add the dialog:

   

2. Add a list box:

   

3. Add the data to a sheet and reference it in the List Box:

   

4. Add a button to the sheet:

   

5. Add a module in VBA and add the following code:

   Public diag As Object 'the dialog box
   
   'this code is assigned to the button on the sheet
   Sub Button3_Click()
       Set diag = DialogSheets("Dialog1") 'define the dialog box
       diag.Show 'sow the dialog box
   End Sub
   
   'to be assigned to the "OK" button in the dialog
   Sub Button2_Click()
   
       ' finds selected items
       Dim Msg As String, i As Integer
       Msg = ""
       With diag.ListBoxes("List Box 5")
           For i = 1 To .ListCount
               If .Selected(i) Then
                   Msg = Msg & .List(i) & ";"
               End If
           Next i
       End With
   
       'set the cell the values as needed
       Worksheets("Sheet1").Range("A1") = Msg
   End Sub
   

Comments

[glh]

@premshekhar see docs.google.com/file/d/0B72_qfwqy_QTMUFsRVVZWWxBd2s/…

[prem shekhar]

@glh.....I wanted to do in the below way.If you can tell me how to implement. I have tried the solution given by you,Thats great but it is not going to fufill the desired purpose. i.stack.imgur.com/oxoAD.jpg

[glh]

You could add x number of check boxes instead and cycle through them

[prem shekhar]

If you dont mind can you share your mail Id, I will send, what is the kind of look and feel i want to have in the excel.

[MS Sankararaman]

@Prem This solution will exactly meet your requirement. Check it out.

[glh]

I believe this from @MSS's answer is what your after he has done a good job.

[prem shekhar]

@MSS Your solution is exactly the same thing that i wanted to have,only one extra feature is required."how can we repeat the same feature in all the fields in the same coulmn?"My expectation is:when user click on any field in the same coulmn, he can make a multi selection and that selection will be presented as output in some other cell.

[prem shekhar]

@MSS..Thanks a lot buddy,I am not sure how can we implement the same feature in all the cells under same coulmn,i tried to modify your code but ultimately fail to achieve the target.

[MS Sankararaman]

@premshekhar Just for clarity, do you require similar buttons to be placed in each of the rows of a table, so that you can choose the required options to be concatenated to a different field of the same row?

[MS Sankararaman]

@premshekhar Check this file. It will have the select button with the list appearing for each of the cells of a particular column when you have the assigned short cut hit (Here it's Ctrl + q). Let me know your thoughts.

[MS Sankararaman]

@glh sir, do check the updated file I shared in the last comment of the answer.

[prem shekhar]

@MSS Set SelShp = Sheet1.Shapes.Range(Array("Group 3")) -- can you explain what is "Group 3"

[Photonic]

Hey guys i don't see where you put Sheet1.ListBox1, @MS have you hidden it or something. I am just trying to understand how the code works so i can use it for my project

[Photonic]

Ahh i get it, i view it in design mode. Anyway great post @MS