Using Form to Search and Provide Next and Previous Buttons

S

slpswhite

New Member
Joined
Jan 2, 2018
Messages
39
I have developed a form to input data in a spreadsheet which works fine. I have added a button which should allow the user to search on a text field, in this case the field name is txtApplicationNumber. When a number is input to the field on the form and the user hits search it should display the data for this request in the form. The data starts on line 9 of the sheet called Master Table. Currently, regardless of the inputs in the field it returns the first line of data. I have been banging my head on this for a few hours and decided to ask for help.

Any quick pointers you might be able to give on using a Next and Previous button would be helpful.

The code I have for this is as follows:

Code: 
Private Sub cmdViewRecord_Click()
Dim totrows As Long, i As Long
Set ws2 = Sheets("Master Table")
totrows = Worksheets("Master Table").Range("B9").CurrentRegion.Rows.Count
If txtApplicationNumber.Text = "" Then
    MsgBox "Search Requires an Entry in Application Number"
    End If
For i = 9 To totrows
txtVendorName = ws2.Cells(i, 1)
txtApplicationNumber = ws2.Cells(i, 2)
txtApplicationName = ws2.Cells(i, 3)
txtIdentifier.Text = ws2.Cells(i, 4)
txtPortfolio.Text = ws2.Cells(i, 5)
txtWorkstream.Text = ws2.Cells(i, 6)
txtApplicationGroup.Text = ws2.Cells(i, 7)
txtSupportGroup.Text = ws2.Cells(i, 12)
txtTier.Text = ws2.Cells(i, 13)
txtOwner.Text = ws2.Cells(i, 19)
txtContractNumber.Text = ws2.Cells(i, 20)
txtContractWorkspaceNumber.Text = ws2.Cells(i, 21)
txtStatusofExecution.Text = ws2.Cells(i, 23)
txtCostCenter.Text = ws2.Cells(i, 24)
txtNov15.Text = ws2.Cells(i, 28)
txtDec15.Text = ws2.Cells(i, 29)
txtJan16.Text = ws2.Cells(i, 30)
txtFeb16.Text = ws2.Cells(i, 31)
txtMar16.Text = ws2.Cells(i, 32)
txtApr16.Text = ws2.Cells(i, 33)
txtMay16.Text = ws2.Cells(i, 34)
txtJun16.Text = ws2.Cells(i, 35)
txtJul16.Text = ws2.Cells(i, 36)
txtAug16.Text = ws2.Cells(i, 37)
txtSep16.Text = ws2.Cells(i, 38)
txtOct16.Text = ws2.Cells(i, 39)
txtNov16.Text = ws2.Cells(i, 40)
txtDec16.Text = ws2.Cells(i, 41)
txtJan17.Text = ws2.Cells(i, 42)
txtFeb17.Text = ws2.Cells(i, 43)
txtMar17.Text = ws2.Cells(i, 44)
txtApr17.Text = ws2.Cells(i, 45)
txtMay17.Text = ws2.Cells(i, 46)
txtJun17.Text = ws2.Cells(i, 47)
txtJul17.Text = ws2.Cells(i, 48)
txtAug17.Text = ws2.Cells(i, 49)
txtSep17.Text = ws2.Cells(i, 50)
txtOct17.Text = ws2.Cells(i, 51)
txtNov17.Text = ws2.Cells(i, 52)
txtDec17.Text = ws2.Cells(i, 53)
txtJan18.Text = ws2.Cells(i, 54)
txtFeb18.Text = ws2.Cells(i, 55)
txtMar18.Text = ws2.Cells(i, 56)
txtApr18.Text = ws2.Cells(i, 57)
txtMay18.Text = ws2.Cells(i, 58)
txtJun18.Text = ws2.Cells(i, 59)
txtJul18.Text = ws2.Cells(i, 60)
txtAug18.Text = ws2.Cells(i, 61)
txtSep18.Text = ws2.Cells(i, 62)
txtOct18.Text = ws2.Cells(i, 63)
txtNov18.Text = ws2.Cells(i, 64)
txtDec18.Text = ws2.Cells(i, 65)
txtJan19.Text = ws2.Cells(i, 66)
txtFeb19.Text = ws2.Cells(i, 67)
txtMar19.Text = ws2.Cells(i, 68)
txtApr19.Text = ws2.Cells(i, 69)
txtMay19.Text = ws2.Cells(i, 70)
txtJun19.Text = ws2.Cells(i, 71)
txtJul19.Text = ws2.Cells(i, 72)
txtAug19.Text = ws2.Cells(i, 73)
txtSep19.Text = ws2.Cells(i, 74)
txtOct19.Text = ws2.Cells(i, 75)
txtNov19.Text = ws2.Cells(i, 76)
txtDec19.Text = ws2.Cells(i, 77)
txtJan20.Text = ws2.Cells(i, 78)
txtFeb20.Text = ws2.Cells(i, 79)
txtMar20.Text = ws2.Cells(i, 80)
txtApr20.Text = ws2.Cells(i, 81)
txtMay20.Text = ws2.Cells(i, 82)
txtJun20.Text = ws2.Cells(i, 83)
txtJul20.Text = ws2.Cells(i, 84)
txtAug20.Text = ws2.Cells(i, 85)
txtSep20.Text = ws2.Cells(i, 86)
txtOct20.Text = ws2.Cells(i, 87)
txtNov20.Text = ws2.Cells(i, 88)
txtNov20.Text = ws2.Cells(i, 88)
txtContractTotal.Text = ws2.Cells(i, 89)
txtYear1.Text = ws2.Cells(i, 92)
txtYear2.Text = ws2.Cells(i, 93)
txtYear3.Text = ws2.Cells(i, 94)
txtYear4.Text = ws2.Cells(i, 95)
txtYear5.Text = ws2.Cells(i, 96)
Exit For
Next i
End Sub

Thanks in advance!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
instead of having the for loop try something like this

Code: 
Private Sub cmdViewRecord_Click()
Dim totrows As Long, i As Long
Set ws2 = Sheets("Master Table")
totrows = Worksheets("Master Table").Range("B9").CurrentRegion.Rows.Count
If txtApplicationNumber.Text = "" Then
    MsgBox "Search Requires an Entry in Application Number"
ElseIf txtApplicationNumber.Value < 0 Then
    MsgBox "A positive number must be entered"
Else
[COLOR=#ff0000]i = txtApplicationNumber.Value[/COLOR]
txtVendorName = ws2.Cells(i, 1)
txtApplicationNumber = ws2.Cells(i, 2)
txtApplicationName = ws2.Cells(i, 3)
txtIdentifier.Text = ws2.Cells(i, 4)
txtPortfolio.Text = ws2.Cells(i, 5)
txtWorkstream.Text = ws2.Cells(i, 6)
txtApplicationGroup.Text = ws2.Cells(i, 7)
txtSupportGroup.Text = ws2.Cells(i, 12)
txtTier.Text = ws2.Cells(i, 13)
txtOwner.Text = ws2.Cells(i, 19)
txtContractNumber.Text = ws2.Cells(i, 20)
txtContractWorkspaceNumber.Text = ws2.Cells(i, 21)
txtStatusofExecution.Text = ws2.Cells(i, 23)
txtCostCenter.Text = ws2.Cells(i, 24)
txtNov15.Text = ws2.Cells(i, 28)
txtDec15.Text = ws2.Cells(i, 29)
txtJan16.Text = ws2.Cells(i, 30)
txtFeb16.Text = ws2.Cells(i, 31)
txtMar16.Text = ws2.Cells(i, 32)
txtApr16.Text = ws2.Cells(i, 33)
txtMay16.Text = ws2.Cells(i, 34)
txtJun16.Text = ws2.Cells(i, 35)
txtJul16.Text = ws2.Cells(i, 36)
txtAug16.Text = ws2.Cells(i, 37)
txtSep16.Text = ws2.Cells(i, 38)
txtOct16.Text = ws2.Cells(i, 39)
txtNov16.Text = ws2.Cells(i, 40)
txtDec16.Text = ws2.Cells(i, 41)
txtJan17.Text = ws2.Cells(i, 42)
txtFeb17.Text = ws2.Cells(i, 43)
txtMar17.Text = ws2.Cells(i, 44)
txtApr17.Text = ws2.Cells(i, 45)
txtMay17.Text = ws2.Cells(i, 46)
txtJun17.Text = ws2.Cells(i, 47)
txtJul17.Text = ws2.Cells(i, 48)
txtAug17.Text = ws2.Cells(i, 49)
txtSep17.Text = ws2.Cells(i, 50)
txtOct17.Text = ws2.Cells(i, 51)
txtNov17.Text = ws2.Cells(i, 52)
txtDec17.Text = ws2.Cells(i, 53)
txtJan18.Text = ws2.Cells(i, 54)
txtFeb18.Text = ws2.Cells(i, 55)
txtMar18.Text = ws2.Cells(i, 56)
txtApr18.Text = ws2.Cells(i, 57)
txtMay18.Text = ws2.Cells(i, 58)
txtJun18.Text = ws2.Cells(i, 59)
txtJul18.Text = ws2.Cells(i, 60)
txtAug18.Text = ws2.Cells(i, 61)
txtSep18.Text = ws2.Cells(i, 62)
txtOct18.Text = ws2.Cells(i, 63)
txtNov18.Text = ws2.Cells(i, 64)
txtDec18.Text = ws2.Cells(i, 65)
txtJan19.Text = ws2.Cells(i, 66)
txtFeb19.Text = ws2.Cells(i, 67)
txtMar19.Text = ws2.Cells(i, 68)
txtApr19.Text = ws2.Cells(i, 69)
txtMay19.Text = ws2.Cells(i, 70)
txtJun19.Text = ws2.Cells(i, 71)
txtJul19.Text = ws2.Cells(i, 72)
txtAug19.Text = ws2.Cells(i, 73)
txtSep19.Text = ws2.Cells(i, 74)
txtOct19.Text = ws2.Cells(i, 75)
txtNov19.Text = ws2.Cells(i, 76)
txtDec19.Text = ws2.Cells(i, 77)
txtJan20.Text = ws2.Cells(i, 78)
txtFeb20.Text = ws2.Cells(i, 79)
txtMar20.Text = ws2.Cells(i, 80)
txtApr20.Text = ws2.Cells(i, 81)
txtMay20.Text = ws2.Cells(i, 82)
txtJun20.Text = ws2.Cells(i, 83)
txtJul20.Text = ws2.Cells(i, 84)
txtAug20.Text = ws2.Cells(i, 85)
txtSep20.Text = ws2.Cells(i, 86)
txtOct20.Text = ws2.Cells(i, 87)
txtNov20.Text = ws2.Cells(i, 88)
txtNov20.Text = ws2.Cells(i, 88)
txtContractTotal.Text = ws2.Cells(i, 89)
txtYear1.Text = ws2.Cells(i, 92)
txtYear2.Text = ws2.Cells(i, 93)
txtYear3.Text = ws2.Cells(i, 94)
txtYear4.Text = ws2.Cells(i, 95)
txtYear5.Text = ws2.Cells(i, 96)
End if

End Sub

if the user does not know to only enter values above 8 then change the red line above to this

i = txtApplicationNumber.Value + 8
 
Last edited:
Upvote 0
Hey Nine thanks for the response. I tried this and ended up with a Run-time error '13': Type mismatch. The error is in the line i=txtapplicationNumber.value

The spreadsheet has just under a thousand entries and the intent is the user will fill in on the form the Application Number (txtApplicationNumber) and when they hit search it will find the first entry of the application number in column B (the first entry is in B9). I will create another button which will go to the next record or return a message box which will say something like This is the last Record in the list. I will also be creating a Previous so they can basically search up the list.

The Application Number is Alpha Numeric by the way.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
How to code for search, previous, next, update, delete, print command buttons in user form in excel vba
Replies
0
Views
6K
Vivek Roshan
V
C
  • Question Question
Search for reference number on seperate workbook and then transfer data to relevent row
Replies
1
Views
615
Clueless and Lost
C

Forum statistics

Threads
1,223,767
Messages
6,174,390
Members
452,561
Latest member
amir5104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top