Ask Your Question
0

Search In Dialog with Listbox

asked 2016-09-30 02:29:36 +0200

ago_med gravatar image

updated 2016-09-30 04:19:00 +0200

mark_t gravatar image

Good night..

I created a dialog ("Dialogo") with two listbox and a textbox....

I need to when i initiate a search on textbox ("TextField_Pesquisa"), automatically show in the listbox ("ListBox_Itens") below the results of the search...

The images below show what i need...

Example 1: image description

Example 2: image description

Thanks!

.

edit retag flag offensive close merge delete

Comments

Is the complete item list going to be held in a range of cells on a spreadsheet?

mark_t gravatar imagemark_t ( 2016-09-30 02:52:37 +0200 )edit

yes... mark_t

ago_med gravatar imageago_med ( 2016-09-30 02:55:23 +0200 )edit

the list of items is in the spreadsheet ("Plan1")...

ago_med gravatar imageago_med ( 2016-09-30 02:55:58 +0200 )edit

there is between ("A1:A400") at the ("Plan1")....

ago_med gravatar imageago_med ( 2016-09-30 03:06:11 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2016-09-30 04:00:36 +0200

mark_t gravatar image

updated 2016-09-30 20:26:41 +0200

First when you set up the dialog you need to initialise the list box with the full list.

Sub Main

    Dim vControl As Variant

    MySampleDialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)

    vControl = MySampleDialog.getControl("ListBox_Previa")
    vControl.selectItemPos(0, True)

    Dim vListBox As Object
    Dim oSheet As Object
    Dim oRange As Object
    Dim i As Integer
    Dim s As String

    vListBox = MySampleDialog.getControl("ListBox_Itens")

    oSheet = ThisComponent.Sheets.getByName("Plan1")
    oRange = oSheet.getCellRangeByName("A1:A400").RangeAddress

    If vListBox.ItemCount > 0 Then vListBox.removeItems(0, vListBox.ItemCount)

    For i = 0 To oRange.EndRow - oRange.StartRow
        s = oSheet.getCellByPosition(oRange.StartColumn, oRange.StartRow + i).String
        vListBox.addItem(s, i)
    Next i

    MySampleDialog.execute()
End Sub

Then assign the following macro to the event for key released within the Textfield.

Sub TextChanged(oEvent As Object)
'   Assign this macro to the Key released event of the text field'

    Dim vTextField As Variant
    Dim vListBox As Object
    Dim oSheet As Object
    Dim oRange As Object
    Dim i As Integer
    Dim ip As Integer
    Dim s As String
    Dim sFilter AS String

    vTextField = oEvent.Source

    sFilter = vTextField.Text

    vListBox = vTextField.Context.getControl("ListBox_Itens")

    oSheet = ThisComponent.Sheets.getByName("Plan1")
    oRange = oSheet.getCellRangeByName("A1:A400").RangeAddress

    If vListBox.ItemCount > 0 Then vListBox.removeItems(0, vListBox.ItemCount)

    ip = 0

    For i = 0 To oRange.EndRow - oRange.StartRow
        s = oSheet.getCellByPosition(oRange.StartColumn, oRange.StartRow + i).String
'       If instr(s, sFilter) > 0 Then         '
        If instr(s, sFilter) = 1 Then
            vListBox.addItem(s, ip)
            ip = ip + 1
        End If
    Next i
End Sub

Using instr() = 1 for the filter will not be case sensitive but the entered text must be at the start of the text from the list. Change to instr() > 0 would allow the search string any position in the text.

The macro is executed after every key entered into the text field so it could get slow as the ammount of data to filter is increased.

Note. I'm not sure if its always safe to use ThisComponent in the dialog macros. It seems to work ok, but probably only because the document is active. If I find a safer method I'll edit this post.

Edit the second macro above to simplify the method of finding the named listbox from the controls of the dialog, now that I realise that getControl method is applied to Context and not the array of Controls.

edit flag offensive delete link more

Comments

it's work perfectly...

thanks a lot mark_t....

you're help me a lot...

ago_med gravatar imageago_med ( 2016-09-30 04:32:08 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-30 02:29:36 +0200

Seen: 252 times

Last updated: Sep 30 '16