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

[Jirka79]

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:

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 :

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!

 

[Fluff]

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

 

[Jirka79]

Hi Fluff,

Thanks for your welcome! You can see my question from the other website here

 

[Jirka79]

Hi all, this thread is solved. Solution is here.

Thanks you all for your time and effort!