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

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

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

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

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

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

C .G. Messinger

"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