Data pull failing in VBA code using User Form

[Manikandan11]

FAQ_V5.xlsm-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":

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

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

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

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

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

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":

Private Sub cmdOk_click()FAQ_V5.xlsm
    ' 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

Private Sub cmdBack_Click()
    Unload Me
End Sub