Consulting

Results 1 to 18 of 18

Thread: Sort new record after add

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    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.


    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
    Peace of mind is found in some of the strangest places.

  2. #2
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Austenr,

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

    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:

    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

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Here is the workbook

    When you said you put it at the end, exactly where?
    Peace of mind is found in some of the strangest places.

  4. #4
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    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.

    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
    Last edited by BlueCactus; 04-27-2005 at 11:59 AM. Reason: Added VBA tags
    Peace of mind is found in some of the strangest places.

  6. #6
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Don't be sorry for trying to understand something
    The way you have it there is exactly right!

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    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.
    Peace of mind is found in some of the strangest places.

  8. #8
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    First off, your sort function is missing a couple of "." characters, it should be:

    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:

    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

  9. #9
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Was trying to test it without killing a tree!!!
    Peace of mind is found in some of the strangest places.

  10. #10
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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)

  11. #11
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    geat tip thanks

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


    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.
    Peace of mind is found in some of the strangest places.

  12. #12
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

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

    .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?

  13. #13
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Got it all but the print part. When I use the print button nothing happens.

    Got it thanks Mvidas
    Peace of mind is found in some of the strangest places.

  14. #14
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    Thought I had this solved but.....

    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.
    Peace of mind is found in some of the strangest places.

  15. #15
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Here is the 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
    Peace of mind is found in some of the strangest places.

  16. #16
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    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.
    Peace of mind is found in some of the strangest places.

  17. #17
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

  18. #18
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    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.
    Peace of mind is found in some of the strangest places.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •