Code for Search btn and Prev/Next Btn???

[kamal1234]

Hi all,

It's been just over a day since I have been learning VBA.

I want data to be navigated from the entire workbook, not worksheet.

I would like to add Prev/Next btns to my userform that will navigate records, I want the data to be displayed in textboxes as buttons are selected.

I would also like to add a Search btn that will pick up data values from the workbook and display a row record in textboxe



This is my current code for my search button. I have received help with all three codes.

Private Sub cmdSearch_Click()
Dim ws As Worksheet
Dim strSearch As String
Dim aCell As Range, v
On Error GoTo Err
'validate text box
v = Trim(TextBox30.Value)
If Len(v) = 0 Then
    MsgBox "Please Enter Data."
    Me.TextBox30.SetFocus
    Exit Sub
End If
Set aCell = Sheets("July").Range("J:J").Find(v, lookat:=xlWhole)
If Not aCell Is Nothing Then
    With aCell.EntireRow
        TextBox1.Text = .Cells(, "A").Value
        TextBox2.Text = .Cells(, "B").Value
        TextBox3.Text = .Cells(, "C").Value
        TextBox4.Text = .Cells(, "D").Value
        TextBox5.Text = .Cells(, "E").Value
        TextBox6.Text = .Cells(, "F").Value
        TextBox7.Text = .Cells(, "G").Value
        TextBox8.Text = .Cells(, "H").Value
        TextBox9.Text = .Cells(, "I").Value
        TextBox10.Text = .Cells(, "J").Value
        TextBox11.Text = .Cells(, "K").Value
        TextBox12.Text = .Cells(, "L").Value
        TextBox13.Text = .Cells(, "M").Value
        TextBox14.Text = .Cells(, "N").Value
        TextBox15.Text = .Cells(, "O").Value
        TextBox16.Text = .Cells(, "P").Value
        TextBox17.Text = .Cells(, "Q").Value
        TextBox18.Text = .Cells(, "R").Value
        TextBox19.Text = .Cells(, "S").Value
    End With
Else
    MsgBox "Data Value Not Found."
    'frmSearch.TextBox30.Value = ""
    TextBox30.SetFocus
End If
Exit Sub
Err:
    MsgBox Err.Description
End Sub

I want this code to find data from the entire workbook. Not worksheet.

This is my code for Prev/Next btns

Private Sub CommandButton2_Click()
'previous
    TraverseData nCurrentRow, "p"
End Sub

Private Sub CommandButton3_Click()
'next
    TraverseData nCurrentRow, "n"
End Sub

Private Sub TraverseData(nRow As Long, sDirection As String)
Select Case sDirection
    Case "p": nCurrentRow = nCurrentRow - 1 ' previos
    Case "n": nCurrentRow = nCurrentRow + 1 ' next
    Case Else: nCurrentRow = nCurrentRow    ' initiaise
End Select
If nCurrentRow < lFirstRow Then nCurrentRow = lLastRow
If nCurrentRow > lLastRow Then nCurrentRow = lFirstRow
If Sheet1.Rows(nCurrentRow).EntireRow.Hidden = True Then
    TraverseData nRow, IIf(sDirection = "", "n", sDirection)
End If
Me.TextBox1.Value = Sheet1.Cells(nRow, 1)
Me.TextBox2.Value = Sheet1.Cells(nRow, 2)
Me.TextBox3.Value = Sheet1.Cells(nRow, 3)
Me.TextBox4.Value = Sheet1.Cells(nRow, 4)
Me.TextBox5.Value = Sheet1.Cells(nRow, 5)
Me.TextBox6.Value = Sheet1.Cells(nRow, 6)
Me.TextBox7.Value = Sheet1.Cells(nRow, 7)
Me.TextBox8.Value = Sheet1.Cells(nRow, 8)
Me.TextBox9.Value = Sheet1.Cells(nRow, 9)
Me.TextBox10.Value = Sheet1.Cells(nRow, 10)
Me.TextBox11.Value = Sheet1.Cells(nRow, 11)
Me.TextBox12.Value = Sheet1.Cells(nRow, 12)
Me.TextBox13.Value = Sheet1.Cells(nRow, 13)
Me.TextBox14.Value = Sheet1.Cells(nRow, 14)
Me.TextBox15.Value = Sheet1.Cells(nRow, 15)
Me.TextBox16.Value = Sheet1.Cells(nRow, 16)
Me.TextBox17.Value = Sheet1.Cells(nRow, 17)
Me.TextBox18.Value = Sheet1.Cells(nRow, 18)
Me.TextBox19.Value = Sheet1.Cells(nRow, 19)
End Sub

Private Sub UserForm_Initialize()
lFirstRow = 8
lLastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
nCurrentRow = lFirstRow
TraverseData nCurrentRow, ""
End Sub

This only navigates data from sheet1, not the entire workbook which is what I would like.



Many thanks all for any advice.