Solved

Auto search plus AutoComplete  dropdown list using VBA Excel

Posted on 2014-04-24
33
11,198 Views
1 Endorsement
Last Modified: 2014-05-23
Hi Team,

I have a list of countries like which I am binding to list in worksheet:


Afghanistan
Albania
Algeria
Bahrain
Bangladesh
Denmark
Djibouti
Haiti
Holy See
Laos
Latvia


# Auto-complete functionality, when user type lets say afg , 'Afghanistan' comes automatically, similarly when user type 'den' 'denmark' appears.

# Auto-search functionality, when user type 'D' in the drop-down all values starting with 'D' should appear so that user can select.

Enclosing sample excel fyr.

Hopefully looking forward to hearing from you.
1
Comment
Question by:satmisha
  • 17
  • 16
33 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 305 total points
Comment Utility
You can do that with a floating ActiveX control. Please see my Magical floating ActiveX control article for details.
0
 

Author Comment

by:satmisha
Comment Utility
Salute to your skills.. Man.. This is exactly what I was looking for...


I have few question on understanding, minor one only:

- Why do we use data validation if we are using combobox.
- Why we require Type.Validation = 3
- Can we use alone data validation to achieve this ?
- Can we use alone combobox to achieve this ?

Looking forward to hearing from you.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
- Why do we use data validation if we are using combobox.
I never really thought about that but it's an easy way to fill the combobox.
- Why we require Type.Validation = 3
That tells Excel you are looking for a string.
- Can we use alone data validation to achieve this ?
- Can we use alone combobox to achieve this ?
I'm not sure what you mean but the floating ActiveX control is the only way I know how to do what you want.
0
 

Author Comment

by:satmisha
Comment Utility
Thanks for your reply.

As you know that we have used Data Validation Plus ActiveX combo box to achieve this. But I guess we can achieve autocomplete + jumping to index(input character by user) without using data validation.

Can we simply use ActiveX control to achieve this( autocomplete + jumping to index(input character by user)  without using Data Validation ?

Looking forward to hearing from you.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
A standard ActiveX combobox, as shown in the attached demo, does that. There is a problem however and that is that ActiveX controls aren't really designed for sheets and you may run into problems where the size of the control changes on it's own.
Autocomplete.xlsm
0
 

Author Closing Comment

by:satmisha
Comment Utility
Thanks a lot genius.. your work exactly justifies your name. :-)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 

Author Comment

by:satmisha
Comment Utility
Hey Pal.. There is one issue..

Whenever I click anywhere else on the sheet. This drop down appears.

I have two three more datavalidation List. I have applied floating combobox on A2 and do have datavalidation on B4 & B10 but when I click on B10 floating dropdown does appear there.

Pls help..
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Please attach your workbook as it is now. Do you want the floating combobox only in A2?
0
 

Author Comment

by:satmisha
Comment Utility
Here enclosing the file. wherever I click on any data-validation list this on worksheet floating drop-down appears.

Looking forward to hearing form you.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Sorry but you forgot the attachment.
0
 

Author Comment

by:satmisha
Comment Utility
Thanks for your reply, enclosing the file.
AutoCompleteCombo.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
If you only want the combo to appear in cell A2 then add lines 23 to 26.

Public Sub ShowAutocomplete(Target As Range)
    Dim strVF As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    
    On Error GoTo errHandler

    Set ws = ActiveSheet
    
    If gbMaintBeingDone Then
        Exit Sub
    End If
    
    Set cboTemp = ws.OLEObjects("TempCombo")
    'On Error Resume Next
    With cboTemp
        ' Clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    
    ' Set the range(s) where you want the combobox to appear
    If Intersect(Target, Range("A2")) Is Nothing Then
        Exit Sub
    End If
    
    If Target.Validation.Type = 3 Then
        ' The cell contains a data validation list
        Application.EnableEvents = False
        ' Get the data validation formula
        strVF = Target.Validation.Formula1
        strVF = Right(strVF, Len(strVF) - 1)
        With cboTemp
            ' Show the combobox with the list
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = strVF
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        ' Open the drop down list automatically
        ActiveSheet.TempCombo.DropDown
    End If
    
    Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub

errHandler:

    Application.EnableEvents = True
    ' If it's 1004 there's no data validation in the cell
    If Err.Number <> 1004 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"
    End If
    
End Sub

Open in new window

0
 

Author Comment

by:satmisha
Comment Utility
Thanks for your prompt reply.

There is one issue. I am giving Combobox Linkcells to merged cells i.e. "=$N$3:$P$3, seems like it is not working.

Looking forward to hearing from you.
0
 

Author Comment

by:satmisha
Comment Utility
Genious I desperately require your help here.... Looking forward to hearing form you. As I totally stuck when I try to use combobox in merged cell..
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Please attach the project you are working on.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Change line 24 above to

    If Target.Address <> "$N$3:$P$3" Then
0
 

Author Comment

by:satmisha
Comment Utility
Attaching sample file here I need to use combo box and need to apply this combo box on merged cells i.e. "$E$2:$G$2" I have applied this in the combo box property and the line that you suggested but appears to me something wrong...

Appreciate your response.

Looking forward to hearing form you.
AutoCompleteCombo.xlsm
0
 

Author Comment

by:satmisha
Comment Utility
when I applied that on merged cell i.e. "$E$2:$G$2", combobox does not disappear.... Pls try to have a look on the sheet once...I must be doing something silly...Attached in above response...

Looking forward to hearing form you.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
OK there were two problems.

1)     You had

If Intersect(Target, Range("$E$2:$G$2")) Is Nothing Then

rather than

If Target.Address <> "$E$2:$G$2" Then

as I suggested in post ID: 40040438.

2) At least one cell in the range E2:G2 did not have data validation. To change that you may have to run the 'Maintenance' macro that I talked about in my article. After you make the change and nothing at all seems to happen when you click the range then go to the Immediate Window, type in Application.EnableEvents = True, press return and click on the range again.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Actually there was a 3rd problem and this one may be mine. The clearing of the combo box should precede the checking of the range, so

    Set cboTemp = ws.OLEObjects("TempCombo")
    'On Error Resume Next
    With cboTemp
        ' Clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    
    ' Set the range(s) where you want the combobox to appear
    If Target.Address <> "$E$2:$G$2" Then
        Exit Sub
    End If

Open in new window

I attached a working wb.
Q-28419581.xlsm
0
 

Author Comment

by:satmisha
Comment Utility
Hi Martin.. Thanks a lot for your help. I am releasing the tool in production but I am little scared.

I was trying to put some code on click of dropdown so that it goes offline means currently when we click on somewhere else after selection of Item in magic dropdown it goes offline but excel does crash.

Looking forward to hearing from you.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I was trying to put some code on click of dropdown so that it goes offline means currently when we click on somewhere else after selection of Item in magic dropdown it goes offline but excel does crash.
I'm not sure what that means but I assume that you are saying that there's still a problem. If so I want to help but to do so I'll need a copy of your actual workbook and a detailed description of how to reproduce the problem.
0
 

Author Comment

by:satmisha
Comment Utility
Thanks Martin for your response.

Here I am trying to write on click of dropdown event to bring the dropdown back to its original position if user clicks on any item in the dropdown. Currently without this event our dropdown remain visible to user till the timi he clicks somewhere else on the worksheet.

for that I have used click event of dropdown but excel does crashes if I try to do that. Enclosing sample file fyr.
Excel-Crash.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
With and without your new ActiveCell.Offset(0, 1).Activate line I could not get Excel to fail. Can you tell me exactly how to do that?

Another way to close the dropdown is this.

Private Sub TempCombo_Click()
    'ActiveCell.Offset(0, 1).Activate
    TempCombo.Visible = False

End Sub

Open in new window

0
 

Author Comment

by:satmisha
Comment Utility
Thanks a lot martin for your prompt reply.
0
 

Author Comment

by:satmisha
Comment Utility
Hey Martin... it stuck in prod... let me redefine the prob...need your urgent attention....

1. Making smart dropdown invisible approach : If user second time clicks on the dropdown, auto-complete feature doesn't work.

2. issue is if user clicks on smart down and does not move away ( i.e. our "Worksheet_SelectionChange" event does not fire ) our smart dropdown remains hanging in the air. in this situation if user navigate to other screen he gets some error.

Is there any way to handle this ? I apologies I engaged you too much in this.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
1. In ShowAutocomplete add line 4

    ' Set the range(s) where you want the combobox to appear
    If Target.Address <> "$E$2:$G$2" Then
        Exit Sub
    End If
    
    ActiveSheet.TempCombo.Visible = True

Open in new window

2. In module ThisWorkbook add
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Float Combo" Then
     Sheets(Sh.Name).TempCombo.Visible = False
End If
End Sub

Open in new window

0
 

Author Comment

by:satmisha
Comment Utility
Hi Martin,
I have incorporated the lines suggested but still the situation is same. If you open the enclosed file and do these steps, you would able to recreate :

1. Click on smart drop down, it will work fine. You select any value.
2. Without clicking anywhere else click on the dropdown again, you wouldn't able to get smart features i.e "AutoComplete" & "Jummping to index".

Am I doing something wrong or shall I consider it as the limitation of smart dropdown ?
Excel-Crash.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Make this change and let me know how it goes.
Private Sub TempCombo_Click()
    'ActiveCell.Offset(0, 1).Activate
    'new
    'TempCombo.Visible = False
    ActiveCell.Select

End Sub

Open in new window

0
 

Author Comment

by:satmisha
Comment Utility
Situation is still same...
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I assume that after making a selection in the combobox and not moving away from it you want to be able to click it again and have the dropdown reappear. I'm sorry but even with a standard, unmodified, ActiveX combobox you can't do that so it is something you'll have to live with.

If you are dissatisfied with that answer I won't object to you requesting attention and asking to have the awarded points removed.

Also no workbook that you have posted has ever crashed for me. If you can post one that does and describe exactly how to cause the crash I try to help (with or without points).
0
 

Author Comment

by:satmisha
Comment Utility
Hey Martin.. I am more than happy with the solution that you provided. There is no point at all to withdraw even a single point here, even you deserve more as I was not aware that I would engage you that much into it.
As far crash is concerned, you have already provided me the solution and it is working fine. Thanks a lot for that.

I was just trying to draw a logical conclusion here as what I am trying is possible or not so that I could setup expectations of user on the same ground i.e. with certain limitation.

Thanks a lot Martin.. will keep in touch base with you . TC
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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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