Search In Dialog with Listbox

[ago_med]

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:

Example 2:

Thanks!

.

[mark_t]

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

[ago_med]

yes… mark_t

[ago_med]

the list of items is in the spreadsheet (“Plan1”)…

[ago_med]

there is between (“A1:A400”) at the (“Plan1”)…

[mark_t]

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.

[ago_med]

it’s work perfectly…

thanks a lot mark_t…

you’re help me a lot…