In the last 3 months I have been trying to get more familiar with Excel and every time I Google any question I may have it always brings me here and I almost always find what I'm looking for so thank you everyone that contributes to this site.
I feel bad for asking since I'm a novice and don't have much knowledge to contribute but I was hoping for some help. I've been trying to modify a free user form I found online to better suit my purposes. The user form searches the first column of my spreadsheet and returns the data on that on that row based on what's entered in a textbox and then allows you to toggle up or down from the result between records. The search feature works fine but all the identifiers need to be unique. I'd like to instead have the search feature filter and only give results that match the search criteria regardless of the values in the first column being unique or not. But more importantly I need to add a combobox to add information to the last column of the row you're currently viewing. Any help would be appreciated. I'll leave a link to the file on Google Drive and the code below.
https://drive.google.com/open?id=0BxJ8xb0y2VlETlBnVnIwZFF5RjQ
Private Sub cmdClose_Click()
'close the userform
Unload Me
End Sub
Private Sub cmdSearch_Click()
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error GoTo errHandler:
'find the row with the data
cRow = Me.txtsearch.Value
Set FindRow = Sheet1.Range("C:C").Find(What:=cRow, LookIn:=xlValues)
'add the values to the userform
Me.control1.Value = FindRow
Me.control2.Value = FindRow.Offset(0, 1)
Me.control3.Value = FindRow.Offset(0, 2)
Me.control4.Value = FindRow.Offset(0, 3)
Me.control5.Value = FindRow.Offset(0, 4)
Me.control6.Value = FindRow.Offset(0, 5)
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "Error! Check you have typed the data correctly " & vbCrLf & Err.Description
End Sub
Private Sub CmndSubmit_Click()
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub ToggleButton1_Click()
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error Resume Next
'find the row with the data and move up one row
cRow = Me.control1.Value
Set FindRow = Sheet1.Range("C:C").Find(What:=cRow, LookIn:=xlValues).Offset(-1, 0)
If FindRow.Value = Sheet1.Range("C4").Value Then Exit Sub
Me.txtsearch = FindRow.Value
'add the values to the userform with a loop
cNum = 6
For x = 1 To cNum
Me.Controls("Control" & x).Value = FindRow '.Offset(1, 0)
Set FindRow = FindRow.Offset(0, 1)
Next
'error block
On Error GoTo 0
End Sub
Private Sub ToggleButton2_Click()
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error Resume Next
'find the row with the data and move down one row
cRow = Me.control1.Value
Set FindRow = Sheet1.Range("C:C").Find(What:=cRow, LookIn:=xlValues).Offset(1, 0)
If FindRow.Value = "" Then Exit Sub
Me.txtsearch = FindRow.Value
'add the values to the userform with a loop
cNum = 6
For x = 1 To cNum
Me.Controls("Control" & x).Value = FindRow '.Offset(1, 0)
Set FindRow = FindRow.Offset(0, 1)
Next
'error block
On Error GoTo 0
End Sub
Private Sub UserForm_Click()
End Sub
I feel bad for asking since I'm a novice and don't have much knowledge to contribute but I was hoping for some help. I've been trying to modify a free user form I found online to better suit my purposes. The user form searches the first column of my spreadsheet and returns the data on that on that row based on what's entered in a textbox and then allows you to toggle up or down from the result between records. The search feature works fine but all the identifiers need to be unique. I'd like to instead have the search feature filter and only give results that match the search criteria regardless of the values in the first column being unique or not. But more importantly I need to add a combobox to add information to the last column of the row you're currently viewing. Any help would be appreciated. I'll leave a link to the file on Google Drive and the code below.
https://drive.google.com/open?id=0BxJ8xb0y2VlETlBnVnIwZFF5RjQ
Private Sub cmdClose_Click()
'close the userform
Unload Me
End Sub
Private Sub cmdSearch_Click()
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error GoTo errHandler:
'find the row with the data
cRow = Me.txtsearch.Value
Set FindRow = Sheet1.Range("C:C").Find(What:=cRow, LookIn:=xlValues)
'add the values to the userform
Me.control1.Value = FindRow
Me.control2.Value = FindRow.Offset(0, 1)
Me.control3.Value = FindRow.Offset(0, 2)
Me.control4.Value = FindRow.Offset(0, 3)
Me.control5.Value = FindRow.Offset(0, 4)
Me.control6.Value = FindRow.Offset(0, 5)
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "Error! Check you have typed the data correctly " & vbCrLf & Err.Description
End Sub
Private Sub CmndSubmit_Click()
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub ToggleButton1_Click()
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error Resume Next
'find the row with the data and move up one row
cRow = Me.control1.Value
Set FindRow = Sheet1.Range("C:C").Find(What:=cRow, LookIn:=xlValues).Offset(-1, 0)
If FindRow.Value = Sheet1.Range("C4").Value Then Exit Sub
Me.txtsearch = FindRow.Value
'add the values to the userform with a loop
cNum = 6
For x = 1 To cNum
Me.Controls("Control" & x).Value = FindRow '.Offset(1, 0)
Set FindRow = FindRow.Offset(0, 1)
Next
'error block
On Error GoTo 0
End Sub
Private Sub ToggleButton2_Click()
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error Resume Next
'find the row with the data and move down one row
cRow = Me.control1.Value
Set FindRow = Sheet1.Range("C:C").Find(What:=cRow, LookIn:=xlValues).Offset(1, 0)
If FindRow.Value = "" Then Exit Sub
Me.txtsearch = FindRow.Value
'add the values to the userform with a loop
cNum = 6
For x = 1 To cNum
Me.Controls("Control" & x).Value = FindRow '.Offset(1, 0)
Set FindRow = FindRow.Offset(0, 1)
Next
'error block
On Error GoTo 0
End Sub
Private Sub UserForm_Click()
End Sub