I have a Sub Called SortDataList that I want to run after a new record is written to the range DataBase. Have tried putting it inside the Sub PutData but it does not work. My code is below. I would appreciate any help anyone can give.


Option Explicit

Dim iRowNumber As Integer  '\ is the row number in the range "Database" that
                            '\ is being changed, added, displayed, etc.
'\ About Moving Through the Database
'\   When the user clicks the scrollbar, it changes iRowNumber
'\   Because the first row of an Excel list contains field headings, record
'\   number 3 will always be stored in row 4 of the range database.
'\   The minimum value of the scrollbar is always set to 2 because the
'\   second row contains the first record.  The maximum is set to the number
'\   of rows in the range named "Database"

Private Sub btnRestore_Click()
    '\ The subroutine "GetData" (see below) copies the values of the cells
    '\ in the range named database to the text boxes on the user form
    Call GetData
'\ Since the text boxes contain the database data, the restore button
    '\ can be grayed out and disabled...until the user makes changes
    '\ to any of the text boxes on the user form sheet.
    btnRestore.Enabled = False
End Sub

Private Sub CommandButton1_Click()
'Add Button
'\ Before starting to add a new record, any changes that were made
    '\ to the last record displayed in the user form are written to the cells
    '\ in the range "Database"  See "PutData" subroutine below.
    Call PutData
    '\ New records are added to the next row below the range named "Database"
    '\ This statement includes this new row in the named range
    Range("DataBase").Resize(Range("Database").Rows.Count + 1).Name = "Database"
    '\ The row number is set to this new row
    iRowNumber = Range("Database").Rows.Count
'\ Probably this new row contains blanks, but by calling the subroutine
    '\ GetData, we display what ever is in this new database row.
    '\ Another options would be to insert a new row in the worksheet
    '\ Or...the program could check to make sure the next row was blank
    '\ and warn the user if it was not.
    Call GetData
    TextA.SetFocus
'\ These statements are explained in the scroll bar subroutine above
    ScrollBar1.Max = Range("Database").Rows.Count
    ScrollBar1.Value = iRowNumber
    btnRestore.Enabled = False
    lblRecNumber.Caption = iRowNumber - 1
End Sub

Private Sub CommandButton2_Click()
    '\ This subroutine assumes that the database contains at least one
    '\ record.  If the user is trying to delete the last record, then
    '\ a warning message is displayed and the program exits this subroutine
    '\ taking the user back to the user form box
    If Range("Database").Rows.Count = 2 Then
        MsgBox "You cannot delete last record", vbExclamation
        Exit Sub
    End If
    '\Before deleting a record, we give the user a chance to cancel.
    If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbOKCancel) = vbCancel Then Exit Sub
'\ This deletes the current row in the range named "Database"
    Range("DataBase").Rows(iRowNumber).EntireRow.Delete
    '\ If the row deleted was the last row, then we must readjust rownumber
    '\ because it will be pointing to the row below the range named "Database"
    If iRowNumber > Range("Database").Rows.Count Then
        iRowNumber = Range("Database").Rows.Count
    End If
    '\ This fills the text boxes on the user form form with data from the row
    '\ that was below the deleted row
    Call GetData
    '\ These statements are explained above in the scroll bar subroutine
    ScrollBar1.Enabled = False
    ScrollBar1.Value = iRowNumber
    ScrollBar1.Max = Range("Database").Rows.Count
    lblRecNumber.Caption = iRowNumber - 1
End Sub
Private Sub CommandButton4_Click()
Unload Me
End Sub

Private Sub ScrollBar1_Change()
'\ Clicking on the scroll bar means it is time to move to a new record
    '\ and before that happens, changes to the current record must be stored
    '\ in the cells of the range "Database"
    Call PutData
    '\ Clicking on the scroll bar changes its value - from the minumum to
    '\ the maximum.  This value is used to select a new row from range
    iRowNumber = ScrollBar1.Value
    '\ Gray out the restore button - just in case it was enabled before
    '\ the scroll bar was clicked on
    btnRestore.Enabled = False
'\ This subroutine (see below) gets data from cells in the range and
    '\ copies it to the text boxes on the user form
    Call GetData
    '\ So that the cursor will be in the first text box on the user form, this
    '\ statement sets the focus of the user form to that text box.
    TextA.SetFocus
    '\ Before the new record is displayed, the label on the user form that contains
    '\ the current record number is updated.
    lblRecNumber.Caption = iRowNumber - 1
End Sub

Private Sub TextA_Change()
Me.EditEntry
End Sub

Private Sub TextB_Change()
Me.EditEntry
End Sub

Private Sub TextC_Change()
Me.EditEntry
End Sub

Private Sub TextD_Change()
Me.EditEntry
End Sub

Private Sub UserForm_Activate()
'\ This code runs when the userform is activated.
'\ The lastRecordNumber range stores the last record you worked on
iRowNumber = Range("lastRecordNumber")
'\ But, if the database range has been updated manually, oRecNum may contain
'\ a row number that is no longer in the database range.  If so, the program
'\ set iRownumber to the last row in the database range.
If iRowNumber > Range("Database").Rows.Count Then iRowNumber = Range("Database").Rows.Count
'\ The scroll bar has to match the record number
Call GetData
ScrollBar1.Value = iRowNumber
'\ This keeps the scroll bar from going past the last record in the range
ScrollBar1.Max = Range("Database").Rows.Count
'\ Now we can fill the text boxes on the user form with data from the range
'\ We do it in a subroutine called "GetData" (see below) that can be
'\ called from different subroutines
'\ One last bit of set-up...throughout the subroutines, we set the value
'\ of oRestoreButton.Enabled because we only want this button to work
'\ if the user has entered something in one of the text boxes.  Otherwise
'\ there is nothing to restore. By setting enabled to false, Excel will
'\ "gray" the caption on the button so the user will know it is not a
'\ valid selection
btnRestore.Enabled = False
End Sub
      
Sub EditEntry() '\ called every time the user types anything into an text box
    '\ When the edit field data is changed, the restore button gives the user
    '\ the option of undoing any changes.  The restore button is "grayed out"
    '\ and cannot be pushed until this subroutine is called
    btnRestore.Enabled = True
End Sub
Sub GetData() '\ called by other subroutines
    '\ A "With" statement simplifies coding.  Without it, the characters
    '\ "user formSheets("DBUpdate")" would be needed everywhere there
    '\ is a period that starts an expression below:
    With Me
        .TextA = Range("DataBase").Cells(iRowNumber, 1)
        .TextB = Range("DataBase").Cells(iRowNumber, 2)
        .TextC = Range("DataBase").Cells(iRowNumber, 3)
        .TextD = Range("DataBase").Cells(iRowNumber, 4)
    End With
End Sub

Sub PutData() '\ called by other subroutines
    With Me
        Range("DataBase").Cells(iRowNumber, 1) = .TextA
        Range("DataBase").Cells(iRowNumber, 2) = .TextB
        Range("DataBase").Cells(iRowNumber, 3) = .TextC
        Range("DataBase").Cells(iRowNumber, 4) = .TextD
    End With
End Sub

Sub SortDataList() '\sorts the data list after new row is entered
    Range("A2:D65000").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    '\ When we get here, it means that the user has pushed the "Close" button or clicked the X button
    '\ The first thing we want to do is store any changes made to the last record
    '\ that was displayed in the user form box.  Subroutine "PutData" copies the
    '\ values in the text boxes on the user form to the cells in the data range.
    '\ It is a subroutine because it is called from several places in the
    '\ data base update logic...when we add a new record, click on the scrollbar
    '\ etc.
    Call PutData
'\ Before leaving the user form - set the last worked on record number
    '\ so when the user form is opened, we will know which record to display
    Range("lastRecordNumber") = iRowNumber
End Sub