VBA UserForm - Using TextBox to look up in a range
Results 1 to 10 of 10

Thread: VBA UserForm - Using TextBox to look up in a range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA UserForm - Using TextBox to look up in a range

    I have a userform that when it runs comes up with a blank text box. I want the user to be able to type in the value that they want to lookup, in this case it is a box size, and once they press enter I need it to put all the matching information on the same sheet that I have my macro button that opens the userform.

    In a separate sheet, my lookup sheet, there is an extensive list of box sizes in the first column and then there are 3 more columns. Each box size appears more than once so in the return sheet I would need it to list out each matching data row.

    The issue that I am having is that I can't seem to find a way to match the text box value to the range of values that I have - I defined the row of boxes as a named range "Boxes".

    The code line I tried to use to match it when the enter button is pressed was:

    If TextBox.Value = Worksheets("Lookup Draft").Range("Boxes").Find(TextBox) Then
    ... (code with a loop) ...

    Else
    MsgBox("No Box Size Matches")
    End If

    Currently, for every box size that I put in the text box I only get the message box even though I know it has at least one matching value.

    How do I match the two?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    Hi & welcome to MrExcel.
    Rather than using a textbox, you could use a ComboBox that has all the box sizes.
    Would that be ok?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    Hi I didn't think of that but maybe that could work. The only issue I could think of with a combo box is that there are over 3000 unique sizes so it might be too much for the combo box.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    You can have 3000 values in a combobox, although it might be a pain trying to find the one you want, unless they are in some sort of order.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    Okay, it's definitely worth a try. I've never used a combo box before so what line of vba would I need to match that value to each row that contains it within my dataset?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    How about
    Code:
    Dim ufDic As Object
    
    Private Sub CommandButton1_Click()
       ActiveSheet.UsedRange.Offset(1).ClearContents
       ufDic(Me.ComboBox1.Value).Copy Range("a2")
    End Sub
    
    Private Sub UserForm_Initialize()
       Dim Cl As Range
       
       Set ufDic = CreateObject("scripting.dictionary")
       For Each Cl In Range("Boxes")
          If Not ufDic.Exists(Cl.Value) Then
             ufDic.Add Cl.Value, Cl.Resize(, 3)
          Else
             Set ufDic(Cl.Value) = Union(ufDic(Cl.Value), Cl.Resize(, 3))
          End If
       Next Cl
       Me.ComboBox1.List = ufDic.keys
    End Sub
    The line in red must go at the very top of the module, before any code.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    I think your approach may be off here. I feel like once the user provides the input for the text box, you would likely be best using a loop to scan the sheet with all the box sizes, then add each matching line item to an array. Then once the loop finishes, the array contents could be dumped to the sheet for display.

    Does this sound more in line with what you are looking for?
    Last edited by Steve_; Jul 10th, 2019 at 10:01 AM.

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    Hi Steve, yes that does sound more like what I was looking for and I have tried various loops with for each but I still can't quite seem to get the contents to appear into various rows.

    Fluff, thank you! I will try out this code and see if it runs the way I would like it to but I am still not 100% sure on the use of a combobox.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    The big draw back of a textbox is that the user has to type the box size exactly as it is on the sheet.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA UserForm - Using TextBox to look up in a range

    If you want to stick with a textbox, try
    Code:
    Dim ufDic As Object
    
    Private Sub CommandButton1_Click()
       ActiveSheet.UsedRange.Offset(1).ClearContents
       If ufDic.Exists(Me.TextBox1.Value) Then
          ufDic(Me.TextBox1.Value).Copy Range("a2")
       Else
          MsgBox "Box size " & Me.TextBox1.Value & " doesn't exist"
          Me.TextBox1.Value = ""
          Me.TextBox1.SetFocus
       End If
    End Sub
    
    Private Sub UserForm_Initialize()
       Dim Cl As Range
       
       Set ufDic = CreateObject("scripting.dictionary")
       For Each Cl In Range("Boxes")
          If Not ufDic.Exists(Cl.Value) Then
             ufDic.Add Cl.Value, Cl.Resize(, 3)
          Else
             Set ufDic(Cl.Value) = Union(ufDic(Cl.Value), Cl.Resize(, 3))
          End If
       Next Cl
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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