Data validation

[ehsas69]

I have following names in my excel file

Mali
Honam
Fanmoon
Bassam
Ramzi
Tokenam

I have created validation list but I only can select one name.How can I select more than one name.

For example I would like to have drop down wit selecting names as follow
Mali & Bassam
Ramzi & Fanmoon & Honam
Etc
Kindly help

 

[jim may]

PM me with your e-mail address and I will gladly send you a small demo file;

Jim May

 

[sous2817]

Try using a listbox and set the "Selection Type" to "multi".

 

[jim may]

Try using a listbox and set the "Selection Type" to "multi".

Sent!!

 

[ehsas69]

How to select listbox and multi selection. Thanks

 

[sous2817]

From the developer tab select Insert.

Mouse over the Forms controls until you see the tool tip for listbox (it looks like a box with lines in it and small up and down arrows on the right side).

Click that once and then click where ever you want to place it on your worksheet.

Right click on the control and select "Format Control"

In the resulting dialog box, switch to the Control tab.

From there choose the "Multi" radio button. You can also set the input range (that would be the range of cells that house you names) there as well.

 

[ehsas69]

Thanks. How to hide unwanted names from the boxlist

 

[ehsas69]

Thanks. How to hide unselected names from the boxlist

 

[sous2817]

Well you can't. You can just select and un-select them. You may want to give Jim's solution a try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then GoTo errHandler
Set Rng = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If Not Intersect(Target, Rng) Is Nothing Then
Application.EnableEvents = False
newval = Target.Value
Application.Undo
oldval = Target.Value
Target.Value = newval
    If Target.Column = 1 Then
        If oldval = "" Then
        'do nothing
        Else
        If newval = "" Then
        'do nothing
        Else
        Target.Value = oldval & ", " & newval
        End If
        End If
    End If
End If
'Application.EnableEvents = True;since errHandler only includes App.EnableEvents = True
'Exit Sub                   program jumps over errHandler: and performs App.EnableEvents = True
errHandler:
Application.EnableEvents = True
End Sub

The above code assumes your data validation is in the first column. Edit this line:

If Target.Column = 1 Then

to be whatever your data validation is (column A =1, column B = 2, column C =3, etc, etc, etc).

Hopefully he'll be available to help should you need anything (you can Private Message him for a small demo file)