search button to populate cells from sheet

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

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
 
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
 
Hi John, thank you for your reply. I have copied your code into my user form
and I have made a couple of updates. It is only coming up with Record not
found even though I am deliberately searching for a record that I know is on
the worksheet.
Is the cn = 1 in:
'Search = txtCustomerID.Text
cn = 1'
defing the column to search? I have changed this to cn = 3, cn = 4, cn = 5
as the CustomerID, Name, and Street are columns D,E and F. Is this right?

I'm not sure about the rest of the code but it is still not working...
Thanks again for your help :)

Rachel
 
Thanks Joel - der, that was pretty obvious!
So my text boxes are filling with the data from the row (YAY!) but is is
starting back at column 1. I need the text boxes to correspond to the correct
column.
If the search is based on txtName (column 5) then txtcustomerID is filled
from column 4, txtStreet is from column 6 etc.
I think it is to do with this part of the code:
Me.txtCustomer_ID.Text = Foundcell.Offset(0, -(cn - 1)).Value
Me.txtName.Text = Foundcell.Offset(0, 1 - (cn - 1)).Value
Me.txtStreet.Text = Foundcell.Offset(0, 2 - (cn - 1)).Value
Me.txtSuburb.Text = Foundcell.Offset(0, 3 - (cn - 1)).Value
Me.txtPostcode.Text = Foundcell.Offset(0, 4 - (cn - 1)).Value
Me.txtPhone.Text = Foundcell.Offset(0, 5 - (cn - 1)).Value
Would the zero after 'offset(' need to change?
Is this even possible as the search may be based on one of 3 textbox
contents....
Thanks again :)
 
Rachel,
you are correct cn = 1 is used to search that Column & needs to be changed
as required.
You are also correct that the Foundcell.Offset part of the code will also
need updating to reflect the changes to cn values you make.

I have corrected code based on info you have supplied & hopefully will work
ok for you.

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 = 4 'Col D

ElseIf txtName.Text <> "" Then

Search = txtName.Text
cn = 5 'Col E

ElseIf txtStreet.Text <> "" Then

Search = txtStreet.Text
cn = 6 'Col F

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 - 4)).Value
.txtName.Text = _
Foundcell.Offset(0, 1 - (cn - 4)).Value
.txtStreet.Text = _
Foundcell.Offset(0, 2 - (cn - 4)).Value

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

End With

Else

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

End If


End Sub
--
jb


rach_303 said:
Thanks Joel - der, that was pretty obvious!
So my text boxes are filling with the data from the row (YAY!) but is is
starting back at column 1. I need the text boxes to correspond to the correct
column.
If the search is based on txtName (column 5) then txtcustomerID is filled
from column 4, txtStreet is from column 6 etc.
I think it is to do with this part of the code:
Me.txtCustomer_ID.Text = Foundcell.Offset(0, -(cn - 1)).Value
Me.txtName.Text = Foundcell.Offset(0, 1 - (cn - 1)).Value
Me.txtStreet.Text = Foundcell.Offset(0, 2 - (cn - 1)).Value
Me.txtSuburb.Text = Foundcell.Offset(0, 3 - (cn - 1)).Value
Me.txtPostcode.Text = Foundcell.Offset(0, 4 - (cn - 1)).Value
Me.txtPhone.Text = Foundcell.Offset(0, 5 - (cn - 1)).Value
Would the zero after 'offset(' need to change?
Is this even possible as the search may be based on one of 3 textbox
contents....
Thanks again :)

joel said:
Wong!
Column A = 1
Column B = 2
Column C = 3
Column D = 4
Column E = 5

defing the column to search? I have changed this to cn = 3, cn = 4, cn
= 5
as the CustomerID, Name, and Street are columns D,E and F. Is this
right?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=154104

Microsoft Office Help

.
Click to expand...
Click to expand...
 
Back
Top