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

Vivek Roshan

New Member
Joined
Feb 15, 2018
Messages
24
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


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

Code:
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



========
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,785
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top