Solved

Microsoft Excel Combo Box Auto complete Help Required

Posted on 2014-07-30
12
589 Views
Last Modified: 2014-08-05
Hi,
I currently have a Microsoft Excel document which uses data validation lists pulled from another worksheet, these work perfectly fine. I needed the ability to auto complete the data validation list entries as they were being added to speed up data entry so found on the internet via Google a work around which uses a Combo Box overlay above the Data Validation and then some VB code to allow you to link the two together and use the auto complete.

The problem I have is, the above works fine when you double click the cell which has a drop down validation list as when you do this the edit mode is through the Combo Box and the auto complete works. However, if you tab across to the cell or press F2 to edit the box it edits the Drop Down validation box and the auto complete fails to work.

Is there any way I can modify this so I can tab across to the cell and press F2 to edit or just edit as a normal cell so the auto complete works and it edits via the Combo Box instead of the Data Validation drop down list?

The example I used was from the following website:

http://www.contextures.com/xlDataVal11.html

Any assistance with the above would be really appreciated.

Regards

Tahir
0
Comment
Question by:Tahir2008
  • 7
  • 4
12 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40232616
The easiest thing would be to lock the cell and when locking the sheet, disable selection of locked cells. Then surround whatever code updates the cell with:

Activesheet.unprotect "yourPassword"
'your code that updates the cell value
Activesheet.protect "yourPassword"

Open in new window


If you need precisely the solution in your question then it would relatively easy to get the tab key to move into the combo box instead of the cell, but the F2 key would take a relatively elaborate hook into windows itself.

Rgds

Jell
0
 
LVL 1

Author Comment

by:Tahir2008
ID: 40232651
The code that is being used at present is the following:

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("AutocompleteCombo")
  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.AutocompleteCombo.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("AutocompleteCombo")
  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

Private Sub AutocompleteCombo_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

Open in new window

0
 
LVL 1

Author Comment

by:Tahir2008
ID: 40232654
How could the above be modified to allow the F2 function to edit the combo box as it currently does by double clicking?
0
 
LVL 1

Author Comment

by:Tahir2008
ID: 40234840
Any more feedback from anyone on this?
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40234873
Try this, made some time ago for something similar.
It is "Datavalidation as you type", or you can select the value.
Select a cell in the Provider range, and a box will pop up.
DataValidationAsYouType.xlsm
0
 
LVL 1

Author Comment

by:Tahir2008
ID: 40234878
Ejgill, that seems to do exactly what I need, its fab.. any background info on how to implement this?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40235305
There are 2 named ranges on the sheets, one maintained by the code, a named userform (the pop up), a worksheet selection change event macro, and a workbook sheet change event macro.
The names are used in the code.

If you are familiar with VBA you can export/import/copy that to your workbook, and change the names and ranges to something that makes sense for the use in that workbook.

If not, I suggest you upload a workbook, then I can do the implementation for you.
If that is not possible (confidential), perhaps we can use an e-mail solution, if that is acceptable.
0
 
LVL 1

Author Comment

by:Tahir2008
ID: 40235448
Can you tell me what this code does under the workbook? Is it needed?

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myRange As Range, mySortRange As Range
If Sh.Name = "Table" Then
    Set myRange = Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    Set mySortRange = myRange.Offset(1).Resize(myRange.Rows.Count - 1, 1)
    ThisWorkbook.Worksheets("Table").Sort.SortFields.Clear
    ThisWorkbook.Worksheets("Table").Sort.SortFields.Add Key:=mySortRange, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Table").Sort
        .SetRange myRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ThisWorkbook.Names("ProviderList").RefersTo = "=Table!" & myRange.Address
End If

End Sub
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40236051
The code sort the validation list on sheet Table when changes are made, and update the range name (ProviderList) reference.

Yes it is needed to make it dynamic.
If the list is static the code can be removed.
0
 
LVL 1

Author Comment

by:Tahir2008
ID: 40236329
But if I have a dynamic named range then it seems to update the list details correctly when changes are made without the code, this is the reason I asked.
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40236580
Yes, that will work, but the code does more than update the range for the name.

If values are changed, or added below, the values are sorted to display in the correct order for the data validation.
Even values added anywhere in the column will get in the list, but then the range should be set once again, just before ThisWorkbook.Names... to eliminate blanks at the end of the validation list.

Using a dynamic named range, and manually sort the list will do the same.
0
 
LVL 1

Author Closing Comment

by:Tahir2008
ID: 40241129
Thanks for the assistance, much appreciated, this did the trick.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now