VBA UserForm - Using TextBox to look up in a range

gobirds12

New Member
Joined
Jul 10, 2019
Messages
4
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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 10, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 10, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
How about
Code:
[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_

Board Regular
Joined
Apr 28, 2010
Messages
167
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:

gobirds12

New Member
Joined
Jul 10, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,218
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top