You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
Thanks for replying Deborah. The password problem is now solved, but has created another issue. When protection is re-applied,it comes back on with only minimum default permissions to select locked and unlocked cells. I need users to be able to adjust row and column sizes, insert hyperlinks and edit objects. Any ideas welcome
Angela, record a macro while you protect the worksheet, with all the settings that you want.
Then, add the password to the recorded settings, and put that in the multiple selection drop down macro.
For example:
ws.Protect Password:=”myPwd”, _ DrawingObjects:=False, _ Contents:=True, _ AllowInsertingColumns:=True, _ AllowInsertingRows:=TrueMany thanks Deborah, that works a treat!
Sorry Debbie, too good to be true! For some weird reason this code is messing with date formats on the sheet. My employer needs them set to UK ie: dd/mm/yyy but even when cell formatting is correct (not system dependent)as soon as I apply the code, date fields revert to mm/dd/yyyy BUT only for dates where the day is between the first and the ninth; For example 1st September 2010 displays as 09/01/2010 but 21st displays correctly as 21/09/2011- remove the code and all is well?? Other date formats also appear the wrong way ie: dd mmmm yyyy
Code now looks like:
Option Explicit
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:=”hrmi”
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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 Target.Column = 12 Or Target.Column = 14 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
exitHandler:
ActiveSheet.protect Password:=”hrmi”, DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingHyperlinks:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Application.EnableEvents = True
End Sub
I hope someone can help with this date problem as it’s driving me crazy!! I tried extracting MONTH and YEAR but these are also the wrong way round. I also notice a pile of odd date formats in the Custom format box, similar to: [$-F800]dddd, mmmm dd, yyyy ???????