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
Reply With Quote
Originally Posted by austenr
