Automatisch een formule door kopieren

[obiku]

All,

Ik hoop dat iemand mij bij een voor mij lastige issue kan helpen. Ik zal eerst even een beeld scheppen van het hoe en waarom.

Als leidinggevende van een aantal mensen dien ik deze 3 maal per jaar te spreken ivm funtioneren. In het verleden zette ik diverse dingen voor mezelf op papier (Word). Maar Word maakte het lastig om zaken te bekijken, vooral indien men lang in dienst was/is.

Vanwege dit heb ik voor mezelf een excel personeelsdossier aangemaakt. Dit dossier bestaat uit een aantal tabbladen, waaronder een tab personeelsgegevens (hierin wordt ook een simpel overzicht gegeven van de andere tabbladen), een tab verzuim, en een tab feedback. In de tabs verzuim en feedback heb ik een aantal formules staan.

In verzuim heb ik een formule waarmee ik bereken hoeveel dagen iemand ziek geweest is of hoeveel dagen hij ziek is. Nu hebben een aantal collega's mijn dossier gezien en hadden hier ook oren naar. Vanwege dit feit heb ik de diverse tabs beveiligd met een password. Nu loop ik tegen het volgende probleem op.

Daar ikzelf dit dossier heb gemaakt weet ik dat ik de formule moet doorkopieren naar een volgende regel zodra er een nieuwe verzuim regel bij komt. Om te voorkomen dat een andere collega in zijn dossier de formule om zeep helpt heb ik dus aangegeven bij beveiliging dat deze cel gelocked is. Nu zou ik deze formule zelf kunnen door kopieren naar regel 65536, maar dan wordt de excel sheet al direct zonder dat er verdere info in staat al ruim 3 Mb. Dus de meer formules ik er zo inzet, de groter mijn excel bestand wordt. Dit is geen optie.

Hierna heb ik geprobeerd Data>Lijst>Lijst maken hier een lijst van te maken. Daarmee worden namelijk formules door gekopieerd zodra er onderaan een nieuwe regel bij wordt gemaakt. Helaas werkt dat niet zodra ik het blad beveilig. Dan wordt de formule namelijk niet door gekopieerd.
(Let wel, ik heb bij de beveiliging de volgende optie's aangevinkt:
vergendelde en ontgerendelde cellen selekteren
Rijen invoegen en verwijderen
Sorteren
Autofilter gebruiken)

Als laatste heb ik geprobeerd om via een stuk VBA een regel toe te voegen. Zie code:

Private Sub cmdInsert_Click()
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Range("A2:D2").Select
'    Selection.Locked = False
    Selection.AllowEdit = True
    Selection.AllowFormattingCells = True
'    Selection.FormulaHidden = False
    Selection.Font.Bold = False
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-1]="""",TODAY()-RC[-2]+1,RC[-1]-RC[-2]+1))"
    Range("C2").Select
'    Selection.Locked = True
    Selection.AllowEdit = False
'    Selection.FormulaHidden = False
    Range("A1:D3").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

In eerste instantie gebruikte ik de geremarkte regels, maar dat liep vast door de beveiliging. Via het inet had ik gelezen dat allowedit en allowformatingcells wel zou gaan werken, maar helaas.

Wie kan mij helpen een oplossing te vinden.

 

[HSV]

Als je bv. kolom B hebt beveilgd, dus waar de formule staat en verder moet komen.
Bij het invullen van bv kolom A kan het op de volgende manier.

omschrijving.
Als je iets invult in kolom A gaat even de beveiliging eraf; zet de formule in kolom B op dezelfde rij; zet de beveiliging er weer op.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Columns(1)) Is Nothing Then
   ActiveSheet.Unprotect
     Target.Offset(, 1).FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-1]="""",TODAY()-RC[-2]+1,RC[-1]-RC[-2]+1))"
      ActiveSheet.Protect
    End If
End Sub

Even in een testbestandje nabootsen.

 

[obiku]

Harry,

Thanks voor je reply.
Deze werkt grotendeels. Mijn sheet blijft wel protected, maar ik moet wel een keer mijn wachtwoord van mijn sheet invullen.

Voor de rest... Top. Nu nog verder zoeken hoe ik die pop-up voor het invoeren van het wachtwoord kan onderdrukken.

 

[Warme bakkertje]

   ActiveSheet.Unprotect "zet hier je wachtwoord"
------
   ActiveSheet.Protect "zet hier weer je wachtwoord"