How to code for search, previous, next, update, delete, print command buttons in user form in excel vba

[Vivek Roshan]

Dear Sir,

I am newly learning the creating of user forms in excel VBA. For my office filing system, I have created a user form for incoming correspondence in SHEET1 to input data into SHEET2 in the same workbook. Userform have command buttons namely CLEAR, ADD, UPDATE, SEARCH, PREVIOUS, NEXT, PRINT, DELETE AND CLOSE. I have written the code for CLEAR, ADD AND CLOSE which are working correctly. I am not able to code for SEARCH, PREVIOUS, NEXT, UPDATE, PRINT AND DELETE. The coding that which has been written for CLEAR, ADD AND CLOSE is mentioned below along with code for other command button (Not working) : For your information, in data SHEET2, the rows from 1-3 are kept for headers and the actual data starts from Row 4.

Can any please guide me in writing code for SEARCH, PREVIOUS, NEXT, UPDATE, PRINT AND DELETE?

I shall highly appreciate your kind help in this regard. Please help me.

With best regards,
Vivek Roshan


============

Dim currentrow As Long
Dim lastrow As Long
Dim erow As Long
Dim count As Integer


Private Sub Cmd_Add_Click()
lastrow = Sheets("ICC_Data").Range("A" & Rows.count).End(xlUp).Row
Sheets("ICC_Data").Cells(lastrow + 1, "A").Value = TextBox1.Text
Sheets("ICC_Data").Cells(lastrow + 1, "B").Value = TextBox2.Text
Sheets("ICC_Data").Cells(lastrow + 1, "C").Value = TextBox3.Text
Sheets("ICC_Data").Cells(lastrow + 1, "D").Value = TextBox4.Text
Sheets("ICC_Data").Cells(lastrow + 1, "E").Value = TextBox5.Text
Sheets("ICC_Data").Cells(lastrow + 1, "F").Value = TextBox6.Text
Sheets("ICC_Data").Cells(lastrow + 1, "G").Value = ListBox1.Value
Sheets("ICC_Data").Cells(lastrow + 1, "H").Value = ListBox2.Value


  If OptionButton1.Value Then
        Sheets("ICC_Data").Cells(lastrow + 1, "I").Value = "YES"
      Else
        If OptionButton2.Value Then
            Sheets("ICC_Data").Cells(lastrow + 1, "I").Value = "NO"
        End If
    End If
  If OptionButton3.Value Then
        Sheets("ICC_Data").Cells(lastrow + 1, "I").Value = "PENDING"
  End If


Sheets("ICC_Data").Cells(lastrow + 1, "J").Value = TextBox7.Text
Sheets("ICC_Data").Cells(lastrow + 1, "K").Value = TextBox8.Text
 
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
OptionButton1.Value = ""
OptionButton2.Value = ""
OptionButton3.Value = ""
TextBox7.Text = ""
TextBox8.Text = ""
End Sub
=====================
Private Sub Cmd_Clear_Click()


'WITH DIM CTL AS CONTROL DID NOT WORK
'Dim ctl As Control
' For Each ctl In UserForm1.Controls
  '  If TypeName(clt) = "Textbox" Then
    '    ctl.Value = ""
   ' End If
'Next ctl




TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
OptionButton1.Value = ""
OptionButton2.Value = ""
OptionButton3.Value = ""
TextBox7.Text = ""
TextBox8.Text = ""


End Sub
===================
Private Sub Cmd_Close_Click()
Unload Me
End Sub
=================

Private Sub Cmd_Next_Click()
currentrow = Sheets("ICC_Data").Cells(Rows.count, 4)
lastrow = Sheets("ICC_Data").Cells(Rows.count, 4).End(xlUp).Row
 If currentrow = lastrow Then
 MsgBox "You are viewing the last row of data!"
 Exit Sub
 End If
 
currentrow = currentrow + 1
 
   
 'TextBox1 = Cells(currentrow, 1)
' TextBox2 = Cells(currentrow, 2)
 'TextBox3 = Cells(currentrow, 3)




'currentrow = 0
'lastrow = Sheets("ICC_Data").Range("A" & Rows.count).End(xlUp).Row
'currentrow = currentrow + 1
    'If currentrow = lastrow + 1 Then
       'currentrow = lastrow
       'MsgBox "You have reached the lastrow!"
    
TextBox1 = Sheets("ICC_Data").Cells(lastrow + 1, "A").Value
'TextBox2.Text = Sheets("ICC_Data").Cells(lastrow + 1, "B").Value
'TextBox3.Text = Sheets("ICC_Data").Cells(lastrow + 1, "C").Value
'TextBox4.Text = Sheets("ICC_Data").Cells(lastrow + 1, "D").Value
'TextBox5.Text = Sheets("ICC_Data").Cells(lastrow + 1, "E").Value
'TextBox6.Text = Sheets("ICC_Data").Cells(lastrow + 1, "F").Value
'ListBox1.Value = Sheets("ICC_Data").Cells(lastrow + 1, "G").Value
'ListBox2.Value = Sheets("ICC_Data").Cells(lastrow + 1, "H").Value
   
  ' End If


End Sub
===============

Private Sub Cmd_Previous_Click()


End Sub
==================
Private Sub Cmd_Report_Click()


End Sub
===================
Private Sub Cmd_Search_Click()
Dim totrows As Long, i As Long
totrows = Worksheets("ICC_Data").Range("A1").CurrentRegion.Rows.count
For i = 2 To totrows
    If Trim(ICC_Data.Cells(i, 1)) = Trim(TextBox1.Text) Then
      TextBox1.Text = ICC_Data.Cells(i, 1)
      TextBox2.Text = ICC_Data.Cells(i, 2)
      TextBox3.Text = ICC_Data.Cells(i, 3)
    Exit For
    End If
Next i


End Sub

========