Userforms Help

[Eryrok]

I have a data set that I need to be able to search and edit through a userform for some of our less computer savy employees, I've added what I have so far but I have yet to find a way to search based on Work Order number and UNID number to return the entire row in the userform. Any help you can offer is greatly appreciated. I have spent the better part of 2 weeks working on and searching for this. My data is set up as so:

A |B |C |D |E |F |H |I
work order| foreman| system | UNID |design| installed| % | comments
111-0452 | Lewis | 063 | 2vc18 |100 |10 | = | none

my code so far is: (but i have an error I can't figure out)

Private Function FindLastRow()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
FindLastRow = iRow

End Function
Private Sub Add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.WO.Value) = "" Then
Me.WO.SetFocus
MsgBox "Please enter a work order number"
Exit Sub
End If
If Trim(Me.Foreman.Value) = "Select Foreman" Then
Me.Foreman.SetFocus
MsgBox "Please select a Foreman"
Exit Sub
End If
If Trim(Me.System.Value) = "" Then
Me.System.SetFocus
MsgBox "Please enter a System number"
Exit Sub
End If
If Trim(Me.UNID.Value) = "" Then
Me.UNID.SetFocus
MsgBox "Please enter a UNID number"
Exit Sub
End If
If Trim(Me.Design.Value) = "" Then
Me.Design.SetFocus
MsgBox "Please enter a Designed Length"
Exit Sub
End If
If Trim(Me.Footage.Value) = "" Then
Me.Footage.SetFocus
MsgBox "Please enter a Installed Length"
Exit Sub
End If
If Trim(Me.Comments.Value) = "" Then
Me.Comments.Value = "None"
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.WO.Value
ws.Cells(iRow, 2).Value = Me.Foreman.Value
ws.Cells(iRow, 3).Value = Me.System.Value
ws.Cells(iRow, 4).Value = Me.UNID.Value
ws.Cells(iRow, 5).Value = Me.Design.Value
ws.Cells(iRow, 6).Value = Me.Footage.Value
ws.Cells(iRow, 8).Value = ""
ws.Cells(iRow, 9).Value = Me.Comments.Value
'clear the data
Me.WO.Value = ""
Me.Foreman.Value = ""
Me.System.Value = ""
Me.UNID.Value = ""
Me.Design.Value = ""
Me.Footage.Value = ""
Me.Comments.Value = ""
Me.WO.SetFocus
End Sub
Private Sub First_Click()
RowNumber.Text = "2"
End Sub
Private Sub Frwd_Click()
Dim n As Long
If IsNumeric(RowNumber.Text) Then
n = CLng(RowNumber.Text)

n = n + 1
If n > 1 And n <= LastRow Then
RowNumber.Text = FormatNumber(n, 1)
End If
End If

End Sub
Private Sub Last_Click()
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
Private Sub Prev_Click()
Dim n As Long
If IsNumeric(RowNumber.Text) Then
n = CLng(RowNumber.Text)

n = n - 1
If n > 1 And n <= LastRow Then
RowNumber.Text = FormatNumber(n, 0)
End If
End If

End Sub
Private Sub RowNumber_Change()
GetData

End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Data")
Dim wsf As Worksheet
Set wsf = Worksheets("Sheet3")
Dim cLoc As Range
LastRow = FindLastRow
GetData

For Each cLoc In wsf.Range("ForemanList")
With Me.Foreman
.AddItem cLoc.Value
End With
Next cLoc
End Sub
Private Sub GetData()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
LastRow = FindLastRow
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)

Else
ClearData
MsgBox "Illegal row number"
Exit Sub

End If

If r > 1 And r <= LastRow Then

WO.Text = Cells(r, 1)
Foreman.Text = Cells(r, 2)
System.Text = Cells(r, 3)
UNID.Text = Cells(r, 4)
Design.Text = Cells(r, 5)
Footage.Text = Cells(r, 6)
Comments.Text = Cells(r, 8)

DisableSave

ElseIf r = 1 Then
ClearData

Else
ClearData
MsgBox "Invalid row number"

End If

End Sub
Private Sub ClearData()
WO.Text = ""
Foreman.Text = "Select Foreman"
System.Text = ""
UNID.Text = ""
Design.Text = ""
Footage.Text = ""
Comments.Text = ""
End Sub
Private Sub DisableSave()
Save.Enabled = False
Cancel.Enabled = False
End Sub

Thanks in advance for your time and effort.

 

[bertie]

Hi Eryrok and welcome to the board.

If you are posting code you can make it easier to read by enclosing it in 'code tags', use the '#' icon on the message box toolbar.

I can't see how you obtain a search parameter from the user, so I can only give you some general advice.

The first thing you are going to need is a value from the user.

For example, in the GetData procedure, assuming the WO_Number is unique

 WO_Number = "get the WO_Number to search for from the user"

Using this value search the 'Data' worksheet for a 'Match' which will give you the row number the information is on

      [COLOR=green]'get the row number of the WO Number[/COLOR]
      r = Application.WorksheetFunction.Match(WO_Number, _
           ws.Range("A1:A" & LastRow), 0)

If a match is found then use the 'r' value to populate your 'TextBoxes'.
But you will have to cater for no match being found, so wrap this within an error trap. For example the full process would be:

   WO_Number = "get the WO_Number to search for from the user"
 
   [COLOR=red]On Error Resume Next[/COLOR]
      [COLOR=green]'get the row number of the WO Number[/COLOR]
      r = Application.WorksheetFunction.Match(WO_Number, _
           ws.Range("A1:A" & LastRow), 0)
      [COLOR=darkblue]If[/COLOR] r = 0 [COLOR=darkblue]Then[/COLOR]
         MsgBox "No match Found"
         [COLOR=green]'do something if no match found?[/COLOR]
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'populate textboxes?[/COLOR]
         RowNumber.Text = r
         WO.Text = Cells(r, 1)
         Foreman.Text = Cells(r, 2)
         System.Text = Cells(r, 3)
         UNID.Text = Cells(r, 4)
         Design.Text = Cells(r, 5)
         Footage.Text = Cells(r, 6)
         Comments.Text = Cells(r, 8)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=red]On Error GoTo 0[/COLOR]   [COLOR=green]'report errors as normal[/COLOR]

If the WO_Number is not unique then you will have to get both the WO_Number and UNID_Number from the user.

Then obtain the row number by maybe looping through column A of the 'Data' sheet to find the WO_Number and perform a check to see if the UNID_Number matches.

Hope this helps,
Bertie

 

[Eryrok]

Thanks Bertie that is exactly what I was asking for. I will have to match the WO and UNID numbers to return a result. Thanks for your help.