Consulting

Results 1 to 9 of 9

Thread: Data pull failing in VBA code using User Form

  1. #1

    Question Data pull failing in VBA code using User Form

    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
    Last edited by Manikandan11; 10-28-2023 at 08:28 AM. Reason: Missed the attachment

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,746
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Thanks Paul. Noted.

  4. #4
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    848
    Location
    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

  5. #5
    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.

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    848
    Location
    ' Display the answer in UserForm2
    UserForm2.TxtAnswers.Value = ws.Cells(cell.Row, 2).Value
    Add item is used for loading listboxes. HTH. Dave

  7. #7
    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)
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    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.

  9. #9
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •