Manikandan11
10-28-2023, 06:05 AM
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":
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()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
Private Sub cmdBack_Click()
Unload Me
End Sub
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()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
Private Sub cmdBack_Click()
Unload Me
End Sub