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.
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.