Prevent ActiveX Combobox change value when selecting list

akaseto

New Member
Joined
Oct 10, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 :
Annotation 2021-10-11 044121.jpg
>
Annotation 2021-10-11 044109.jpg


how to make/select 2nd suggestions with arrow keys without updating combobox value ? so, combobox list wont change.
this is my code :
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
Here's an example of searchable combobox on a single cell:

You need to adjust the code:

Rich (BB 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"
 
Solution

akaseto

New Member
Joined
Oct 10, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Here's an example of searchable combobox on a single cell:

You need to adjust the code:

Rich (BB 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

New Member
Joined
Oct 10, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you don't mind, I'd like to ask for an explanation
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you don't mind, I'd like to ask for an explanation
Explanation on which part?
 

akaseto

New Member
Joined
Oct 10, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Oct 10, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You mean you have a macro that clearcontents a range? Can you post the macro?
just a simple clear contents of cell :
VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
yes i want to add value
Replace the sub with this:
VBA Code:
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:
Rich (BB 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,125
Messages
5,768,250
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top