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 :
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!
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 :
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!