Userform with a textbox that looks up a value

billyshears

Board Regular
Joined
Aug 29, 2013
Messages
61
I have a userform with a textbox that I want to reference data from a range in a work sheet. I have this formula but I cant seem to get it to work. Where would I paste this code?

Rich (BB code):
Option Explicit Public Sub LoadDataExample()   
 'Declare our Result range reference    
Dim Result As Range: Set Result = Range("A:A").Find(1, LookIn:=xlValues, Lookat:=xlWhole) 'Search for the value 1 in Column A    
If Not Result Is Nothing Then 'If our Result reference is Not Nothing (It's something) then do stuff        
'We can load the values into our userform by using the range reference (It's a single cell containing the search term)        
TextBox1.Text = Result.Value 'This is the cell that contains our search term (1)        
TextBox2.Text = Result.Offset(0, 1).Value 'We offset the value being returned by moving 0 rows up/down and moving 1 column to the right        
TextBox3.Text = Result.Offset(0, 2).Value 'We offset the value being returned by moving 0 rows up/down and moving 2 columns to the right    

End If End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi
place the code in your Userforms code page

Rich (BB code):
Option Explicit
Public Sub LoadDataExample()
   
    'Declare variables
    'our Result range reference
    Dim Result      As Range
    'search value
    Dim Search      As String
   
    'Search for the value entered in Textbox
    Search = TextBox1.Text
   
    'search worksheet column A
    Set Result = Worksheets("Sheet1").Range("A:A").Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
    'If our Result reference is Not Nothing (It's something) then do stuff
    If Not Result Is Nothing Then
        'We can load the values into our userform by using the range reference
        '(It's a single cell containing the search term)
       
        'This is the cell that contains our search term
        TextBox1.Text = Result.Value
        'We offset the value being returned by moving 0 rows up/down and moving 1 column to the right
        TextBox2.Text = Result.Offset(0, 1).Value
        'We offset the value being returned by moving 0 rows up/down and moving 2 columns to the right
        TextBox3.Text = Result.Offset(0, 2).Value
       
    Else
        'imform user
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
   
End Sub

Change the names I have shown in BOLD as required

you can call the code from a commandbutton on your userform

VBA Code:
Private Sub CommandButton1_Click()
    LoadDataExample
End Sub

Note: Option Explicit declaration must sit at the very TOP of your forms code page OUTSIDE any procedure

Dave
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top