Drop down list, selecting multiple items

[Kiwi den]

Hi all,

I amlooking for info on how to set up a drop down box, that will allow the user to select multiple options from the list.
Can anyone point me to a solution please

I would like cell A:1 to have drop down and a list of items appears, if on is selected then this appears in A:2, if another is selected it will appear in A;3 and so onhopefully no more than 15 items per list. I see the list being on a seperate sheet.


Tanks in advance

 

[L. Howard]

Give this a try, copy to sheet 1 code nodule.

With your drop down in A1 of sheet 1.
Compiles a list on sheet 2, column F, as well as on sheet 1 column K with a date in column L.

When this list on sheet 2 gets to 15 entries, you get a warning.
The ranges are easily changed to suit your workbook. (You probably don't want the sheet 1 list & date, but it shows another option)

If you want to make a selection in the drop down unrepeatable, only able to select it once, that is possible also.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Intersect(Target, Range("$A$1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim sCount As Long
Dim sRng As Range

Set sRng = Worksheets("Sheet2").Range("F1:F17")
sCount = Application.WorksheetFunction.CountA(sRng)

 If sCount > 14 Then
   MsgBox "15 limit alert!"
   Exit Sub
  End If

    Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp)(2) = Target
    
    Target.Copy Range("K" & Rows.Count).End(xlUp)(2)
    Range("K" & Rows.Count).End(xlUp).Offset(, 1) = Date '(2)
    
  With Sheets("Sheet2")

      sCount = Application.WorksheetFunction.CountA(sRng)

    If sCount = 15 Then
      MsgBox sCount & " Is the max you wanted??"
    End If
   
  End With
   
End Sub

 

[mtugahan]

Hi Howard. Can you help me creating a drop down list that can select multiple options? The options selected should appear on the same column but in a different cell/row on that column. Please help me Thanks in advance.

Give this a try, copy to sheet 1 code nodule.

With your drop down in A1 of sheet 1.
Compiles a list on sheet 2, column F, as well as on sheet 1 column K with a date in column L.

When this list on sheet 2 gets to 15 entries, you get a warning.
The ranges are easily changed to suit your workbook. (You probably don't want the sheet 1 list & date, but it shows another option)

If you want to make a selection in the drop down unrepeatable, only able to select it once, that is possible also.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Intersect(Target, Range("$A$1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim sCount As Long
Dim sRng As Range

Set sRng = Worksheets("Sheet2").Range("F1:F17")
sCount = Application.WorksheetFunction.CountA(sRng)

 If sCount > 14 Then
   MsgBox "15 limit alert!"
   Exit Sub
  End If

    Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp)(2) = Target
    
    Target.Copy Range("K" & Rows.Count).End(xlUp)(2)
    Range("K" & Rows.Count).End(xlUp).Offset(, 1) = Date '(2)
    
  With Sheets("Sheet2")

      sCount = Application.WorksheetFunction.CountA(sRng)

    If sCount = 15 Then
      MsgBox sCount & " Is the max you wanted??"
    End If
   
  End With
   
End Sub

 

[My Aswer Is This]

I do not believe you can select multiple items in a Data validation drop down list.
But you could select them one at a time and each chosen value would go in the next row.
To do what you want could be done in a Activex listbox
Try this:

In this example the drop down list in in column (5) Row (1)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target.Row = 1 Then Target.Offset(Cells(Rows.Count, "E").End(xlUp).Row).Value = Target.Value
End Sub

 

[mtugahan]

Yes, we can select one at a time only. How to create a drop down list that the chosen value would go to the next cell in the same column? Please I need your help

I do not believe you can select multiple items in a Data validation drop down list.
But you could select them one at a time and each chosen value would go in the next row.
To do what you want could be done in a Activex listbox
Try this:

In this example the drop down list in in column (5) Row (1)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target.Row = 1 Then Target.Offset(Cells(Rows.Count, "E").End(xlUp).Row).Value = Target.Value
End Sub

 

[My Aswer Is This]

I just gave you a script that will do that in post 5

 

[mtugahan]

OH YES! Thanks for this one. But if i have multiple columns, does this works too? How to add new code to new column by the way? thank you so much

I just gave you a script that will do that in post 5

 

[My Aswer Is This]

Try this:
This script works on columns 5 7 and 12:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Or Target.Column = 7 Or Target.Column = 12 And Target.Row = 1 Then Target.Offset(Cells(Rows.Count, Target.Column).End(xlUp).Row).Value = Target.Value
End Sub

 

[mtugahan]

I tried it but it gives me an error. By the way, here's details i want to do.

Master List Scope of Work	HOUSE 1 NEEDS:	HOUSE 2 NEEDS:	HOUSE 3 NEEDS:
*General*
Check attic for active leaks and open penetrations/repair
Unfasten ceiling and vanity lights prior to painting
Remove all electrical cover-plates prioro to painting
All doors must function properly-adjust/trim
Check All plumbing/drains and report issues to PM
Install straight door stops for all doors
Cut all blinds to length
Paint wall and ceiling registers-white
Paint floor registers-Brown
Vacuum debris from all floor vents
GC to perform full construction celan after punch out is complete
Remove alarm equipment from the house and patch I want to put my drop down list under each Header (HOUSE 1, 2 ,3 NEEDS). is it possible?

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Try this:
This script works on columns 5 7 and 12:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Or Target.Column = 7 Or Target.Column = 12 And Target.Row = 1 Then Target.Offset(Cells(Rows.Count, Target.Column).End(xlUp).Row).Value = Target.Value
End Sub

 

[mtugahan]

the list will come from the general scope of work (in the left), I really appreciate you reply sir