Prevent ActiveX Combobox change value when selecting list

[akaseto]

hi,
im using combobox to make a region search engine based on this tutorial : Create an Excel Drop Down Search
with some modifications i want to use down arrow to select item from the list but the problem is when i pressed down to list, combobox automaticaly value updated to the 1st suggestions.
sample :

>

how to make/select 2nd suggestions with arrow keys without updating combobox value ? so, combobox list wont change.
this is my code :

Private Sub Desa_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 13
            Dim rRng As Range, c As Range
            Kec.Clear
            Set rRng = ActiveSheet.Range("C32:C36")
                If Cells(32, 2) <> "" Then
                    With rRng
                        For Each c In .Columns(1).Cells
                            If c.Value <> "" Then
                                Kec.AddItem c.Value
                            End If
                        Next c
                    End With
                Else
                    Exit Sub
                End If
            Kec.Activate
        Case Else
            Desa.ListFillRange = "DropDownList"
            Me.Desa.DropDown
        End Select
End Sub

please help!

 

[Akuini]

Here's an example of searchable combobox on a single cell:

deCombobox - sheet,1 cell, searchable,dictionary 2.xlsm

Shared with Dropbox

www.dropbox.com

You need to adjust the code:

'=============== ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "deList"]
Private Const sList As String = "deList"

'cell where the list start [in the sample: cell A2 in sheet "deList" ]
Private Const sCell As String = "A2"

'the linked cell (cell that link to the combobox)
Private Const xCell As String = "B3"

 

[akaseto]

Here's an example of searchable combobox on a single cell:

deCombobox - sheet,1 cell, searchable,dictionary 2.xlsm

Shared with Dropbox

www.dropbox.com

You need to adjust the code:

'=============== ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "deList"]
Private Const sList As String = "deList"

'cell where the list start [in the sample: cell A2 in sheet "deList" ]
Private Const sCell As String = "A2"

'the linked cell (cell that link to the combobox)
Private Const xCell As String = "B3"

AWESOME !!! u r my hero sir !!
now i can get it work.. thanks you so much !!!

 

[akaseto]

If you don't mind, I'd like to ask for an explanation

 

[Akuini]

If you don't mind, I'd like to ask for an explanation

Explanation on which part?

 

[akaseto]

Explanation on which part?

actually all the parts XD

well i can't understand why .List = vList sometimes got error message when i run another clearcontents macro.
and Range(xCell) = .Value wont add value to cell if not listed in vList.

Private Sub ComboBox1_Change()

With ComboBox1
    If .Value <> "" Then
        If IsError(Application.Match(.Value, vList, 0)) Then
        Call get_filterX
           .List = d.keys
           .DropDown
        Else
            Range(xCell) = .Value
        End If
    Else
        Range(xCell) = .Value
[B][I]        [U][COLOR=rgb(226, 80, 65)].List = vList[/COLOR][/U][/I][/B]
    End If
End With
End Sub

 

[Akuini]

why .List = vList sometimes got error message when i run another clearcontents macro.

You mean you have a macro that clearcontents a range? Can you post the macro?

and Range(xCell) = .Value wont add value to cell if not listed in vList.

You mean you want to add a value to the cell even it's not on the list? or you just want an explanation why it happens?

 

[akaseto]

You mean you have a macro that clearcontents a range? Can you post the macro?

just a simple clear contents of cell :

Private Sub refresh()
    Application.ScreenUpdating = False
        Range(Cells(32, 2), Cells(35, 2)).ClearContents
            Range(Cells(7, 5), Cells(17, 8)).ClearContents
                Range(Cells(9, 26), Cells(12, 26)).ClearContents
            Cells(18, 6).ClearContents
nama.Activate
End Sub

You mean you want to add a value to the cell even it's not on the list? or you just want an explanation why it happens?

yes i want to add value, also an explanation sir, please :D

 

[Akuini]

Private Sub refresh()

Where do you put the code? in sheet1 module?
and "nama.Activate" is nama in the same sheet?
It's rather hard to guess without a a sample file.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?

 

[Akuini]

yes i want to add value

Replace the sub with this:

Private Sub ComboBox1_Change()

With ComboBox1
    Range(xCell) = .Value

    If .Value <> "" Then
        If IsError(Application.Match(.Value, vList, 0)) Then
            Call get_filterX
           .List = d.keys
           .DropDown
        End If
    Else
        .List = vList
    End If
End With
End Sub

also an explanation

Explanation of the original code:

Private Sub ComboBox1_Change()

With ComboBox1
    'if combobox value isn't empty 
    If .Value <> "" Then
        'if the value is not on the list (vList) then the code will call get_filterX
        'for example if the value is "a" - which is not on the list - then it will display only items that has "a" in it
        'this line "If IsError(Application.Match(.Value, vList, 0)) Then" means that if it can't find the value on the list then ...
        If IsError(Application.Match(.Value, vList, 0)) Then
            Call get_filterX
           .List = d.keys
           .DropDown
        Else
            'if the value is on the list (vLis) then send the value to the cell
            Range(xCell) = .Value
        End If
    Else
        'if value is empty then show the whole list & the cell will be blank
        Range(xCell) = .Value
        .List = vList
    End If
End With
End Sub