Hello,
I'm working in a vehicle service centre and I am attempting to create a spreadsheet to track the work through the workshop.
I have created a sheet with a working search function but would like to seperate the two so that the service advisors can only search and not change the data in the sheet.
Basically, I want the service advisors to be able to open an excel file with only 1 tab - the search tab and the workshop controller to open the other excel file to input the data.
Currently tab 1 is the search and tabs 2,4-15 are where the data will be held.
When I type a 'WIP number' into the first textbox on my userform and click search, the other 8 text boxes are populated with the data corresponding to that WIP number.
the code is as follows (if someone could also tell me how to search 'allsheets' instead of each sheet individually that would be appreciated)
Private Sub SEARCH_Click()
Dim WIP_Number As String
WIP_Number = Trim(WIPTEXT.Text)
lastrow = Worksheets("JAN").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("JAN").Cells(i, 2).Value = WIP_Number Then
WIPTEXT.Text = Worksheets("JAN").Cells(i, 2).Value
CARTEXT.Text = Worksheets("JAN").Cells(i, 3).Value
ADVISORTEXT.Text = Worksheets("JAN").Cells(i, 11).Value
REQUIREDTEXT.Text = Worksheets("JAN").Cells(i, 6).Text
COMPLETETEXT.Text = Worksheets("JAN").Cells(i, 10).Text
STATUSTEXT.Text = Worksheets("JAN").Cells(i, 14).Value
AUTHORITYTEXT.Text = Worksheets("JAN").Cells(i, 12).Text
QCTEXT.Text = Worksheets("JAN").Cells(i, 13).Text
TEAMTEXT.Text = Worksheets("JAN").Cells(i, 9).Text
End If
Next
End sub
I have copied and pasted the code and changed JAN to FEB, MAR etc.
Bookmarks