Data pull failing in VBA code using User Form

Attachment 31143-Attachments available for your quick reference-

Dear Expert,

I am trying to build a Excel VBA macro where a user can find an answer for their question (Kind of 'FAQ' thing) with a few clicks or by searching what they want (like keywords) from a UserForm.

I am new to VBA. I will try to explain my idea as clear as possible.

I have been working on this for the past 2 weeks and I'm almost there. But for some obvious reasons, an expert's advice is required to complete it.

There are 2 sheets in my Excel Workbook.

Sheet1- "FAQ_Data4":
Column A: "Reference ID" and
Column B: "Questions and answers"

Questions are in rows that are prefixed by "Q" and followed by the question number (eg: Q1, Q2, Q3).

Answers are in rows that are prefixed by "A_" & the corresponding question's reference ID. (eg: Reference ID for Q1's answer is A_Q1).

Please note:
Some answers might even have one or more tables to it.
So the answer reference ID might be repeated multiple times.
To put it simple, if a user chooses Q5, I want to display all the data (all the columns) that is in the row with the reference ID A_Q5.

Sheet2- "Home":

There is a command button in this sheet - "cmdHome".

When a user clicks the "cmdHome" button, it will open a UserForm (let's call it "UserForm1").

"UserForm1" has the following:

1- "lstQuestions" - A List Box where all the questions are displayed. By default, all the questions are displayed.
2- "txtSearch" - A text box where user can type a keyword to filter the questions in the "lstQuestions" list box.
When you type a keyword, the list of questions will be automatically filtered.
3- "cmdClear" - A Command Button to clear the selection / whatever data that is in the "txtSearch" box.
4- "cmdOk" - A Command Button when clicked will open the second userform (let's call it "UserForm2").

"UserForm2" has the following:

1- "txtQuestions" - A Text Box that displays whatever the question is selected in "UserForm1".
2- "txtAnswers" - A Text Box that displays the answer to that question.
3- "cmdBack" - A Command Button when clicked will close the "UserForm2" and open "UserForm1"

I have researched a lot and did my homework.

I was able to pull the questions from "FAQ_Data4".

I made the "txtSearch", "lstQuestions" text boxes and "cmdClear" command button work just fine as I wanted to. But I am failing to pull the answers from "FAQ_Data4" and display it in "UserForm2" - "txtAnswers".

I don't know where I am going wrong. I greatly appreciate if someone could help me solve this. Apologies if there is any mistakes in the below codes. Please correct me wherever necessary.

Below is the code I have for "UserForm1":

Code:

---

Private Sub UserForm_Initialize()
    ' Populate the List Box with questions from FAQ_Data4
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("FAQ_Data4")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim cell As Range
    For Each cell In ws.Range("B2:B" & lastRow)
        If Left(cell.Offset(0, -1).Value, 1) = "Q" Then
              UserForm1.lstQuestions.AddItem cell.Value
        End If
    Next cell
    ' Select all text in the txtSearch text box when userform is initialized
    Me.txtSearch.SelStart = 0
    Me.txtSearch.SelLength = Len(Me.txtSearch.Text)
    Me.BackColor = RGB(128, 128, 128)
    Me.cmdOK.BackColor = RGB(204, 255, 255)
    Me.cmdClear.BackColor = RGB(204, 255, 255)
End Sub

---

Code:

---

Private Sub lstQuestions_change()
    ' Display the selected question in the text box.
    If Me.lstQuestions.ListIndex >= 0 Then
        Me.txtSearch.Value = Me.lstQuestions.List(Me.lstQuestions.ListIndex)
    End If
End Sub

---

Code:

---

Private Sub cmdOk_click(). 
    ' Handle selection from the List Box or keyword search
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("FAQ_Data4")
    Dim selectedQuestion As String
    selectedQuestion = UserForm1.txtSearch.Value
    ' Check if the Search box is empty
    If Trim(UserForm1.txtSearch.Value) = "" Then
        MsgBox "No Selection made. Please select a question or enter a keyword.", vbExclamation, "Invalid Input"
        Exit Sub
    End If
    If selectedQuestion = "" Then
        ' Handle keyword search
        Dim keyword As String
        keyword = UserForm1.txtSearch.Value
        UserForm1.lstQuestions.Clear
        Dim cell As Range
        For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
              If InStr(1, cell.Value, keyword, vbTextCompare) > 0 Then
                  UserForm1.lstQuestions.AddItem cell.Value
            End If
        Next cell
    Else
          ' Display USerForm2
          UserForm2.txtQuestions.Value = Me.txtSearch.Value
          UserForm2.Show
    End If
End Sub

---

Code:

---

Private Sub txtSearch_Enter()
    ' Select all text in the Search Box when it is clicked
    Me.txtSearch.SelStart = 0
    Me.txtSearch.SelLength = Len(Me.txtSearch.Text)
End Sub

---

Code:

---

Private Sub txtSearch_Change()
    ' Filter question based on keyword
    Dim keyword As String
    keyword = Trim(Me.txtSearch.Value) ' Remove leading and trailing spaces
    ' clear the list Box
    Me.lstQuestions.Clear
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("FAQ_Data4")
    Dim cell As Range
    If keyword = "" Then
        ' If the search box is empty, show all questions
        For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
            If Left(cell.Value, 1) = "Q" Then ' Filter for reference ID
                Me.lstQuestions.AddItem ws.Cells(cell.Row, 2).Value
            End If
        Next cell
    Else
        ' Filter question based on the keyword
        For Each cell In ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
            If Left(cell.Value, 1) = "Q" Then ' Filter for reference ID
                If InStr(1, ws.Cells(cell.Row, 2).Value, keyword, vbTextCompare) > 0 Then
                    Me.lstQuestions.AddItem ws.Cells(cell.Row, 2).Value
                End If
            End If
        Next cell
    End If
End Sub

---

Code:

---

Private Sub cmdClear_Click()
    ' Clear the text in the search text box
    UserForm1.txtSearch.Value = ""
End Sub

---

Below is the code I have for "UserForm2":

Code:

---

Private Sub cmdOk_click()Attachment 31143
    ' Handle selection from the List Box or keyword search
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("FAQ_Data4")
    Dim selectedQuestion As String
    selectedQuestion = UserForm1.txtSearch.Value
    ' Check if the Search box is empty
    If Trim(UserForm1.txtSearch.Value) = "" Then
        MsgBox "No Selection made. Please select a question or enter a keyword.", vbExclamation, "Invalid Input"
        Exit Sub
    End If
    Dim selectedReferenceID As String
    selectedReferenceID = "A_" & selectedQuestion
    ' Display answers in UserForm2
    UserForm2.lstAnswers.Clear
    UserForm2.txtQuestions.Value = selectedQuestion
    ' Find the corresponding answers
    Dim cell As Range
    Dim answerFound As Boolean
    answerFound = False
    For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        If cell.Value = selectedReferenceID Then
              ' Display the answer in UserForm2
              UserForm2.lstAnswers.AddItem ws.Cells(cell.Row, 2).Value
              answerFound = True
        End If
    Next cell
    ' Check if answers were found
    If Not answerFound Then
        MsgBox "No answers found for the selected question.", vbInformation, "No Answers"
    End If
    UserForm2.Show
End Sub

---

Code:

---

Private Sub cmdBack_Click()
    Unload Me
End Sub

---

Welcome to the forum

Please read the FAQ at the link in my signature, especially the part about multi-posting

I added CODE tags to set off and format the macros, but you can use the [#] icon next time

Quote:

---

-Attachments available for your quick reference-

---

For things that require userforms, etc. I've found it's usually best to include an attachment

I can't find them. Instructions to attach are also in my signature

Thanks Paul. Noted.

UserForm2" - "txtAnswers" is not the same as you have in your code...

Code:

---

UserForm2.lstAnswers.Clear


UserForm2.lstAnswers.AddItem ws.Cells(cell.Row, 2).Value

---

Check that the textbox name is correct. HTH. Dave

OMG. How did I miss it? I feel bad. Thanks for pointing it out Dave. I tried it. But still not working.

I changed it from 'lstAnswers' to 'txtAnswers'. Answers are not displayed.

I even tried changing the txt box to list box and kept the original code. Still nothing is displayed.

Code:

---

' Display the answer in UserForm2
UserForm2.TxtAnswers.Value = ws.Cells(cell.Row, 2).Value

---

Add item is used for loading listboxes. HTH. Dave

I added a new function to the first form:

Code:

---

Private Function Answers() As String


    ' Find the corresponding answers
   
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("FAQ_Data4")
   
    ' this is the value that will be returned
    Dim AnswerText As String
    AnswerText = ""
   
    ' find the selected question in the range to get it's id
    Dim QuestionID As String
    QuestionID = ""
   
    Dim cell As Range
    For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
        If cell.Value = Me.txtSearch.Value Then
            QuestionID = cell.Offset(0, -1).Value
            Exit For
        End If
    Next cell
       
    If QuestionID = "" Then
        MsgBox "The selected question could not be found.", vbInformation, "No Answers"
        Answers = AnswerText
        Exit Function
    End If
     
    ' create the answer id
    Dim selectedReferenceID As String
    selectedReferenceID = "A_" & QuestionID


    For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        If cell.Value = selectedReferenceID Then
            ' add each answer found
            AnswerText = AnswerText + vbCr + cell.Offset(0, 1).Value
        End If
    Next cell


    ' Check if answers were found
    If AnswerText = "" Then
        MsgBox "No answers found for the selected question.", vbInformation, "No Answers"
    End If


    Answers = AnswerText
   
End Function

---

which just scans the questions to match the one selected to get the reference id, then scans for the answers for that reference id, creates a string and returns it.
The answer textbox was changed to Multiline to display the answers that are, well, multiple lines. The returned string is then set as the value for the second user form.

this is one way of doing it and it isn't elegant by any means. (edit: added code screenshot)

Thank you so much. Really appreciate it. This works.
What method do you prefer if I have to display multiple columns in the answer box?

I agree, as you mentioned, it is not elegant and also it is pulling only one column. If I pull more columns it will be even more bad.

But at the same time, I need to show the users some tables that are part of the answer. Any method is ok for me.
What is your opinion? Please let me know.

Thank you once again.

You're welcome, glad to lend a hand.

You could change the answer Textbox to a Listbox (it allows for columns), setup the listbox columns. Then have the Answers function return a multi-dimensional array instead of a string.

Some info on using these ideas:
ListBox control, ColumnCount, ColumnWidths properties example | Microsoft Learn
Using arrays (VBA) | Microsoft Learn