Update navigate records Excel worksheet with user form

How to update navigate records Excel worksheet with user form. Before you start coding the command buttons enter this line of code right at the top:

Dim currentrow As Long

This line of code defines a variable called ‘currentrow’ and it will be available to all the modules in this workbook.

Now watch the training video before you view the rest of the code:

 

We create our user-form with the following buttons:

  • Clear – Clears the data from the user-form controls and sets the cursor on the combo-box. The VBA code is: Private Sub cmdClear_Click()
    cboTitle.Text = “”
    txtFname = “”
    txtLname = “”
    End Sub
  • Add – Adds records to our Excel worksheet by first locating the next blank row. The VBA code is: Private Sub cmdAdd_Click()
    If cboTitle.Text = “” Then
    MsgBox “Please select a title”
    cboTitle.SetFocus
    Exit Sub
    End If
    If txtFname.Text = “” Then
    MsgBox “Please enter a first name”
    txtFname.SetFocus
    Exit Sub
    End If
    If txtLname.Text = “” Then
    MsgBox “Please enter a last name”
    txtLname.SetFocus
    Exit Sub
    End If
    erow = Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(erow, 1) = cboTitle.Text
    Cells(erow, 2) = txtFname.Text
    Cells(erow, 3) = txtLname.Text
    End Sub
  • Previous button – navigates us to the previous record.  The VBA code is:Private Sub cmdPrevious_Click()
    currentrow = currentrow – 1
    If currentrow > 1 Then
    cboTitle.Text = Cells(currentrow, 1)
    txtFname.Text = Cells(currentrow, 2)
    txtLname.Text = Cells(currentrow, 3)
    End IfIf currentrow = 1 Then
    MsgBox “Now you are in the header row!”, vbCritical
    currentrow = currentrow + 1
    End IfEnd Sub
  • Next button – navigates user to the next record in the Excel worksheet. The VBA code is:Private Sub cmdNext_Click()
    Dim lastrow As Long
    lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).RowIf currentrow = lastrow Then
    MsgBox “you are viewing the last row of data”, vbCritical
    Else
    currentrow = currentrow + 1
    cboTitle.Text = Cells(currentrow, 1)
    txtFname.Text = Cells(currentrow, 2)
    txtLname.Text = Cells(currentrow, 3)
    End IfEnd Sub
  • Update command button – updates the record in the Excel worksheet of the current record. The VBA code: Private Sub cmdUpdate_Click()
    Cells(currentrow, 1) = cboTitle.Text
    Cells(currentrow, 2) = txtFname.Text
    Cells(currentrow, 3) = txtLname.Text
    End Sub
  • VBA code when the user-form initializes:Private Sub UserForm_Initialize()
    currentrow = 2
    cboTitle.List = Array(“Ms”, “Mrs”, “Mr”, “Dr”, “Prof”)
    cboTitle.Text = Cells(currentrow, 1)
    txtFname.Text = Cells(currentrow, 2)
    txtLname.Text = Cells(currentrow, 3)
    cboTitle.SetFocus
    End Sub
  • Visual Basic for Applications code when the workbook is opened: Private Sub Workbook_Open()
    UserForm1.Show
    End Sub

Further reading:

Variable Scope

Database and Customer Relationship Management with Excel

4 thoughts on “Update navigate records Excel worksheet with user form

  1. Ravi James

    ahm can we add match destination formula where you will find the value ex associate ID before it add or populate the data.

    Thanks in Advance

    Reply
  2. raja

    IS IT POSSIBLE TO UPDATE DATA OF A CLOSED WORKBOOK WITHOUT OPENING IT.?
    MY COSTING WORKBOOKS ARE LINKED TO ONE DATABASE WORKBOOK.SUDDENLY I GET NOTICE OF PRICE CHANGES OF ITEMS IN THIS DATABASE.CAN I UPDATE THEM WITHOUT REALLY OPENING THIS DATA BASE.? ITEMS ARE RECORDED UNDER DIFFERENT CATEGORIES. PL.HELP
    THANKS FOR YOUR VALUABLE SUPPORT.

    Reply
  3. Ken

    I : instance 1 – combobox selected – populated textboxes
    Q : How can i Update combobox navigated records Excel worksheet with user form?

    Thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *