How to add or remove names within one sheet and automatically update others

Hi,

I have created (with a lot of help) a tracking and monitoring spreadsheet for school.

I have a sheet within it titled 'Add or Remove Names' that the other sheets pull information from.

How can the spreadsheet be set up so that if I delete a row within this then it will automatically delete the information within the other sheets to do with that particular child.
Similarly if I was to add a child to this sheet it would automatically update the other sheets with the child info also?

It may already be able to do this- I am afraid to try out incase I break it!!

Any thoughts or ideas?

Thanks,
Kevin

I have had some sucess using a 'piviot list' see the example. THen drive all other names in the worksheet from that list.

Jim O

Hi newdoverman and JO505,
What is the difference of using your method to what I already had. Would I have encountered problems with my current system for adding and removing names?

Hi Newdoverman

Sorry to be a pain but would you mind doing your magic on this file also. The previous one that I uploaded had some sheets removed as the file was to big to upload- I didnt think about just compressing the file!

I thought I could just copy the deleted tabs back into the workbook that you adapted for me but it doesn't seem to like this.

Also would you mind giving me a brief explanation as to the benefits of using your system. Will this interfere with any other formulas that i have within the system?

Thank you again very much for your support.

Kevin

Hi newdoverman,

I have trialled out this solution although there seems to be a couple of errors. I deleted a row from 'Add or Remove Names' however this is what happens with the other sheets:

1. it deletes the name, class and date of birth from the other sheets but does not delete the information from the other columns causing all data to be out of sync with the childrens name.
2. when viewing the 'summative assessment' sheet the following message is displayed- 'Run-time error '13' Type mismatch'

Anyone have any ideas how to fix this?

Thanks newdoverman! I may look into possibility of VBA but until then I will just leave pupils on the record.

Hi Kevin,

Try the following Macro to REMOVE all traces of a Student from the Workbook.

Instructions:
a. You must be on Sheet 'Add or Remove Pupil'.
b. Select ('Left Click') any cell in the Workbook.
c. The software will ask you if you REALLY WANT to REMOVE information for the PUPIL on the row selected.
d. Yes or No will determine that pupil's future.

I am working on the ADD STUDENT part of your question. It's a lot more complicated than the DELETE, and should take about another week (not full time).

Option Explicit

Private Const sMasterSheetNAME = "Add or Remove Pupil"

Sub RemoveOnePupilFromWorkbook()
  'This removes all traces of ONE PUPIL from the Workbook

  Dim iRowsToDelete() As Long

  Dim wks As Worksheet
  Dim r As Range
  
  Dim i As Long
  Dim iCount As Long
  Dim iDeleteRow As Long
  Dim iReply As Long
  Dim iRow As Long
  Dim iRowsToDeleteIndex As Long
  
  Dim bNeedMore As Boolean
  
  Dim sAddress As String
  Dim sDateOfBirth As String
  Dim sFirstAddress As String
  Dim sForename As String
  Dim sSheetName As String
  Dim sStage As String
  Dim sTargetValue As String
  Dim sSurname As String
  
  'Turn Screen Updating Off to Improve Speed Performance
  Application.ScreenUpdating = False

  'Get the 'Active Sheet' Name
  'Verify that the 'Active Sheet' is the MASTER Sheet ('Add or Remove Pupil')
  sSheetName = ActiveSheet.Name
  If sSheetName <> sMasterSheetNAME Then
    MsgBox "NOTHING DONE. The Active Sheet MUST BE" & vbCrLf & _
           "Sheet '" & sMasterSheetNAME & "'."
    GoTo MYEXIT
  End If
  
  'Get the Row Number
  'Get Data for the Pupil from the Row
  iRow = ActiveCell.Row
  sSurname = Trim(ActiveSheet.Cells(iRow, "B").Value)
  sForename = Trim(ActiveSheet.Cells(iRow, "C").Value)
  sStage = Trim(ActiveSheet.Cells(iRow, "D").Value)
  sDateOfBirth = Trim(ActiveSheet.Cells(iRow, "E").Value)
  
  'Create the Target Value to Search For of the form: "='Add or Remove Pupil'!B7"
  sTargetValue = "='" & sSheetName & "'!B" & iRow
  'Debug.Print sTargetValue
  
  'Verify that there is a SURNAME on the row
  If Len(sSurname) = 0 Then
    MsgBox "NOTHING DONE. The Surname in Column 'B' of Row " & iRow & vbCrLf & _
           "Sheet '" & sMasterSheetNAME & "' MUST NOT be BLANK."
    GoTo MYEXIT
  End If
  
  'Verify that the User REALLY wants to Delete all traces of the PUPIL
  iReply = MsgBox(Buttons:=vbYesNo, _
            Title:="Permanently Delete a Pupil Record", _
            Prompt:= _
            "Deletion of all trace of the following Pupil has been Selected:" & vbCrLf & _
            "Row:           " & iRow & vbCrLf & _
            "Surname:   " & sSurname & vbCrLf & _
            "Forename: " & sForename & vbCrLf & _
            "Stage:          " & sStage & vbCrLf & _
            "D.O.B.:        " & sDateOfBirth & vbCrLf & vbCrLf & _
            "Select 'Yes' to DELETE Data." & vbCrLf & _
            "Select 'No'  to RETAIN all DATA." & vbCrLf & vbCrLf & _
            "Once Data for a Pupil is DELETED, there is NO UNDO." & vbCrLf & _
            "Close the file WITHOUT Saving if DATA for a Pupil was DELETED IN ERROR." & vbCrLf & _
            "Data SHOULD NOT be DELETED unless a BACKUP copy of the Original File Exists.")
           
  'Exit if the User DOES NOT want to DELETE
  If iReply = vbNo Then
    MsgBox "NOTHING DONE per User Request."
    GoTo MYEXIT
  End If
           
  
  'Process each WORKSHEET in the Workbook
  For Each wks In ActiveWorkbook.Worksheets

    'Get the Sheet Name
    sSheetName = wks.Name
    
    'Process all Sheets EXCEPT the 'MASTER Sheet'
    'NOTE: This will DELETE all occurrences of a PUPIL on a Sheet
    '      in the rare occurrence that a PUPIL is DUPLICATED on a Sheet
    If sSheetName <> sMasterSheetNAME Then
    
      'Initialize (Clear) the Row Numbers in the Delete Array
      iRowsToDeleteIndex = 0
      ReDim iRowsToDelete(1 To 1)
    
    
      'Find the first occurence of the string (PUPIL)
      Set r = Nothing
      Set r = wks.Cells.Find(What:=sTargetValue, _
                      After:=wks.Range("A1"), _
                      LookIn:=xlFormulas, _
                      LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, _
                      MatchCase:=False, _
                      SearchFormat:=False)
    
      'Continue only if a MATCH was found for the PUPIL
      If Not r Is Nothing Then
      
        'Save the found address as the 'First Address'
        sFirstAddress = r.Address(False, False)
        
        'Search for additional values
        'If found add them to the array to be returned
        bNeedMore = True
        While bNeedMore
          
          'Save the count of total Rows to Delete
          iCount = iCount + 1
             
          'Save the row numbers
          iRowsToDeleteIndex = iRowsToDeleteIndex + 1
          ReDim Preserve iRowsToDelete(1 To iRowsToDeleteIndex)
          iRowsToDelete(iRowsToDeleteIndex) = r.Row
          
          
          'Debug.Print iCount, sSheetName, r.Address(False, False), r.Row
          
          'Find the NEXT occurrence
          'Exit when the ORIGINAL address is found a second time (means there are NO MORE MATCHES)
          Set r = wks.Cells.FindNext(After:=r)
          sAddress = r.Address(False, False)
          If sAddress = sFirstAddress Then
            bNeedMore = False
          End If
        Wend
        
        'Delete all matching rows on the Sheet from bottom  to top
        'otherwise the row numbers would be WRONG after the first row was deleted
        For i = iRowsToDeleteIndex To 1 Step -1
          iDeleteRow = iRowsToDelete(i)
          Debug.Print "Sheet '" & sSheetName & "' Deleting row " & iDeleteRow
          wks.Rows(iDeleteRow).EntireRow.Delete
        Next i
      
      End If
    End If
    
  
  Next wks
  
  'Delete the Row on the MASTER SHEET
  iCount = iCount + 1
  iDeleteRow = iRow
  Sheets(sMasterSheetNAME).Rows(iDeleteRow).EntireRow.Delete
  Debug.Print "Sheet '" & sMasterSheetNAME & "' Deleting row " & iDeleteRow
  
  'Output a completed message
  MsgBox "Data DELETED for" & vbCrLf & _
          "Row:           " & iRow & vbCrLf & _
          "Surname:   " & sSurname & vbCrLf & _
          "Forename: " & sForename & vbCrLf & _
          "Stage:          " & sStage & vbCrLf & _
          "D.O.B.:        " & sDateOfBirth & vbCrLf & vbCrLf & _
          iCount & " Rows were Deleted."

MYEXIT:
  Application.ScreenUpdating = True

  'Clear the object pointer
  Set r = Nothing

End Sub

Lewis

Fantastic Lewis!!!

I will give this a try. As always I so appreciate your ongoing support.

Thanks massively!

Please excuse my ignorance LJMetzger but how do I add this macro to my workbook?

Hi Kevin,

I've included the Macro in the attachment to this file.

Installation Instructions:
a. Make a Backup copy of your file.
b. Extract file 'ModAddOrRemoveStudent.bas' from the .zip file (the only file in the .zip file).
c. Open Your file.
d. 'Left Click' on any cell in the Excel Spreadsheet.
e. ALT-F11 to get to VBA.
f. CTRL-R to get project explorer (if it isn't already showing).
g. Right click anywhere in 'Project Explorer'.
h. Select import file. Select file 'ModAddOrRemoveStudent.bas' to import.
i. Save your file.
j. Done.


Instructions for Use (corrected):
a. You must be on Sheet 'Add or Remove Pupil'.
b. Select ('Left Click') any cell in the Workbook.
c. Press 'ALT f8' to get a list of Available Macros.
d. Select Macro 'RemoveOnePupilFromWorkbook()' and Select RUN.
e. The software will ask you if you REALLY WANT to REMOVE information for the PUPIL on the row selected.
f. Yes or No will determine that pupil's future.

I apologize for your problems.

Lewis

Hi Lewis,
I have installed the macro and gave it a trial run.
All seems to work fine except that once a name has been deleted I can no longer search for names within 'Learner Report' (C5)
Is this fixable?

Hi Kevin,

I will fix the 'Learner Report' Problem when I post the 'Add a Student' code.

In the mean time there seems to be an inconsistency for Forename and Surname on the following Sheets:
a. Add or Remove Pupil (Surname in Column B, Forename in Column C)
b. Sort (Composite[Column D] used for sorting has Forename_Surname). This is fine if you want to Sort by Forename, but my speculation is that you want to sort by Surname.
c. Learner Report cells C6 and C7 have Forename and Surname reversed due to Sort Column D.

Lewis

I was unable to put attachment in previous post. Attachment associated with post #19 in this thread is attached to this post.

Hi Lewis,

Thank you. I have now installed your macros and my file is now starting to look a bit more like your sample now. Thank you for the advice.

I have made some amendments to 'Summative Assessments' though as we no longer need the columns on GL Assessments but most of all because the data seemed to have shifted about slightly.

This now looks to be fixed however when I click on 'Correct Cross Reference Errors' Macro then the summative assessment sheet goes all to pot again.

You noted in your last post that if I make changes then I needed to run a macro titled 'ModAddOrRemoveStudent'- however I viewed my macros and could not find this?

Please can you help me to alter the cross reference macro so that it does not alter my 'summative assessment' sheet.

(I've still to play around with add or remove names to check if all working correctly).

Thanks again,
Kevin

Hi Kevin,

It looks like we're very very close to success.

You noted in your last post that if I make changes then I needed to run a macro titled 'ModAddOrRemoveStudent'- however I viewed my macros and could not find this?

My instructions were not clear. I meant Sub CreateSheetAndCellDictionary() must be changed in module 'ModAddOrRemoveStudent' when a change is made. See below.

I have made some amendments to 'Summative Assessments' though as we no longer need the columns on GL Assessments but most of all because the data seemed to have shifted about slightly.

This now looks to be fixed however when I click on 'Correct Cross Reference Errors' Macro then the summative assessment sheet goes all to pot again.

I noticed that the file I sent you seemed to be was off one column from the file I sent you. When you do that in Excel without VBA, Excel magically adjusts all the formulas and references.

With VBA, this is not the case. Each time Excel rows and or columns are changed when using VBA, it is probable that VBA code has to be changed. Typically in this project, changes like that are localized in routines:
a. CreateSheetAndCellDictionary() - This must be changed each time a column is added, deleted or moved to make sure the formulas are correct. It must also be changed each time a formula is added, deleted, or changed.
b. PopulateLearnerReportSheet() - This must be changed each time a design change is made to Sheets:
(1) Add or Remove Pupil
(2) Pastoral
(3) Summative Assessments
(4) Curricular Tracking
(5) Learner Report

You should study the two routines when you have time in the future to see how the recipes work. Eventually you will be able to modify them yourself if needed.

Please can you help me to alter the cross reference macro so that it does not alter my 'summative assessment' sheet.

Install the .bas files in the attached .zip file in your file as follows:

Installation Instructions (full installation not required, because the Sheet code has not changed):
a. Make a backup copy of your file.
b. Extract the following .bas files from the attached .zip file.
(1) ModAddOrRemoveStudent.bas
(2) ModColorCodeSummAssessments.bas
(3) ModHowAreWeDoing.bas
(4) Module1.bas
(5) ModUtilities.bas
c. Open your file.
d. Alt F11 - to get to VBA environment.
e. CTRL R - to open Project Explorer (if not already opened).
f. Delete all the modules listed in b. above (in Project Explorer). 'Delete Module' instructions are included above.
g. Import the modules from b. above (in Project Explorer).
h. Go to Sheet 'Add or Remove Pupil'.
i. Click on the CommandButton to 'Correct Cross Reference Errors'.
j. Save your file.
k. Done.

Since you are nearing a hard time deadline, now is probably not the time to make design changes (adding/removing columns).

Lewis

Thank you very much Lewis.

I have installed the modules and all seems to be working great... apart from one tiny part.

Column AB within 'Summative Assessments is not automatically colour coding like it used to.

How can I get around this?

I now understand that I will need to modify the two routines in the future when new columns or formulas have been added, deleted or moved. Do I need to alter the routines if i add, delete or move names within 'Add or Remove Pupils' also?

Thanks so much,
Kevin

Thank You!!!!!!!

This thread is now complete!! All seems to be working correctly!

I am so grateful for all your support Lewis!

Kevin,

It's nice that you are satisfied. However, I think there may be some subtle problems that neither of us thought of. Please let me know if anything like that shows up.

In the mean time, I will be thinking about a long term fix to sheet reference problems.

Lewis

Hi Lewis- You were correct, there still seems to be some teething issues.

The hope is that we can duplicate this tracking and monitoring sheet so that it can be used in other schools.

I work in two schools so I removed each name from the 'Add or Remove Pupils' sheet using the macro to remove rows one at a time.

I then set about adding children from the other school. This school is larger. This led to my first problem:
1. I could only add a maximum of 99 pupils at one time. Is it possible to adapt the system so that schools can add more than 99 pupils at a time. Ideally if this could be set to 999 then that would be great.

When attempting to add new names I was getting the following problems:
1. A message popped up to say there was a data integrity issue.
2. The 'Correct Cross References' button kept duplicating.
3. Even though I inserted the date of births the spreadsheet did not work out their actual age as before.
4. When adding new names it creates rows that are very wide- can these remain the original size?

Lewis has been a huge help to me but to save bothering him again- if anyone else knows how to fix any of the above then I would be very thankful.

I have attached the spreadsheet so that you can see errors for yourself.

Kevin

You always seem to come up with situations I did not anticipate.

I could only add a maximum of 99 pupils at one time.

In module ModAddOrRemoveStudent starting at line 155, replace 99 with 999 in 3 places:

  'Get the Number of Pupils to Add (1 <= x <= 99)
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
  iPupilsToAdd = Application.InputBox( _
    Prompt:="Enter the Number of Pupils to Add'.", _
    Title:="Add Pupils Data Entry", _
    Default:=1, _
    Type:=1)
  
  If iPupilsToAdd < 1 Or iPupilsToAdd > 99 Then
    MsgBox "NOTHING DONE. The Number of PUPILS to Add must be" & vbCrLf & _
           "a NUMBER between 1 and 99 inclusive."
    GoTo MYEXIT
  End If

When attempting to add new names I was getting the following problems:

I did not anticipate that you would start a new workbook from scratch. I always anticipated that there would be at least one student in the file. That is why you are getting the errors.

There has to be at least one row of existing data remaining in the workbook. When a new pupil is created, the previous row is copied on all sheets, which includes formulas and formatting.

----------------------------------

I know you are excited about how this is working. However, I think a lot more testing is needed, before you let the rest of world loose on the application. I usually required 4 to 12 weeks of testing on a completed application, before releasing completed software. The boss usually won the fight with less testing, and the boss was always sorry. Bosses never seem to learn.

Lewis

Thanks Lewis.

I will adapt the code to suit. Ta

I have saved a new blank version of the spreadsheet with dummy name inserted in first row.

Yeah this is me testing it out before I share it with others. I work in a large school and small school so I am going to test it over the two for a few months to see where the errors lie.

Thank you again!