edit-list box

[Yogi39]

Trying to make an edit-list box, using a form, listbox and a text box.
found some code here...

http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=421

will this work in an Excel macro ?
I cant seem to mod it to function.
can this be done ?

 

[rmikesmith]

Yogi39,

Here are the pertinent modifications and additions I used to get this to compile and run under Excel VBA (XL2000):

Private Declare Function SendMessage Lib "User32" Alias "SendMessageA" (ByVal _
    HWnd As Long, ByVal msg As Long, ByVal wParam As Long, _
    lParam As Any) As Long
Const LB_FINDSTRING = &H18F

Private Declare Function GetFocus Lib "user32.dll" () As Long

Private Declare Function IsWindow Lib "user32.dll" (ByVal HWnd As Long) As Long


' this is a form-level variable
Dim DoSearch As Integer
Dim HWnd As Long


Sub List1_Click()
    ' user selected a new item
    ' (also activated by Text1_KeyDown)
    Text1.Text = List1.Text
End Sub


Private Sub List1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
   ' keep the two controls synchronized
    Text1.Text = List1.Text
End Sub


Private Sub List1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' keep the two controls synchronized
    Text1.Text = List1.Text
End Sub


Sub Text1_Change()
    Static active As Boolean
    Dim index As Integer
    Dim search As String
    Dim found As Integer

    ' avoid recursive calls
    If active Or DoSearch = False Then Exit Sub
    active = True
    ' search is not case sensitive
    search = UCase$(Text1.Text)
    found = -1
    
    ' search the first item in the listbox
    ' that matches the search string in the textbox
    found = SendMessage(HWnd, LB_FINDSTRING, -1, ByVal search)
    If found >= 0 Then
        ' make the found value the current item
        List1.ListIndex = found
        Text1.Text = List1
        ' select the remaining characters
        Text1.SelStart = Len(search)
        Text1.SelLength = 999
    End If
    active = False
End Sub


Private Sub Text1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Shift Then
        ' do nothing if a shift key is pressed
    ElseIf KeyCode = vbKeyUp Then
        ' move on previous item
        If List1.ListIndex > 0 Then
            List1.ListIndex = List1.ListIndex - 1
        End If
        KeyCode = 0
    ElseIf KeyCode = vbKeyDown Then
        ' move on next item
        If List1.ListIndex < List1.ListCount - 1 Then
            List1.ListIndex = List1.ListIndex + 1
        End If
        KeyCode = 0
    End If
End Sub


Private Sub Text1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    If KeyAscii = 8 And Text1.SelStart > 0 Then
        ' if BackSpace has be pressed, trim one
        ' character off the search string
        DoSearch = False
        Text1.Text = Left$(Text1.Text, Text1.SelStart)
        Text1.SelStart = Len(Text1.Text)
    Else
        DoSearch = True
    End If
End Sub

Private Sub UserForm_Initialize()
  Me.List1.SetFocus
  HWnd = GetFocus
End Sub

I can send you a copy of the workbook if you like; just post your email.

Note: This did not behave as advertised; for example, I used entries such as Item1, Item2, Item3, etc. When I typed an initial I into the Textbox, Item1 did appear with tem1 highlighted (so far, so good). As I continued to type, the begining of the highlighted section moved to the next character (OK). When I tried to type a 9 instead of the 1 (Item9 was in the list) it wouldn't accept it! Other anomalies as well but I don't have time to detail right now.

Regards,
Mike

 

[Yogi39]

Will tryto cutand paste. However please foward me a working model.

Thanks for the help.
Cant tell you how much time you saved me!


yogi_36@hotmail.com

 

[Yogi39]

PS I can send you a working work book with a similar
working "dialogsheet".
Maybee easier to convert the dialog to a form and mod the code ?

 

[rmikesmith]

For anyone who stumbles on this thread -- the linked Edit-ListBox code I posted doesn't really work. The code was originally from the VB2TheMax website for VB implementation, and my intent was to tweak it so it would work in Excel. If you try it out you will find that it compiles OK but the Edit-ListBox doesn't function properly. I didn't have time to look into it and then Yogi39 forwarded a demo workbook written by Stephen Bullen, a Microsoft MVP from the UK. His demo was for pre 97 versions of Excel that use Dialog Sheets, but worked very nicely. I have converted this to a Userform implementation for Excel97 and later. If you are interested in a copy, please post your email address.

Regards,
Mike