Solved

Auto search plus AutoComplete  dropdown list using VBA Excel

Posted on 2014-04-24
33
12,282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 16
33 Comments
 
LVL 48

Accepted Solution

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

Author Comment

by:satmisha
ID: 40023242
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 48

Expert Comment

by:Martin Liss
ID: 40023350
- 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:satmisha
ID: 40025768
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 48

Expert Comment

by:Martin Liss
ID: 40026041
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
ID: 40026251
Thanks a lot genius.. your work exactly justifies your name. :-)
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40026281
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
ID: 40036425
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 48

Expert Comment

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

Author Comment

by:satmisha
ID: 40039167
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 48

Expert Comment

by:Martin Liss
ID: 40039272
Sorry but you forgot the attachment.
0
 

Author Comment

by:satmisha
ID: 40039279
Thanks for your reply, enclosing the file.
AutoCompleteCombo.xlsm
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40039348
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
ID: 40039414
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
ID: 40040356
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 48

Expert Comment

by:Martin Liss
ID: 40040430
Please attach the project you are working on.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40040438
Change line 24 above to

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

Author Comment

by:satmisha
ID: 40040669
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
ID: 40040681
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 48

Expert Comment

by:Martin Liss
ID: 40040770
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 48

Expert Comment

by:Martin Liss
ID: 40040780
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
ID: 40072049
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 48

Expert Comment

by:Martin Liss
ID: 40072129
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
ID: 40072840
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 48

Expert Comment

by:Martin Liss
ID: 40073359
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
ID: 40073523
Thanks a lot martin for your prompt reply.
0
 

Author Comment

by:satmisha
ID: 40081569
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 48

Expert Comment

by:Martin Liss
ID: 40081631
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
ID: 40084659
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 48

Expert Comment

by:Martin Liss
ID: 40085038
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
ID: 40085502
Situation is still same...
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40086483
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
ID: 40087535
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

696 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