VBA 3 Column Table and Display each column separately in 3 list boxes.

[JulianvO]

Greetings

Background:

Sheet1, tab name: Formula Data
Sheet2, tab name: Formula Search

Sheet1 has a Table (Table1), which has 3 columns, namely, Formula, Example and Explanation
Sheet2 has the same 3 columns.

VBA User form contains the following controls, Textbox (TxtSearch), 3 Command Buttons (BtnSearch), (BtnReset) and (BtnExit).
There are 3 List boxes, listbox1, listbox2 and listbox3.

Object of Application: To Search for a formula, Display Search results in listbox1, if any.
User clicks on the appropriate formula in listbox1 and then the Formula example is displayed in listbox2 and the Explanation is displayed
in listbox3.

SearchResults is in the Name Manager and reads as follows: =OFFSET('Formula Search'!$A$2;0;0;COUNTA('Formula Search'!$A:$A)-1;3)
The following code works, up to listbox1.

<VBA CODE>

Option Explicit

Private Sub BtnClear_Click()

TxtSearch.Text = ""

ListBox1.RowSource = ""

ListBox2.RowSource = ""

TxtSearch.SetFocus

End Sub

Private Sub BtnExit_Click()

Unload Me

End Sub

Private Sub BtnSearch_Click()

Dim RowNum As Long
Dim SearchRow As Long

RowNum = 2
SearchRow = 2

Worksheets("Formula Data").Activate

Do Until Cells(RowNum, 1).Value = ""

If InStr(1, Cells(RowNum, 2).Value, TxtSearch.Value, vbTextCompare) > 0 Then
Worksheets("Formula Search").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
Worksheets("Formula Search").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
Worksheets("Formula Search").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
SearchRow = SearchRow + 1
End If

RowNum = RowNum + 1

Loop

If SearchRow = 2 Then
MsgBox "No formula was found that matches your search criteria."
Exit Sub
End If

TxtSearch.SetFocus

ListBox1.RowSource = "SearchResults"

End Sub

Private Sub UserForm_Initialize()

TxtSearch.SetFocus

Worksheets("Formula Search").Range("A2:C100").ClearContents

End Sub
</VBA CODE>

Can someone assist me with the coding for listboxes 2 and 3?

Thanking you in advance

Julian

 

[John_w]

User clicks on the appropriate formula in listbox1 and then the Formula example is displayed in listbox2 and the Explanation is displayed
in listbox3.

Try:

Private Sub ListBox1_Click()
    ListBox2.Clear
    ListBox2.AddItem Worksheets("Formula Search").Cells(ListBox1.ListIndex + 2, 2).Value
    ListBox3.Clear
    ListBox3.AddItem Worksheets("Formula Search").Cells(ListBox1.ListIndex + 2, 3).Value
End Sub

 

[JulianvO]

Try:

Private Sub ListBox1_Click()
    ListBox2.Clear
    ListBox2.AddItem Worksheets("Formula Search").Cells(ListBox1.ListIndex + 2, 2).Value
    ListBox3.Clear
    ListBox3.AddItem Worksheets("Formula Search").Cells(ListBox1.ListIndex + 2, 3).Value
End Sub

Greetings John_w

Thank you very much. code works like a dream.

Julian