Office: Funktionen im Datenblatt

Guten Morgen zusammen,
bei der Erstellung eines (neuen) Dienstplanes bin ich auf einige Frage gestossen und hoffe das mir vllt der ein oder andere weiterhelfen kann :)
Unsere Version: Office 2013 (2015.2 (2019?) serverseitig; active directory)

Frage 1:
Ich würde gerne bei den Kommentaren die entsprechenden Namen stehen haben.
Allerdings ist es derzeit so das jedesmal wenn ich ein Kommentar schreibe, erst mein Name steht und sobald ich alles beende bzw erneut den Kommentar aufrufe, steht nun immer Autor als Nutzer.
Ich habe schon versucht mich selber als Autor im Startbereich der Tabelle einzutragen.
Ich kann mich auswählen und einfügen, aber sobald ich Speicher - bin ich wieder raus und es ist kein Autor eingetragen.

Frage 2: Kann ich auch einzelne Felder für bestimmte Nutzer freigeben?
Die Idee dahinter ist das 2 Leute ihre Markierung setzen (evtl der Name oder ein X) - das ganze soll aber natürlich nur von der entsprechenden Person bzw vom entsprechendem Account (active directory) möglich sein (ohne das der Schutz aufgehoben werden muss).

Frage 3: Ist es möglich einzelne Spalten / Zeilen zu sperren und gleichzeitig "am Ende der Kontrolle" das ganze Datenblatt? ohne das dann der Schutz per Passwort aufgehoben und wieder erneut gesetzt werden muss?

Frage 4: Wenn ich ein Datenblatt aufrufe, kann man sich dort evtl automatisch anzeigen lassen wer wann was als letztes geändert hat? zB ab einem bestimmten Datum?

 

zu 2
bearbeiten, Schützen, Berabeiten von Bereichen zulassen.
zu 4
Ja
Die Informationen dazu sind ja in Deiner Spalt IV und IB

 

Grüsse zusammen,
erst einmal danke für die Antwort (auch in dem anderen Thread).
Ich bin schon etwas weiter gekommen und habe einige offene Punkte abarbeiten können.

Was für mich noch offen ist:
Mitarbeiter scheidet aus und ein neuer Mitarbeiter kommt. Ich habe eine Zelle in der der Name eines Mitarbeiters steht (zu dem Mitarbeiter gehören insgesamt 4 Zeilen und ca 15 Spalten).
Nun soll der Mitarbeitername (eine Zelle) geändert werden - Ich kann die Zellen zwar ausblenden, was meine erste Idee war - bekomme nur dann keinen neuen Mitarbeiter rein (das Excel Dokument besteht aus 52 Datenblättern (die Kalenderwochen).
Ich muss nun also den Bezug von jedem Datenblatt auf das vorherige setzen und muss dieses nun ändern (weil es beim kopieren 1:1 übernommen wird).
Gibt es dafür evtl ein Makro (das aus dem Bezug / der Formel kw3!b5 ein kw4!b5 macht und beim nächsten datenblatt aus kw4!b5 ein kw5!b5)?

Zum Verständnis für mich (ob sowas per Makro automatisch machbar ist):
- Kann ein "Button" für den Druckbereich per Makro erstellt werden, wo festgelegt ist das er von A-X und 2-5 den Bereich drucken soll? Das es nicht mehr manuell ausgewählt werden muss?
- Ich möchte für Überstunden ein Feld festlegen was, je nach Zahlenwert, grün gelb oder rot ist. Diese Automation geht leider nicht, korrekt? ich müsste wenn dann ein Makro starten und jedes mal ausführen. Richtig?

 

Anbei noch eine Beispiel Datei bzgl der "noch offenen" Frage.

 

Hallo,

in der beiliegenden Excel-Datei habe ich dir im allgemeinen Codemodul "Modul1" 2 Makros (+1 Funktion) eingefügt, die mit den beiden Buttons im Arbeitsblatt "kw3" verknüpft sind.

Klickt man auf Button "Neues "kw"-Blatt anfügen" im Arbeitsblatt "kw3" wird dieses in das Arbeitsblatt "kw4" kopiert und der Formelbezug in Zelle A2 umgestellt von "='kw2'!A2" auf "='kw3'!A2". Das wird mit allen Formeln gemacht, die einen Blattbezug auf das Vorblatt haben.
Dieser Mechanismus kann aber frühestens im Blatt "kw2" funktionieren, wenn dieses nach "kw3" kopiert wird. Denn im Blatt "kw1" sind keine Formeln enthalten, die Bezug nehmen können auf ein Vorblatt.

Der 2. Button "Druckbereich A2:X5 festlegen" besagt bereits alles, was er tut.

Nachfolgend habe ich die Makros nochmals gelistet:

Code:

Option Explicit

Sub NeuesBlatt_Anfuegen()
  Dim WsAlt As Worksheet, WsNeu As Worksheet
  Dim strNameAlt As String, strNameNeu As String
  Dim rgFormelZelle As Range, strFormel As String, Bl$(), BlI As Integer
 
  Set WsAlt = ActiveSheet
  strNameAlt = WsAlt.Name
  strNameNeu = "kw" & (Mid$(strNameAlt, 3) + 1)
 
  WsAlt.Copy After:=WsAlt
  Set WsNeu = Worksheets(Worksheets.Count)
  With WsNeu
     strNameAlt = WsAlt.Name
     .Name = strNameNeu
    
     'Ersetze in den Formeln alle Bezüge auf den Vorvorblattnamen auf den Vorblattnamen(=strNameAlt):
'--> 'Das kann nur dann funktionieren, wenn bereits im WsAlt-Blatt Bezüge auf das VorBlatt enthalten waren!!
    
     With .UsedRange.SpecialCells(xlCellTypeFormulas)
    
        For Each rgFormelZelle In .Areas
          strFormel = rgFormelZelle.Formula
          'Testen, ob es sich tatsächlich bei allen entdeckten Stellen um den strNameAlt-Blattnamen handelt:
          rgFormelZelle.Formula = Formel_BlattAendern(strFormel, strNameAlt)
        Next rgFormelZelle
      
     End With
  End With
End Sub
Function Formel_BlattAendern(strFormel As String, BlattAltName As String) As String
   Dim VorBlattAltName As String
   Dim Bl() As String, BlI As Integer
  
   VorBlattAltName = "kw" & (Mid$(BlattAltName, 3) - 1)
   Bl$ = Split(strFormel, VorBlattAltName)
   strFormel = Bl$(0)
   For BlI = 1 To UBound(Bl$)
     If Bl$(BlI) Like "[0-9_A-Za-z]*" Then
       'Es liegt ein anderer Bezeichner vor, der mit dem VorBlattAltName beginnt
       'Keine Blattnamensersetzung!
     Else
       'Es handelt sich um den VorBlattAltName-Blattnamen.
       'Ersetze ihn durch den BlattAltName:
       strFormel = strFormel & BlattAltName & Bl$(BlI)
     End If
   Next BlI
   Formel_BlattAendern = strFormel
End Function
Sub Druckbereich_festlegen()
    ActiveSheet.PageSetup.PrintArea = "$A$2:$X$5"
End Sub

 

Das werde ich nächste Woche direkt mal einbinden und testen.

Vielen vielen Dank dir dafür :)

 

Hallo,

ich habe die enthaltenen Makros nochmals ausführlich getestet und festgestellt, dass ein paar Fehler enthalten waren, die unter bestimmten Umständen zu Tage treten. Ich hoffe, ich habe jetzt alle Fehler ausgemerzt.
Die korrigierte und in einigen Details verbesserte Datei liegt bei. Der enthaltene Makrocode:

Code:

Option Explicit

Sub NeuesBlatt_Anfuegen()
  Dim WsAlt As Worksheet
  Dim strNameAlt As String, strNameNeu As String
  Dim rgFormelZelle As Range, strFormel As String
 
  Set WsAlt = GetLetztesKwBlatt()
  If WsAlt Is Nothing Then MsgBox "Kein 'kw<nn>'-Blatt": Exit Sub  'Falls kein "kw<NN>"-Blatt vorhanden ist
 
  With WsAlt
     strNameAlt = .Name
     strNameNeu = "kw" & (Mid$(strNameAlt, 3) + 1)
     .Copy After:=Worksheets(Worksheets.Count)
  End With
 
  With Worksheets(Worksheets.Count)
     .Name = strNameNeu
    
     'Ersetze in den Formeln alle Bezüge auf den Vorvorblattnamen auf den Vorblattnamen(=strNameAlt):
'--> 'Das kann nur dann funktionieren, wenn bereits im WsAlt-Blatt Bezüge auf das VorBlatt enthalten waren!!
    
     With .UsedRange.SpecialCells(xlCellTypeFormulas)
    
        For Each rgFormelZelle In .Cells
          strFormel = rgFormelZelle.Formula
          'Testen, ob es sich tatsächlich bei allen entdeckten Stellen um den strNameAlt-Blattnamen handelt:
          rgFormelZelle.Formula = Formel_BlattAendern(strFormel, strNameAlt)
        Next rgFormelZelle
      
     End With
  End With
End Sub
Function GetLetztesKwBlatt() As Worksheet
  Dim Ws As Worksheet
  Dim Nr As Integer, NrL As Integer
 
  On Error GoTo Err_KwBlatt
  Set GetLetztesKwBlatt = Nothing: NrL = 0
  For Each Ws In Worksheets
    If Ws.Name Like "kw[0-9]*" Then
      Nr = Mid$(Ws.Name, 3)  '<-- Error abfangen, falls nicht numerisch
      If Nr > NrL Then NrL = Nr: Set GetLetztesKwBlatt = Ws
    End If
Nxt_KwBlatt:
  Next Ws
  Exit Function
Err_KwBlatt:
  Resume Nxt_KwBlatt
End Function
Function Formel_BlattAendern(strFormel As String, BlattAltName As String) As String
   Dim VorBlattAltName As String
   Dim Bl() As String, BlI As Integer
  
   VorBlattAltName = "kw" & (Mid$(BlattAltName, 3) - 1)
   Bl = Split(strFormel, VorBlattAltName)
   strFormel = Bl(0)
   For BlI = 1 To UBound(Bl$)
     If Bl$(BlI) Like "[0-9_A-Za-z]*" Then
       'Es liegt ein anderer Bezeichner vor,
       'der zwar mit dem VorBlattAltName beginnt, aber noch weitere Zeichen enthält:
       'Keine Blattnamensersetzung!
       strFormel = strFormel & VorBlattAltName
     Else
       'Es handelt sich um den VorBlattAltName-Blattnamen.
       'Ersetze ihn durch den BlattAltName:
       strFormel = strFormel & BlattAltName
     End If
     strFormel = strFormel & Bl$(BlI)
   Next BlI
   Formel_BlattAendern = strFormel
End Function
Sub Druckbereich_festlegen()
    ActiveSheet.PageSetup.PrintArea = "$A$2:$X$5"
End Sub

 

Grüsse Anton,
vielen Dank erst einmal für die Anpassung :)
Ich habe meine Probedatei genommen und einfach untereinander weitere Namen geschrieben, welcher er dann ohne Probleme mit dem Makro übernommen hat.
Nun habe ich das Makro bei mir eingefügt 1:1 und auch die 2 Boxen.
Ich erhalte nun eine Fehlermeldung "Laufzeitfehler 9" - Index ausserhalb des gültigen Bereiches.
Wende ich den debugger an zeigt er die Zeile "strFormel = Bl(0)" an.
Kann ich dies evtl. eigenständig lösen oder erweitern? oder auf noch etwas achten?

Vielen Dank schon einmal vorab :)

 

Hallo,

1) ich nehme an, dass du die enthaltenen Makros markiert, kopiert und in der neuen Excel-Datei wieder in einem allgemeinen Codemodul (im Projektexplorer unterhalb von "Module") eingefügt hast.
(Bei der alternativen Methode, dem händischen Abtippen, kann sich nämlich leicht irgendwo ein Fehler einschleichen).
2) hast du eh die neueren Makros, die ich dir im Posting #7 vom 29.5. übermittelte, genommen. Die Makros im Posting #5 vom 26.5. enthalten nämlich Fehler, die unter bestimmten Umständen zuschlagen können. Sie haben allerdings nichts mit dem im Folgenden geschilderten Problem zu tun.

Zu einem Laufzeitfehler "Index außerhalb ..." kann es an dieser Stelle nur kommen, wenn der Index 0 im Array Bl(...) nicht vorkommen darf, weil die dazugehörige Deklaration im Dim-Statement anders lautet.
Dieses Dim-Statement lautet (innerhalb der Funktion "Formel_BlattAendern"):
Dim VorBlattAltName As String
Dim Bl() As String, BlI As Integer
Damit wird eine dynamische Array-Variable "Bl" definiert, die String-Werte aufnehmen darf. "Dynamisch" bedeutet dabei, dass die Anzahl der String-Werte, die sie enthalten kann, zum Zeitpunkt der Dim-Deklaration noch nicht feststeht (deshalb das leere Klammernpaar).
Diese Anzahl wird in der folgenden Zuweisung (im Makro 2 Zeilen später) festgelegt:
Bl = Split(strFormel, VorBlattAltName)
Die Split-Funktion macht dabei Folgendes: Sie nimmt den String, der in der Variablen "strFormel" enthalten ist, und schneidet (=splittet) alle Teilstücke heraus, an denen der Inhalt der Stringvariablen "VorBlattAltName" gefunden wurde. Die so verbleibenden Reststücke werden durchnummeriert, wobei mit der Nummerierung bei 0 begonnen wird.
Angenommen: In "strFormel" ist folgender Formelstring enthalten: "='kw4'!A2" und in "VorBlattAltName" folgender String-Wert: "kw4". Dann weist vorgenannte Split-Formel der Array-Variablen Bl() folgende Werte zu: Bl(0)="='"; Bl(1)="'!A2"
Daraus folgt aber, dass es stets ein String-Array-Element "Bl(0)" muss.

 

Guten Abend,
ich habe in der Tat beides probiert.
Einmal natürlich die Formel kopiert und unter Modul eingefügt, korrekt :)
Auch habe ich die neuere Version von dir genommen, vielen Dank :)
Ebenfalls habe ich versucht einfach die Beispieldatei zu nehmen und dort den Aufbau zu kopieren.
Das habe ich allerdings von zuhause aus gemacht, daher hat er die Formeln nicht übernommen.
Ich habe einfach die ersten beiden Zeilen eingetragen (alles andere gelöscht).
Die erste Zeile enthält eine kwx!xx Formel - welche angepasst wird.
Ab der 2. Zeile klappt dies allerdings nicht mehr - obwohl da auch der gleiche Wert (bzw die gleiche Formel nur mit einer anderen Zelle) enthalten ist.
Beim Rest des Blattes ist alles "leer" an Formeln.
Soweit ich den letzten Teil von dir verstanden habe (sorry ich konnte da nicht alles verstehen) macht er aber genau das - es soll alles erhalten bleiben (in deinem Beispiel = !A2). nur der kwX soll angepasst werden.
Ich dachte erst es gibt Probleme weil ich halt mehrere (als Beispiel) kw4!yx drin habe; aber in der Beispieldatei - wenn ich da untereinander mehrere nehme - klappt es ohne Probleme.

Kann ich dich evtl in einem Discord antreffen, wo ich dir ggf das Dokument einmal zeigen kann?
Vielleicht kann ich dir das dann einfach besser beschreiben bzw zeigen.
Ansonsten würde ich sonst die Beispieldatei einfach mal soweit erweitern oder "leer" machen, das der Fehler auftaucht und ich die Datei auch öffentlich zeigen kann.

Ansich kann ich auch kein Problem erkennen und bin sehr dankbar das du dich dem Problem hier weiter annimmst.
Vielen vielen Dank dafür.

 

Hallo,

darum möchte ich dich bitten - denn ich kann mir dein Problem nicht erklären.

 

Grüsse,
anbei die entsprechende Datei.
Ich habe mal die Formeln mit =kw!xy blau im Hintergrund gemacht, sowie Zellen in denen das Wort KW auftaucht rot).
Nach unten hin geht die Tabelle immer gleich weiter.

Vielen Dank.

 

Hallo,

ich habe die Datei getestet. Resultat: Excel weist bei Verbundzellen mit Formeln nicht bloß die 1.Zelle als Formelzelle aus, sondern auch die restlichen Zellen des Verbundes, in denen gar keine Formel enthalten ist.
So geschehen zB. im Zellbereich M2:O2. Dieser Zellverbund enthält die Formel "='kw3'!M2+7". Diese Formel ist aber tatsächlich nur in Zelle M2 enthalten und nicht auch in N2 und O2. In N2 und O2 ist tatsächlich überhaupt keine Formel enthalten, obwohl sie in der Liste der Formelzellen geführt werden.
Die beiden blauen Makrozeilen habe ich hinzugefügt, damit Zellen, die zwar in der Formelliste enthalten sind, aber keine Formel enthalten, übergangen werden:

strFormel = rgFormelZelle.Formula
'Testen, ob es sich tatsächlich bei allen entdeckten Stellen um den strNameAlt-Blattnamen handelt:
If Len(strFormel) Then
rgFormelZelle.Formula = Formel_BlattAendern(strFormel, strNameAlt)
End If

Damit müsste das Makro jetzt funktionieren.
Was wieder einmal mehr beweist: Finger weg von Verbundzellen, sie verursachen nur Ärger.

In der beiliegenden Datei habe ich bereits das Makro um das IF-Statement (blaue Makrozeilen) ergänzt.

 

Grüsse,
perfekt das passt genau so :)

Vielen vielen Dank dafür!

 

Grüsse Exl121150,

darf ich mich noch einmal erneut an dich wenden?
Bei uns gibt es leider eine Umstellung und diese wirkt sich leider auch auf die Dienstpläne der xls Tabelle aus.
Ich würde mir damit behelfen aus einer Datei, welche auf einem Netzwerklaufwerk liegt, per Formel, die Daten einzutragen.

Als Beispiel: ='Z:\Wochenplan\[Wochenplan KW 23.xlsx]Sheet1'!$C$12

Nun soll, wie bei der Formel die du mir schon bereits erstellt hast, die Zahl "Wochenplan KW nn" ebenfalls um +1 steigen.
Ich habe schon versucht selber die Formel zu kopieren und anzupassen, aber das hat leider nicht geklappt

Daher meine Frage ob du mir hier erneut helfen kannst (oder jemand anderes :)).

Vielen Lieben Dank schon einmal vorab.