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

[Paul_Hossler]

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

-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

[Manikandan11]

Thanks Paul. Noted.

[Dave]

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

UserForm2.lstAnswers.Clear


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

Check that the textbox name is correct. HTH. Dave

[Manikandan11]

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.

[Dave]

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

Add item is used for loading listboxes. HTH. Dave

[jdelano]

I added a new function to the first form:

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)

[Manikandan11]

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.

[jdelano]

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