Ok, I'll try and explain.
The workbook has been altered and now has two sheets ("Customer" and
"Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one
button that opens the userform.
I enter the details in the userform (2) and the data gets stored in sheet
("Customer") columns A to AJ rows 1 to 5000.
What I'm trying to do is to be able search by name (column A) and call up
individual customer details and have it displayed on the userform, if that is
possible.
--
AOP


"Dave Peterson" wrote:

I don't understand what you're doing.

Either you'll have to provide more information or maybe someone else will chime
in.

AOP wrote:

Sorry Dave,
I cant get my head round this. could you walk me through this.......The
sheet that has the data is sheet(6) "Customers" columns A to AJ.
Sheet(2) is the menu sheet with one button, when pressed userform(2) is
shown and the data entered in that gets stored in sheet 6. That works well.
I'm assuming that I need to put an additional button on sheet (2) to run the
code? or I'm well off the mark!
--
AOP

"Dave Peterson" wrote:

If it's a followup to this question, just post in the same thread.

If it's a different question, start a new thread.

You'll find that there are lots of people who read the newsgroups who can help.

AOP wrote:

Thanks Dave,
Yes I'm displaying each of the fields in a separate textbox and checkbox too.
I'm trying to use the same form, which I use to enter data, to show the
clients details. It is the mother of all forms but I need all the information.
I shall try your code and see how I get on!
If there is a problem can I get back to you?
--
AOP

"Dave Peterson" wrote:

How are you displaying those 32 columns?

If it's part of a giant listbox or combobox, then if you were only using up to
10 columns, you could loop through the important column and add each item that
matches your requirement -- and include the cells to the right.

But since you're using columns A:AF (32 columns), you can filter your range to
show what you want.

Then copy the visible rows to a new location (on a new worksheet--maybe
hidden???). Then use the range on that worksheet as the rowsource.

If you're displaying each of those fields in a separate textbox (or label or
....), you could loop through the visible cells in column A and do something
like:

Dim HowManyVisRows as long
dim VisRng as range
dim myCell as range
dim iCtr as long

'apply the filter someway

With worksheets("somesheetname").autofilter.range
'subtract one for the header.
howmanyvisrows _
= .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1

if howmanyvisrows 0 then
'avoid the header and come down one row
set visrng = .resize(.rows.count-1, 1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)
else
set visrng = nothing
end if
end with

if visrng is nothing then
'warning message???
else
for each mycell in visrng.cells
me.textbox1.value = mycell.value
me.textbox2.value = mycell.offset(0,1).value
'etc
'or if the textboxes are named nicely.
for ictr = 1 to 32
me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value
next ictr
end if

Untested, uncompiled--watch for typos.


AOP wrote:

At the moment I have a data sheet that details are entered using a userform.
It finds the last empty row and enters the data from columns "A" to "AF"
Is there a way in which I can filter by name (column "A") and the result be
shown on the same userform in which data was entered. Or do I have to use a
different userform.
--
AOP

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson