Filter question?

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP


"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP


"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Is there a reason you changed .offset(0,20) to .offset(1,20)?

Did you really mean to go to the next row down?

And did you change the .triplestate property from False to True? If you want
checked and unchecked, that property should be False.

And that code only looks at one checkbox and one cell. Did you assign a common
linked cell to that checkbox?

AOP wrote:

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP

"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,
Yes I changed the offset to 1, because there is a heading row and after
several different ways that was the only code that would work.
The triplestate was not changed, I checked and is at "False". And there is
no link set up from the checkbox to a cell.
--
AOP


"Dave Peterson" wrote:

Is there a reason you changed .offset(0,20) to .offset(1,20)?

Did you really mean to go to the next row down?

And did you change the .triplestate property from False to True? If you want
checked and unchecked, that property should be False.

And that code only looks at one checkbox and one cell. Did you assign a common
linked cell to that checkbox?

AOP wrote:

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP

"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Sorry Dave,
I was looking at a different code. The code you posted me remains the same ie
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
and so on for all the textboxes.
The following you posted me for the checkboxes ie:
me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")
when I run it I get an error message with "lcase" high lighted.
--
AOP


"AOP" wrote:

Hi Dave,
Yes I changed the offset to 1, because there is a heading row and after
several different ways that was the only code that would work.
The triplestate was not changed, I checked and is at "False". And there is
no link set up from the checkbox to a cell.
--
AOP


"Dave Peterson" wrote:

Is there a reason you changed .offset(0,20) to .offset(1,20)?

Did you really mean to go to the next row down?

And did you change the .triplestate property from False to True? If you want
checked and unchecked, that property should be False.

And that code only looks at one checkbox and one cell. Did you assign a common
linked cell to that checkbox?

AOP wrote:

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP

"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

This is the code as it is:

Private Sub CommandButton2_Click()

Dim FoundCell As Range

If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With


If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 15).Value
Me.TextBox17.Value = FoundCell.Offset(0, 16).Value
Me.TextBox18.Value = FoundCell.Offset(0, 17).Value
Me.TextBox19.Value = FoundCell.Offset(0, 18).Value
Me.TextBox20.Value = FoundCell.Offset(0, 19).Value
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value
Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value
Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value
Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value
Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value
Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value
Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value
Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value
Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value
Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value
Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value
Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value
Me.TextBox21.Value = FoundCell.Offset(0, 32).Value
Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value
Me.CheckBox14.Value = FoundCell.Offset(0, 34).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value

'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
End If

End Sub
--
AOP


"AOP" wrote:

Sorry Dave,
I was looking at a different code. The code you posted me remains the same ie
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
and so on for all the textboxes.
The following you posted me for the checkboxes ie:
me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")
when I run it I get an error message with "lcase" high lighted.
--
AOP


"AOP" wrote:

Hi Dave,
Yes I changed the offset to 1, because there is a heading row and after
several different ways that was the only code that would work.
The triplestate was not changed, I checked and is at "False". And there is
no link set up from the checkbox to a cell.
--
AOP


"Dave Peterson" wrote:

Is there a reason you changed .offset(0,20) to .offset(1,20)?

Did you really mean to go to the next row down?

And did you change the .triplestate property from False to True? If you want
checked and unchecked, that property should be False.

And that code only looks at one checkbox and one cell. Did you assign a common
linked cell to that checkbox?

AOP wrote:

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP

"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson

Maybe these kinds of lines:
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
have to be
Me.TextBox13.Value = cbool(lcase(FoundCell.Offset(0, 12).Value) = "yes")

If you're still getting an error with lCase, then look at:
Tools|References
Look for MISSING

And uncheck it.

A missing reference can cause errors like this.


AOP wrote:

This is the code as it is:

Private Sub CommandButton2_Click()

Dim FoundCell As Range

If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 15).Value
Me.TextBox17.Value = FoundCell.Offset(0, 16).Value
Me.TextBox18.Value = FoundCell.Offset(0, 17).Value
Me.TextBox19.Value = FoundCell.Offset(0, 18).Value
Me.TextBox20.Value = FoundCell.Offset(0, 19).Value
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value
Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value
Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value
Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value
Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value
Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value
Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value
Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value
Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value
Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value
Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value
Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value
Me.TextBox21.Value = FoundCell.Offset(0, 32).Value
Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value
Me.CheckBox14.Value = FoundCell.Offset(0, 34).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value

'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
End If

End Sub
--
AOP

"AOP" wrote:

Sorry Dave,
I was looking at a different code. The code you posted me remains the same ie
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
and so on for all the textboxes.
The following you posted me for the checkboxes ie:
me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")
when I run it I get an error message with "lcase" high lighted.
--
AOP


"AOP" wrote:

Hi Dave,
Yes I changed the offset to 1, because there is a heading row and after
several different ways that was the only code that would work.
The triplestate was not changed, I checked and is at "False". And there is
no link set up from the checkbox to a cell.
--
AOP


"Dave Peterson" wrote:

Is there a reason you changed .offset(0,20) to .offset(1,20)?

Did you really mean to go to the next row down?

And did you change the .triplestate property from False to True? If you want
checked and unchecked, that property should be False.

And that code only looks at one checkbox and one cell. Did you assign a common
linked cell to that checkbox?

AOP wrote:

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP

"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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


--

Dave Peterson


--

Dave Peterson



--

Dave Peterson

Dave,
Thank you for your patience. It all works great.
We finally got there
thank you

--
AOP


"Dave Peterson" wrote:

Maybe these kinds of lines:
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
have to be
Me.TextBox13.Value = cbool(lcase(FoundCell.Offset(0, 12).Value) = "yes")

If you're still getting an error with lCase, then look at:
Tools|References
Look for MISSING

And uncheck it.

A missing reference can cause errors like this.


AOP wrote:

This is the code as it is:

Private Sub CommandButton2_Click()

Dim FoundCell As Range

If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 15).Value
Me.TextBox17.Value = FoundCell.Offset(0, 16).Value
Me.TextBox18.Value = FoundCell.Offset(0, 17).Value
Me.TextBox19.Value = FoundCell.Offset(0, 18).Value
Me.TextBox20.Value = FoundCell.Offset(0, 19).Value
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value
Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value
Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value
Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value
Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value
Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value
Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value
Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value
Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value
Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value
Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value
Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value
Me.TextBox21.Value = FoundCell.Offset(0, 32).Value
Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value
Me.CheckBox14.Value = FoundCell.Offset(0, 34).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value

'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
End If

End Sub
--
AOP

"AOP" wrote:

Sorry Dave,
I was looking at a different code. The code you posted me remains the same ie
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
and so on for all the textboxes.
The following you posted me for the checkboxes ie:
me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")
when I run it I get an error message with "lcase" high lighted.
--
AOP


"AOP" wrote:

Hi Dave,
Yes I changed the offset to 1, because there is a heading row and after
several different ways that was the only code that would work.
The triplestate was not changed, I checked and is at "False". And there is
no link set up from the checkbox to a cell.
--
AOP


"Dave Peterson" wrote:

Is there a reason you changed .offset(0,20) to .offset(1,20)?

Did you really mean to go to the next row down?

And did you change the .triplestate property from False to True? If you want
checked and unchecked, that property should be False.

And that code only looks at one checkbox and one cell. Did you assign a common
linked cell to that checkbox?

AOP wrote:

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP

"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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:

That's good.

I was getting more and more confused vbg.

AOP wrote:

Dave,
Thank you for your patience. It all works great.
We finally got there
thank you

--
AOP

"Dave Peterson" wrote:

Maybe these kinds of lines:
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
have to be
Me.TextBox13.Value = cbool(lcase(FoundCell.Offset(0, 12).Value) = "yes")

If you're still getting an error with lCase, then look at:
Tools|References
Look for MISSING

And uncheck it.

A missing reference can cause errors like this.


AOP wrote:

This is the code as it is:

Private Sub CommandButton2_Click()

Dim FoundCell As Range

If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 15).Value
Me.TextBox17.Value = FoundCell.Offset(0, 16).Value
Me.TextBox18.Value = FoundCell.Offset(0, 17).Value
Me.TextBox19.Value = FoundCell.Offset(0, 18).Value
Me.TextBox20.Value = FoundCell.Offset(0, 19).Value
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value
Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value
Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value
Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value
Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value
Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value
Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value
Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value
Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value
Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value
Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value
Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value
Me.TextBox21.Value = FoundCell.Offset(0, 32).Value
Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value
Me.CheckBox14.Value = FoundCell.Offset(0, 34).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value

'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
End If

End Sub
--
AOP

"AOP" wrote:

Sorry Dave,
I was looking at a different code. The code you posted me remains the same ie
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
and so on for all the textboxes.
The following you posted me for the checkboxes ie:
me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")
when I run it I get an error message with "lcase" high lighted.
--
AOP


"AOP" wrote:

Hi Dave,
Yes I changed the offset to 1, because there is a heading row and after
several different ways that was the only code that would work.
The triplestate was not changed, I checked and is at "False". And there is
no link set up from the checkbox to a cell.
--
AOP


"Dave Peterson" wrote:

Is there a reason you changed .offset(0,20) to .offset(1,20)?

Did you really mean to go to the next row down?

And did you change the .triplestate property from False to True? If you want
checked and unchecked, that property should be False.

And that code only looks at one checkbox and one cell. Did you assign a common
linked cell to that checkbox?

AOP wrote:

Dave,
I used the following code
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes")
and nothing happens. All I get is the ticks on all the checkboxes in a light
grey.
Is there something I need to do in the properties maybe?
--
AOP

"Dave Peterson" wrote:

The checkbox value will be true/false.

So you could use:

if lcase(foundcell.offset(0,20).value) = "yes" then
me.checkbox1.value = true
else
me.checkbox1.value = false
end if

or

me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes")



AOP wrote:

Thanks very much Dave,
Its working fine now...geat stuff!.
But, only one more thing. On my userform I have a checkbox, when selected it
saves it on the data sheet as text "Yes" with the following code:
LastRow.Offset(1, 20).Value = CheckBox1.Caption

In your code how do I recall the checkbox as a tick?
I have written it as:
Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value

--
AOP

"Dave Peterson" wrote:

Typo on my part:

With Worksheets("Customers").range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End with



AOP wrote:

Dave,
I followed you instructions, all fine until it runs the following happens:
I get run 13 error. with the following lines highlighted.
With Worksheets("Customers")
Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

any ideas
--
AOP

"Dave Peterson" wrote:

All the names are unique in column A?

I'd put a combobox on the userform that displays those unique values from Column
A.

Then you could search for that username and when you find it, you could fill out
the 35 textboxes. I'd put a button that retrieved the values after the user
chose the customer name from the combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim FoundCell as Range

if me.combobox1.listindex = -1 then
'nothing filled in
beep
exit sub
end if

With worksheets("Customer")
set Foundcell = .range("a:a").cells.find(what:=me.combobox1.value, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'this shouldn't happen!
beep
else
me.textbox1.value = foundcell.offset(0,1).value
'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
end if

End sub

Uncompiled, untested. Watch for typos!

AOP wrote:

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:

--

Dave Peterson