Rachel,

not fully tested but give this a try. it may need some correction to suit
how your data is laid out in worksheet but hopefully, it will do what you are
seeking.

Private Sub cmdSearch_Click()
Dim Foundcell As Range
Dim Search As String
Dim cn As Integer
Dim ws1 As Worksheet

'set search criteria based
'on which textbox has data entered
If txtCustomerID.Text < "" Then

Search = txtCustomerID.Text
cn = 1

ElseIf txtName.Text < "" Then

Search = txtName.Text
cn = 2

ElseIf txtStreet.Text < "" Then

Search = txtStreet.Text
cn = 3

Else

Exit Sub

End If

'worksheet name where data stored
'change as required
Set ws1 = ThisWorkbook.Worksheets("myworksheetname")



Set Foundcell = ws1.Columns(cn).Find(Search, _
After:=ws1.Cells(1, cn), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Foundcell Is Nothing = False Then


With UserForm1

.txtCustomerID.Text = _
Foundcell.Offset(0, -(cn - 1)).Value
.txtName.Text = _
Foundcell.Offset(0, 1 - (cn - 1)).Value
.txtStreet.Text = _
Foundcell.Offset(0, 2 - (cn - 1)).Value

.txtSuburb.Text = _
Foundcell.Offset(0, 3 - (cn - 1)).Value
.txtPostcode.Text = _
Foundcell.Offset(0, 4 - (cn - 1)).Value
.txtPhone.Text = _
Foundcell.Offset(0, 5 - (cn - 1)).Value

End With

Else

msg = MsgBox(Search & Chr(10) & _
"No Current Record", _
16, _
"Search")

End If


End Sub

--
jb


"Rachel" wrote:

Hi Hi,
I need a code for a user form command button (cmdSearch) based on entries
input into any of 3 text boxes (txtCustomerID/txtName/txtStreet) to search
the 3 corresponding worksheet columns (CustomerID/Name/Street). If any of the
3 entries is found then populate all text boxes
(CustomerID/Name/Street/Suburb/Postcode/Phone) based on adjacent data in row.
If data not found then msgbox "No current record" which then allows user to
input new data manually in text boxes and then cmdAdd creates a new row on
worksheet.
I have the cmdAdd bit sorted but just don't want to have to repeatedly input
data if already existing.
NB: entries may be on more than one row but adjacent info will be the same.
Hope this makes sense!
Thanks in advance!
Rachel