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