Array combobox list display, showing the value you are introducing always in the top and see the subsequent values below

Jirka79

New Member
Joined
Dec 9, 2020
Messages
32
Office Version
  1. 2010
Platform
  1. Windows
Hello everybody,

I'm trying to figure out how to change the display options of a combobox list. As it is now, the actual combobox list goes to the bottom when starts to match with an existing value while you are in the process to introduce a new value.

In this example I start to introduce the value "M6" and goes to the bottom of the displayed values that starts to match with the value that I introduce, but what I would actually like, is to see as first the value that starts to match in the existing list on the top and below the other values as you can see attached:

Combobox list Sample.png


They tried to help me in another website, but because of my existing code they told me that seems unlike it to do it. My Excel has a combobox attached to the active sheet and the source of the values, are from a data validation list.

The following code is in the Sheet module :

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Set Tgt = Target.Cells(1, 1)
Set ws = ActiveSheet
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Cancel = True
End If

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Tgt.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Tgt.Left - 10
      .Top = Target.Top
      .Width = 160
      .Height = 20
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Tgt.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.ListIndex = -1
    Me.TempCombo.DropDown
            newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
    'Call RunOsk_on64Bit 'Calls the windows keyboard. This is a macro I have in another module
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

'Call Closekeyboard 'Close the windows keyboard. This is a macro I have in another module

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 20
    .Left = 20
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

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

This code was taken from this website Excel Data Validation Combo box using Named Ranges and I just did few changes to adapt it in my way.

So, is there a way to expand the list as I want that can be introduced in the existing code I have?

Thank you all in advance!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi & welcome to MrExcel
They tried to help me in another website
Can you please supply a link to your question on the other site, as per board rules.
Thanks
 
Upvote 0
Hi all, this thread is solved. Solution is here.

Thanks you all for your time and effort!
 
Upvote 0
Solution

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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
Back
Top