Data Validation Combo Box Auto Fill + Multiple Values?

Question

All,

I've been placing a dropdown menu in designated columns in a series of spreadsheets that allows me to select multiple values for cells, each separated by a semicolon (e.g., Bedford, Sybille; Lessing, Doris; Spark, Muriel). Recently it occurred to me that enabling auto-complete in the dropdown menus in those columns would considerably accelerate the process of data entry. But while I've been able to enable the auto complete function for ~single~ values in my dropdown menus by following the procedures described at http://www.contextures.com/, I've been unsuccessful in using auto complete in the dropdown to enter ~multiple~ values with semicolon separators. I've tried combining two clumps of code, one for the multiple values-separated-by-semicolons function (#1) and another for the auto complete function (2)--see below. As far as I can determine, there isn't a conflict in the sets of code, but this may be a blinkered oversight on my part, and it may have been a mistake to try to sandwich them together. Any advice about making this work would be most sincerely and gratefully appreciated.

Many thanks,

J. 

1.  '====================================

Private Sub Worksheet_Change(ByVal Target As Range)

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 = 2 Or Target.Column = 5 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:

  Application.EnableEvents = True

End Sub

2. 

'====================================

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

  Cancel As Boolean)

Dim str As String

Dim cboTemp As OLEObject

Dim ws As Worksheet

Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")

  On Error Resume Next

  With cboTemp

  'clear and hide the combo box

    .ListFillRange = ""

    .LinkedCell = ""

    .Visible = False

  End With

On Error GoTo errHandler

  If Target.Validation.Type = 3 Then

    'if the cell contains a data validation list

    Cancel = True

    Application.EnableEvents = False

    'get the data validation formula

    str = Target.Validation.Formula1

    str = Right(str, Len(str) - 1)

    With cboTemp

      'show the combobox with the list

      .Visible = True

      .Left = Target.Left

      .Top = Target.Top

      .Width = Target.Width + 5

      .Height = Target.Height + 5

      .ListFillRange = str

      .LinkedCell = Target.Address

    End With

    cboTemp.Activate

    'open the drop down list automatically

    Me.TempCombo.DropDown

  End If

errHandler:

  Application.EnableEvents = True

  Exit Sub

End Sub

'=========================================

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String

Dim cboTemp As OLEObject

Dim ws As Worksheet

Set ws = ActiveSheet

Application.EnableEvents = False

Application.ScreenUpdating = True

If Application.CutCopyMode Then

  'allow copying and pasting on the worksheet

  GoTo errHandler

End If

Set cboTemp = ws.OLEObjects("TempCombo")

  On Error Resume Next

  With cboTemp

    .Top = 10

    .Left = 10

    .Width = 0

    .ListFillRange = ""

    .LinkedCell = ""

    .Visible = False

    .Value = ""

  End With

errHandler:

  Application.EnableEvents = True

  Exit Sub

End Sub

'====================================

'Optional code to move to next cell if Tab or Enter are pressed

'from code by Ted Lanham

'***NOTE: if KeyDown causes problems, change to KeyUp

'Table with numbers for other keys such as Right Arrow (39)

'https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx

Private Sub TempCombo_KeyDown(ByVal _

        KeyCode As MSForms.ReturnInteger, _

        ByVal Shift As Integer)

    Select Case KeyCode

        Case 9 'Tab

            ActiveCell.Offset(0, 1).Activate

        Case 13 'Enter

            ActiveCell.Offset(1, 0).Activate

        Case Else

            'do nothing

    End Select

End Sub

'====================================

Answer 1

The problem with autocomplete for multiple items is that autocomplete does not offer a suggested entry until it knows that you have typed the start of a unique string that has been entered before. So if you have previously entered

Bedford, Sybille; Lessing, Doris; Spark, Muriel

Bedford, Sybille; Lessing, Doris; Another, Name

Then you would need to type 

Bedford, Sybille; Lessing, Doris;

and then when you enter an S or an A it would allow you to select the value.

If you have specific combinations you could enter them above the range where you want to enter them, and right-click a cell you want to fill and choose "Pick from drop down list" which will show a set of unique values that have been entered before.

Answer 2

Mr. Deitrick,

Many thanks--this is extremely helpful information. Much obliged.

J. Nicholson