• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need help with code please!!

I have created a little database file. This has the functionality of searching information(previously set in an table that is in sheet called "Guests". The code I set works perfectly when you search by "Guest Name" and when searching by "Room Number". You can see there are two more TextBoxes in the UserForm. I need that those three TextBoxes(TextBox2, Textbox3 and TextBox4) to display the corresponding data that appears in the ListBox Below. I mean...if I search a name in TextBox1, I want that TextBoxes 2, 3 and 4 to display that data(Room Number, Arrival Date and Departure Date. The same situation must happens if I only search by Room Number(TextBox1, TextBox3 and TextBox4) should display info according to the ListBox1. Can it be done? If it is so, I need to help me how to do it. Thank you very much. I am sending the file attachment and some screenshots for a better understanding of what I need. Also I am attaching the code I used in the Complete UserForm.

Thank you so much.
Code:
Private Sub CommandButton1_Click()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox1.SetFocus
TextBox1.BackColor = vbWhite
TextBox2.BackColor = vbWhite
TextBox3.BackColor = vbWhite
TextBox4.BackColor = vbWhite
End Sub


Private Sub CommandButton2_Click()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox1.SetFocus
TextBox1.BackColor = vbWhite
TextBox2.BackColor = vbWhite
TextBox3.BackColor = vbWhite
TextBox4.BackColor = vbWhite
End Sub


Private Sub TextBox1_Change()
TextBox1.BackColor = vbCyan
With ListBox1
.List = Sheets("Guests").ListObjects(1).DataBodyRange.Value
    For i = .ListCount - 1 To 0 Step -1
        If Not UCase(.List(i, 0)) Like "*" & UCase(TextBox1) & "*" Then .RemoveItem i
    Next
End With
End Sub
Private Sub Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.BackColor = vbCyan
End Sub


Private Sub TextBox2_Change()
TextBox2.BackColor = vbCyan
With ListBox1
.List = Sheets("Guests").ListObjects(1).DataBodyRange.Value
    For i = .ListCount - 1 To 0 Step -1
        If Not UCase(.List(i, 1)) Like "*" & UCase(TextBox2) & "*" Then .RemoveItem i
    Next
End With
End Sub
Private Sub Textbox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.BackColor = vbCyan
End Sub


Private Sub TextBox3_Change()
TextBox3.BackColor = vbCyan
End Sub
Private Sub Textbox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox3.BackColor = vbCyan
End Sub
Private Sub TextBox4_Change()
TextBox4.BackColor = vbCyan
End Sub
Private Sub Textbox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox4.BackColor = vbCyan
End Sub


Private Sub UserForm_initialize()
With Sheets("Guests").ListObjects(1)
ar = .DataBodyRange
    With ListBox1
        .List = ar
        .ColumnCount = 9
        .ColumnWidths = "150;50;50;60;60;63;40;40;40"
    End With
End With
End Sub
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    80.4 KB · Views: 4
  • Capture2.JPG
    Capture2.JPG
    48.8 KB · Views: 5
  • Capture3.JPG
    Capture3.JPG
    56.8 KB · Views: 5
  • Capture4.JPG
    Capture4.JPG
    61.4 KB · Views: 3
  • Guest Search.xlsm
    56.1 KB · Views: 2
Thank you. You are awesome. It worked. But what happens if I just click on any data from the Listbox below. Could it be possible to do, display the requested information on the Corresponding TextBoxes: TextBox1, TextBox2, TextBox3 and TextBox4?
 
Back
Top