|
search button to populate cells from sheet
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 |
search button to populate cells from sheet
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 |
search button to populate cells from sheet
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 "john" wrote: 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 |
search button to populate cells from sheet
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/sh...d.php?t=154104 Microsoft Office Help |
search button to populate cells from sheet
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" wrote: 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/sh...d.php?t=154104 Microsoft Office Help . |
search button to populate cells from sheet
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" wrote: 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" wrote: 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/sh...d.php?t=154104 Microsoft Office Help . |
| All times are GMT +1. The time now is 10:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com