Sort new record after add

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.

Code:

---

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

---

Austenr,

Without seeing your workbook it is difficult to test this, but it doesn't work when you change PutData to:

Code:

---

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
    SortDataList
End Sub

---

? I just added the sortdatalist at the end, and it will be called when the PutData is run.
If not, the only thing I can think of is that the sheet that contains "DataBase" is not the activesheet at the time, as the SortDataList sub is sorting the activesheet. You can adjust for this by changing the SortDataList to:

Code:

---

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

---

Just a thought
Matt

Here is the workbook

When you said you put it at the end, exactly where?

As far as the two changes I suggested above, those will sort the database for you as you first suggested. But when playing around with your file it seems that a few duplicate records are continually being added, and I think it has to do with the PutData subroutine being called at the userform_queryclose event as well as in the scrollbar1_change event.

Why not just have the New button be the only thing that calls PutData? This will prevent the duplicate entries! Try scrolling up to the top of the list, then back down, you'll see you have the same entry for your entire database! As long as the user understands that only the "New" button will add a new entry (which should be the way it is) then you don't have to account for users forgetting to add their entry.

But as for your original question, making the two changes I suggest in my first comment will take care of that.

Matt

Just trying to understand...So I should just make the change as I have below. I already changed the sort sub to add With and End With. Sorry I am asking again just not clear exactly where to put the sort.

Code:

---

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
  SortDataList
End Sub

---

Don't be sorry for trying to understand something :)
The way you have it there is exactly right!

That works. Thanks. Now for my last challenge. I added a print sub at the end that I want to attach to the "Restore" button. I called it but nothing happens. I did set the range in the routine. Can someone please take a look. Thanks. File attached.

First off, your sort function is missing a couple of "." characters, it should be:

Code:

---

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

---

(note the . before each use of Range on the sort line)

Although you do not necessarily need to use "Range("DataBase").Parent" anymore, I just used that when I didn't know the sheet name of "DataSheet". You could use "Sheets("DataSheet")" instead of the .parent part.

As for your print sub, I don't understand why you're setting Row and LastRow, but I'm sure you have a reason for it. But just like with the SortDataList above, there isn't a reason to put With / End With around something if you're not going to use it to qualify anything inside. But I try and stay away from PrintPreviews while using userforms unless you hide the userform first, like:

Code:

---

Sub PrintGroupList()
    Me.Hide
    With Range("DataBase").Parent
        LastRow = .Range("A65536").End(xlUp).Row
        Row = 3
        .Columns("A:D").PrintPreview
    End With
    Me.Show
End Sub

---

Was trying to test it without killing a tree!!!

I understand, which is why I threw that part in there about .Hide'ing and .Show'ing before and after calling the print preview (so that it will work without freezing your system)

geat tip thanks

If you try to scroll down You get a 1004 error. Any ideas?

Code:

---

Option Explicit
Dim iRowNumber As Integer  '\ is the row number in the range "Database" that
Dim LastRow As Integer, Row As Integer                            '\ 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
    Call PrintGroupList
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
    SortDataList
End Sub

Sub SortDataList() '\sorts the data list after new row is entered
    With Range("DataBase").Parent
    .Range("A3:D65536").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    End With
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
Sub PrintGroupList()
    Me.Hide
    LastRow = Range("A65536").End(xlUp).Row
    Row = 3
    Columns("A:D").PrintPreview
    Me.Show
End Sub

---

also if you try to add a new record the print preview pops up. Im confused.

Quote:

---

Originally Posted by austenr

If you try to scroll down You get a 1004 error. Any ideas?

---

It looks like this is because you are still missing a . in the SortDataList. You have

Code:

---

.Range("A3:D65536").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _

---

and it should be

Code:

---

.Range("A3:D65536").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo, _

---

(note the . before the Range in Key1)

Quote:

---

Originally Posted by austenr

also if you try to add a new record the print preview pops up. Im confused.

---

It looks like this is because you're calling the PrintGroupList sub from the EditEntry sub ?? Do you want the database to print/printpreview any time a text box is updated?

Got it all but the print part. When I use the print button nothing happens.

Got it thanks Mvidas

Thanks to Mvidas for the help today. I thought that this was fixed but still have a problem.

There are multiple PutData calls in this routine (UserForm1). The problem lies in the ones in the User_form_query_close and Scroolbar_1_change subs.

If you comment out the PutData routines in these two subs the sort works correctly and you can continue to add records, however you cannot scroll up and down. If you uncomment the calls mentioned above, when you click the "New" button the form will not clear but inserts a blank row. Am enclosing the file. Any help would be appreciated. Thanks again to Mvidas for your help.

Here is the code:

Code:

---

'\ This is a standard statement that causes Excel to check to make sure
'\ each variable in the program is defined (using a Dim statement)

Option Explicit

Dim iRowNumber As Integer  '\ is the row number in the range "Database" that
Dim LastRow As Integer, Row As Integer                            '\ 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 CommandButton5_Click()
PrintGroupList
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 TextBox1_Change()
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
    SortDataList
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

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

Sub PrintGroupList()
    Me.Hide
    With Range("DataBase").Parent
        LastRow = .Range("A65536").End(xlUp).Row
        Row = 3
        .Columns("A:D").PrintPreview
    End With
    Me.Show
End Sub

---

I figured out the scrolling problem, however if you go back and change a record and hit "New", it writes a blank row at the end.

Quote:

---

Originally Posted by austenr

Thanks to Mvidas for the help today. I thought that this was fixed but still have a problem.

---

Austen, please keep all relevant data in the same thread and not in seperate threads. Our forum database would grow exponentially if we did that. ;)

Sorry about that Zack...Could I pick at your brain a minute? The routine above seems to add a record at the top of the range everytime you press the "Close" button. I have been going through it and can't see it. Also, when you press "New" to add a record, it puts a blank row in front of the other rows. Can't seem to see it.