VBA UserForm - Using TextBox to look up in a range

[gobirds12]

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?

 

[Fluff]

Hi & welcome to MrExcel.
Rather than using a textbox, you could use a ComboBox that has all the box sizes.
Would that be ok?

 

[gobirds12]

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.

 

[Fluff]

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.

 

[gobirds12]

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?

 

[Fluff]

How about

[COLOR=#ff0000]Dim ufDic As Object[/COLOR]

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.

 

[Steve_]

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?

 

[gobirds12]

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.

 

[Fluff]

The big draw back of a textbox is that the user has to type the box size exactly as it is on the sheet.

 

[Fluff]

If you want to stick with a textbox, try

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