Search a column to match textbox value from a form

[Albert]

Hi Guys,

I have designed a userform so that if a customer has called in before I need
to view certain fields of their previous interaction. I have a textbox
"Customer ID" which I would like to searchh through a worksheet find that ID
# then populate the corresponding previous information.

Any ideas.

Regards
Albert

 

[JLGWhiz]

Would you like to tell us a little bit about your user form? Like, are you
using labels, textboxes, list boxes or comboboxes to display the data that
you call from your worksheet. And how about the layout of the worksheet? Is
it designed so that the individual data elements that you intend to retrieve
can be easily accessed?
I have a program where I retrieve about ten data elements. I have them
arranged in columns so that all elements for a single call are on one row.
That allows me to not only locate them easily, but to also select them
individually for corrections. You can call them up by using the Find method,
in your case it would be by ID#. Then use Offset to access the individual
columns of data. To put the data in the form, if you use labels, Label1 =
ID#.Offset(0, 2). If you have five elements to populate you can use a loop
with a variable to get all five in one search.

For i = 1 To 5
Label & i = ID#.Offset(0, i)
Next

Maybe this will get you started.

 

[JLGWhiz]

Syntax correction.

Label1.Caption = ID#.Offset(0, 2).Value

 

[Bernie Deitrick]

Albert,

Put a commandbutton on your userform, with code like

Private Sub CommandButton1_Click()
Dim myC As Range
Set myC = Worksheets("Sheet1").Range("A:A").Find(UserForm1.TextBox1.Text)
If myC Is Nothing Then
Msgbox UserForm1.TextBox1.Text & " was not found."
Else
Userform1.TextBox2.Text = myC(1,2).Value 'Pick up the cell to the right of the ID#
Userform1.TextBox3.Text = myC(1,3).Value 'Pick up the cell two to the right of the ID#
End If
End Sub

You cannot have a textbox named "Customer ID" since that is an illegal name....

HTH,
Bernie
MS Excel MVP

 

[Albert]

Hi,

I have set my spreadsheet out exactly like that. My form has individual
textboxs for each datafield. Is there any way you can present it in a table
or is this the best way?

I am still not sure how to find and select the ID# in the ID# column, so
that the offset function can work? I know I should use the findmethod but am
not sure what the code is for this as it will have to loop through each cell
in the column until it finds the matching ID# or ends when the last cell is
blank.

Also do you perhaps know how 4 users (using the same spreadsheet format and
form) can write and query a closed master list excel file. This is the
combined 4 users output?

Most appreciatted.
Albert

 

[Albert]

Thanks works perfectly.

Would you know how 4 users, using the same worksheet format and form, can
get their worksheet automatically updated to a closed spreadsheet and also
access data from the master if it is closed?