• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autofill and Dependent Combo box in Excel

Jerry57

New Member
Hi,

Good Day!!

I am looking for autofill combo box and also dependent. Attached the sample file column A to D are my data.

Column G I have headings - I am looking for combo box in H2, H3, H4 and H5

H2 = Is countries name
H3 = Is state name
H4 = Is city name
H5 = Zipcode

H2 = Is an Independent
H3= Is dependent with H2
H4 = Is dependent with H2 and H3
H5 = Is dependent with H2, H3 and H4

Hope someone can help me..
 

Attachments

Jerry57

New Member
Thank you for responding.

In terms of dependent dropdown this works fine but auto fill (if you type letter "A" then America should show up in the dropdown) is not working.

I need a combo box with auto fill option enabled and dependent dropdown list as well.
 

p45cal

Well-Known Member
True enough, I didn't answer properly.
The attached has the bare bones of a solution. It needs vba. It can be improved on, especially regarding behaviour with tab, enter & cursor key presses.
It only behaves as a combobox does with autocomplete using only the first characters as you type; I remember years ago writing code for a combobox that would reduce the dropdown selection to choices down to entries that contained (not just at the start) what you typed, but I doubt that I'd be able to find it now.
I've stolen code from https://www.contextures.com/xldataval10.html with some clumsy adaptation for dynamic array ranges (combobox .listFillRange property doesn't seem to accept the P2# type of address).
 

Attachments

Jerry57

New Member
This is nice, almost what I wanted but one last request. Can we eliminate the formula in P, Q and R columns.

Will VBA code work without those formulas in P, Q and R columns.

Thanks in advance
 

p45cal

Well-Known Member
Yes, the attached has just shifted (a slight variation of) those formulae into the code. Lot's of hard-coded ranges and values. It would be easier if the main table was a proper excel Table, also we could use other ways to calculate which formula to apply
 

Attachments

Jerry57

New Member
Thank you so much.

It is working awesome in your file. I tried editing the code to adjust the columns in my file but its not working. Attached actual columns where data are stored. I tried the below code but its not working.. Can you please add the code in the file which I am sharing. It will be really helpful.

>>> use code - tags <<<
Code:
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
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m As Long
With Me.TempCombo
  .Visible = False
  If Not Intersect(Range("B6:B9"), ActiveCell) Is Nothing Then
    Select Case ActiveCell.Row - 1
      Case 1
        x = Evaluate("SORT(UNIQUE(W2:W407))")
      Case 2
        x = Evaluate("SORT(UNIQUE(FILTER(X2:X407,W2:W407=B6)))")
      Case 3
        x = Evaluate("SORT(UNIQUE(FILTER(Y2:Y407,(W2:W407=B7)*(W2:W407=B6))))")
      Case 4
        x = Evaluate("SORT(UNIQUE(FILTER(Z2:Z407,(Y2:Y407=B9)*(X2:X407=B7)*(W2:W407=B6))))")
    End Select
    .Top = ActiveCell.Top
    .Left = ActiveCell.Left
    .Width = ActiveCell.Width + 15
    .Height = ActiveCell.Height + 5
    .List = IIf(IsError(x), Array(Empty), x)
    .LinkedCell = ActiveCell.Address
    .Visible = True
    .Activate
    .DropDown
  End If
End With
End Sub
 

Attachments

Last edited by a moderator:

p45cal

Well-Known Member
You didn't put a combobox called TempCombo in the sheet.
Your formulae were awry.
I adjusted the formula which produces the zip code list to keep it as text. This (a) sorts alphabetically not numerically and (b) allows autocomplete to work when you start entering a code.
I also adjusted so that when multiple entry cells are selected the dropdown doesn't appear (makes it easier to delete entries).
 

Attachments

Jerry57

New Member
Thank you so much. B6 is working fine.. When I try to change other 3 combo box name (B7, B8 and B9) to TempCombo I get the attached error
 

Attachments

Jerry57

New Member
Hi,

Sorry to bother you once again.

I have similar request for entire column now. Can you please change the code to match my requirement.

Entire Column C = Is countries name
Entire Column D = Is state name
Entire Column E = Is city name
Entire Column F = ZipCode

Entire Column C = Is an Independent
Entire Column D = Is dependent with Column C
Entire Column E = Is dependent with Column C and Column D
Entire Column F = Is dependent with Column C, Column D and Column E
 

p45cal

Well-Known Member
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.TempCombo
  .Visible = False
  If Not Intersect(Range("C2:F10"), ActiveCell) Is Nothing And Target.Count = 1 Then
    rw = ActiveCell.Row
    Select Case ActiveCell.Column
      Case 3
        x = Evaluate("SORT(UNIQUE(W2:W407))")    'country
      Case 4
        x = Evaluate("SORT(UNIQUE(FILTER(X2:X407,W2:W407=C" & rw & ")))")    'state
      Case 5
        x = Evaluate("SORT(UNIQUE(FILTER(Y2:Y407,(W2:W407=C" & rw & ")*(X2:X407=D" & rw & "))))")    'City
      Case 6
        x = Evaluate("SORT("""" & UNIQUE(FILTER(Z2:Z407,(W2:W407=C" & rw & ")*(X2:X407=D" & rw & ")*(Y2:Y407=E" & rw & "))))")    'zip
    End Select
    .Top = ActiveCell.Top
    .Left = ActiveCell.Left
    .Width = ActiveCell.Width + 15
    .Height = ActiveCell.Height + 3    '5
    .List = IIf(IsError(x), Array(Empty), x)
    .LinkedCell = ActiveCell.Address
    .Visible = True
    .Activate
    .DropDown
  End If
End With
End Sub
 

Attachments

p45cal

Well-Known Member
Well I haven't changed that at all. Have you added Option Explicit at the top of the code-module?
 

Jerry57

New Member
Attached file were data are stored exactly,

C, D, E and F are headings and data are stored in column: DN.DO,DP and DQ and number of rows are 2356. Could you please help me with code for the attached file.
 

Attachments

Jerry57

New Member
I just copied your code to my actually file without removing.. Only thing I noticed is that it says "Genenral" and "TempCombo_KeyDown" in the above the code. Wherein in your file it says "TempCombo" and "KeyDown". Please see the screen shot at the top of the code
 

Attachments

p45cal

Well-Known Member
The code should be in the sheet concerned's code module and nowhere else. From the sheet concerned, right-click on its tab and choose View code - that's where ther code should be. Remove it from anywhere else.
If this still doesn't work, attach the workbook, I can't just keep guessing.
 
Top