Selected Answer
Ghost
Your speed problem with the file puzzled me for a while then I realised that the problem was the recalculation of formulae (e.g. the SORT spill in your table in the worksheet Master). It adds seconds since it happens every time a cell value is changed by your code.
In the attached revised file, I changed the method to Manual- that makes the changes almost instantaneous but that table isn't updated so, in the code behind JaneDoe (only), I made these changes (in bold):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim rowNum As Long
Dim ws As Worksheet
Dim password As String
Dim firstRow As Long
Dim lastRow As Long
Dim i As Long
Dim newRow As Range
Application.EnableEvents = False
Set ws = Me
password = "admin" ' Replace with your actual password
firstRow = 9 ' Starting row for processing
' Check if the changed cell is in column C
If Not Intersect(Target, ws.Columns("C")) Is Nothing Then
Application.EnableEvents = False
' set calc method to manual for speed
Application.Calculation = xlCalculationManual
On Error GoTo ErrorHandler
' Unprotect the worksheet before making changes
ws.Unprotect password:=password
' << existing code unchanged>>
Application.EnableEvents = True
' recalculate all sheets (will take time to )
Calculate
Worksheets("MASTER").Calculate
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbExclamation
Application.EnableEvents = True
' Ensure protection is reapplied even if an error occurs
'ws.Protect password:=password, _
' AllowInsertingRows:=True, _
' AllowDeletingRows:=True
End If
End Sub
Now the message appears very soon after you add a value to column C (say) of the Jane Doe sheet (since the recalculation is really done after that message).
You'd need to change that for each sheet (or -better- have a new common code in a module and pass the sheet name and Target to that new macro).
You could set the method to Automatic (or Semi...) via menu File/ Options/ Formulas/ Calculation Options and add code to capture the method at the start (before Application.Calculation = xlCalculationManual) and restore it at the end if it's not xlManual (when you wouldn't need the Calculate line).
Revision #1 dated 23 July 2024
Further to the point below (code not working after Jane Doe "speed up" elements copied correctly to Rik), the problem is NOT because of my code changes above but due to flaw in your logic in the module 2 code AddSheetNameToConfig.
If you add a new line to the sheet Rik, your code the associated Worksheet_Change event code calls AddSheetNameToConfig which finds "Rik" at $B$7 of Config then searches from $B$7 to $B$38 looking to match the row number ONLY. It finds a match at $B$13 (for example), which is a Jane Doe entry. You then get a MsgBox to says that already exists (though one doesn't for Rik and the row number!).
The simple fix is to change the following lines within the module 2 code AddSheetNameToConfig (changes in bold):
' Sheet name found, now check if the row number exists in column B for this sheet name
For Each cell In .Range(.Cells(foundSheetName.Row, "B"), .Cells(lastRow, "B"))
' check that both row and sheet match
If cell.Value = currentRow And cell.Offset(0, 1).Value = sheetName Then
I did that in the revised file attached below.
Hope this fixes your problem (or sets you one the right path). Don't forget to mark as Selected if so.
