AUTOCOMPLETE in combobox

[sal21]

Code: Select all

Const CB_FINDSTRING = &H14C

Public Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long

Public Enum EnumKarakter
Asli = 0
Ubah = 1
End Enum

Code: Select all

Private Sub CSTRADE_KeyPress(KeyAscii As Integer)

KeyAscii = AutoComplete(CSTRADE, KeyAscii, False, Asli)

End Sub

Code: Select all

Public Function AutoComplete( _
cbCombo As ComboBox, _
sKeyAscii As Integer, _
Optional bUpperCase As Boolean = True, _
Optional cCharacter As EnumKarakter = Asli) _
As Integer
Dim lngFind As Long, intPos As Integer
Dim intLength As Integer, tStr As String
With cbCombo
If sKeyAscii = 8 Then
If .SelStart = 0 Then Exit Function
.SelStart = .SelStart - 1
.SelLength = 32000
.SelText = ""
Else
intPos = .SelStart
tStr = .Text
If bUpperCase = True Then
.SelText = UCase(Chr(sKeyAscii))
Else
.SelText = (Chr(sKeyAscii))
End If
End If

lngFind = SendMessage(.hwnd, CB_FINDSTRING, 0, _
ByVal .Text)

If lngFind = -1 Then
Exit Function
Else
intPos = .SelStart
intLength = Len(.List(lngFind)) - Len(.Text)
If cCharacter = Ubah Then
.SelText = .SelText & Right(.List(lngFind), _
intLength)
Else
.Text = .List(lngFind)
End If
.SelStart = intPos
.SelLength = intLength
End If
End With
End Function

error in image, based this line:
lngFind = SendMessage(.hwnd, CB_FINDSTRING, 0, _
ByVal .Text)

[HansV]

I don't know why you need this, but does it help if you change the lines

Code: Select all

lngFind = SendMessage(.hwnd, CB_FINDSTRING, 0, _
ByVal .Text)

to

Code: Select all

lngFind = SendMessage(.hwnd, CB_FINDSTRING, 0, tStr)

[sal21]

I don't know why you need this, but does it help if you change the lines

Code: Select all

lngFind = SendMessage(.hwnd, CB_FINDSTRING, 0, _
ByVal .Text)

to

Code: Select all

lngFind = SendMessage(.hwnd, CB_FINDSTRING, 0, tStr)

HUMMM...

in effect i need to show a list, of copmbobox based a key press, example:

Via I

show this list from the combobox
Via Italia
Via Italiana
Via Italiano
...

ecc

[HansV]

I'm afraid I cannot help you with this.

[DocAElstein]

Hello,
I can’t give a full solution here, but what I give might give some ideas on how to get a solution. ( It might not as well, lol. ! :) )
I can’t give a full solution here for two reasons
_1 I suspect the question required is for Visual basic “box things” and my solution here is for VBA
_2 I don’t fully understand the original coding and original problem

But, what I offer does
_ (i) solve this sort of thing…..

....in effect I need to show a list, of combobox based a key press, example:
Via I
show this list from the combobox
Via Italia
Via Italiana
Via Italiano
... etc

..
_ (ii) I am using “box things” and “List things” in VBA UserForms, which, if I am not mistaken?, are often similarly to the “box things” in Visual basic? So I am guessing my solution could be adaptable to Visual Basic “box things” ???

My solution here is a simplified version of something I use almost every day to get a form of AutoComplete / Intellisense to work on large column lists of data. It involves using a VBA Userforms ListBox and TextBox. That is why I thought it was worth posting as it might give an idea how to do it with a Visual Basic Combobox thing: I am taking a guess they may work similarly and have similar coding possibilities???


The key to this solution working is a particular Event coding thing , ( which to me very annoyingly is not available to a cell in VBA). This following particular Event coding thing does appear to be available to a text box, - the Private Sub TextBox1_Change() thing. This particular Event coding thing allows you to go off and do something every time a single character is added to the text box, be it the first character, or any further characters as you type further. For something like a pseudo AutoComplete or pseudo Intellisense, this is very useful:
You can probably guess what I go off and do every time a further character is added:
I go off and search my long column list for words containing the text so far typed.

So the end effect is, for example, in the enclosed demo file, I type in the Text Box
Via I
, and in a ListBox I get the offerings of
Via Italia
Via Italiana
Via Italiano
Via Inverness to Thurso and Wick
via i.JPG
https://i.postimg.cc/sXqKKG3t/via-i.jpg

via i.JPG

That’s the main thing that the code example does. This is the main important coding:

Code: Select all

 Private Sub TextBox1_Change()
Me.ListBox1.Clear

'Find (Next)  https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
Dim rngFnd As Range, Lr As Long
 Let Lr = Range("A" & Rows.Count & "").End(xlUp).Row + 1 '  +1 is required to make the search work, that is to say terminate if our last found cell was the last one
 Set rngFnd = Range("A1:A" & Lr & "").Find(What:=Me.TextBox1.Text & "*", after:=Range("A" & Lr & ""), LookIn:=xlValues, LookAt:=xlPart)
    If rngFnd Is Nothing Then Exit Sub
    Do While Not rngFnd Is Nothing
     '                Debug.Print rngFnd.Value ' or  Do anything you wanna do                                                                                         http://www.youtuberepeater.com/watch?v=8GoN-y9irn4&name=Eddie+and+the+Hot+Rods+Do+anything+you+wanna
     Me.ListBox1.AddItem rngFnd.Row                                             'Row number
     Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = rngFnd.Value              'Name
     Set rngFnd = Range("A" & rngFnd.Row + 1 & ":A" & Lr & "").Find(What:=Me.TextBox1.Text & "*", after:=Range("A" & Lr & ""), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)   '  https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-find-method-excel
    Loop
End Sub

( There is some other coding in the file, but that is more to do with just making the file a working demo of the idea, For example:- If you select something from the list suggrested in the List Box, and then terminate/ close the userform, then some stuff is done in a Private Sub UserForm_Terminate() with the selected text, but you can fiddle around with that to do what you want )


Alan

_._________________________________________________________________________

PseudoIntellisenseAutoCompleteSearch.xls https://app.box.com/s/oems24yinmnophvvpovgd1mwz5q49fbq

_.____________________________________________________________________________
Ref
https://www.excelforum.com/excel-progra ... ost4382748
https://www.excelforum.com/excel-progra ... ost4660761
Info given to me from Alpha Frog

[sal21]

Hello,
I can’t give a full solution here, but what I give might give some ideas on how to get a solution. ( It might not as well, lol. ! :) )
I can’t give a full solution here for two reasons
_1 I suspect the question required is for Visual basic “box things” and my solution here is for VBA
_2 I don’t fully understand the original coding and original problem

But, what I offer does
_ (i) solve this sort of thing…..

....in effect I need to show a list, of combobox based a key press, example:
Via I
show this list from the combobox
Via Italia
Via Italiana
Via Italiano
... etc

..
_ (ii) I am using “box things” and “List things” in VBA UserForms, which, if I am not mistaken?, are often similarly to the “box things” in Visual basic? So I am guessing my solution could be adaptable to Visual Basic “box things” ???

My solution here is a simplified version of something I use almost every day to get a form of AutoComplete / Intellisense to work on large column lists of data. It involves using a VBA Userforms ListBox and TextBox. That is why I thought it was worth posting as it might give an idea how to do it with a Visual Basic Combobox thing: I am taking a guess they may work similarly and have similar coding possibilities???


The key to this solution working is a particular Event coding thing , ( which to me very annoyingly is not available to a cell in VBA). This following particular Event coding thing does appear to be available to a text box, - the Private Sub TextBox1_Change() thing. This particular Event coding thing allows you to go off and do something every time a single character is added to the text box, be it the first character, or any further characters as you type further. For something like a pseudo AutoComplete or pseudo Intellisense, this is very useful:
You can probably guess what I go off and do every time a further character is added:
I go off and search my long column list for words containing the text so far typed.

So the end effect is, for example, in the enclosed demo file, I type in the Text Box
Via I
, and in a ListBox I get the offerings of
Via Italia
Via Italiana
Via Italiano
Via Inverness to Thurso and Wick
via i.JPG
https://i.postimg.cc/sXqKKG3t/via-i.jpg
via i.JPG


That’s the main thing that the code example does. This is the main important coding:

Code: Select all

 Private Sub TextBox1_Change()
Me.ListBox1.Clear

'Find (Next)  https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
Dim rngFnd As Range, Lr As Long
 Let Lr = Range("A" & Rows.Count & "").End(xlUp).Row + 1 '  +1 is required to make the search work, that is to say terminate if our last found cell was the last one
 Set rngFnd = Range("A1:A" & Lr & "").Find(What:=Me.TextBox1.Text & "*", after:=Range("A" & Lr & ""), LookIn:=xlValues, LookAt:=xlPart)
    If rngFnd Is Nothing Then Exit Sub
    Do While Not rngFnd Is Nothing
     '                Debug.Print rngFnd.Value ' or  Do anything you wanna do                                                                                         http://www.youtuberepeater.com/watch?v=8GoN-y9irn4&name=Eddie+and+the+Hot+Rods+Do+anything+you+wanna
     Me.ListBox1.AddItem rngFnd.Row                                             'Row number
     Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = rngFnd.Value              'Name
     Set rngFnd = Range("A" & rngFnd.Row + 1 & ":A" & Lr & "").Find(What:=Me.TextBox1.Text & "*", after:=Range("A" & Lr & ""), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)   '  https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-find-method-excel
    Loop
End Sub

( There is some other coding in the file, but that is more to do with just making the file a working demo of the idea, For example:- If you select something from the list suggrested in the List Box, and then terminate/ close the userform, then some stuff is done in a Private Sub UserForm_Terminate() with the selected text, but you can fiddle around with that to do what you want )


Alan

_._________________________________________________________________________

PseudoIntellisenseAutoCompleteSearch.xls https://app.box.com/s/oems24yinmnophvvpovgd1mwz5q49fbq

_.____________________________________________________________________________
Ref
https://www.excelforum.com/excel-progra ... ost4382748
https://www.excelforum.com/excel-progra ... ost4660761
Info given to me from Alpha Frog

SORRY for delay.
very Nice

[SpeakEasy]

I do have a fairly short VB6 solution to this that I wrote some years ago. I'll try and dig it out when I return from holiday...

[sal21]

I do have a fairly short VB6 solution to this that I wrote some years ago. I'll try and dig it out when I return from holiday...

yes, tkx
i'm interestsed