Office: (Office Version unbekannt) Excel allgemein

Hallo zusammen,

ich bastel momentan an einer Excel Urlaubsliste für mehrere Personen und habe noch folgende Probleme:

1) Im ersten Reiter (Legende) habe ich eine Namensliste. Wenn dort eine Zelle leer ist soll diese immer automatisch mit N.N. befüllt werden.

2) Wenn sich in den Reitern Jan - Dez ein Name in N.N. ändert (Verknüpfung mit der Legende funktioniert bereits) sollen automatisch die Tage alle mit X belegt werden. (Beispiel: B4:AF4)

3) Wenn sich ein Name ändert, dann sollen automatisch alle Zellen Jan-Dez geleert werden. (Beispiel: B4:AF4)

Ãœber Unterstützung wäre ich sehr Dankbar.

Gruß
Muste85

 

Hallo,

ich habe auch einige Probleme mit deinen Angaben - vorweg:

1. Folgende Makros funktionieren nur dann, wenn die "Verknüpfung" zwischen Namensliste im Blatt "Legende" und den Monatsliste per Formel automatisch erfolgt. Falls die "Verknüpfung" in den Monatsblättern per DropDown/ComboBox mit der Namensliste im Blatt "Legende" erfolgt, kannst du nachfolgende Makros gleich vergessen - sie werden nicht funktionieren.
2. Wenn du ein deutsches (DE-DE) Windows/Excel verwendest, müssen die Namen der Monatsblätter wie folgt lauten:
   Jan,Feb,Mär,Apr,Mai,Jun,Jul,Aug,Sep,Okt,Nov,Dez
   Wenn du ein österreichisches (DE-AT) Windows/Excel verwendest, müssen die Namen der Monatsblätter wie folgt lauten:
   Jän,Feb,Mär,Apr,Mai,Jun,Jul,Aug,Sep,Okt,Nov,Dez (Unterschied beim Januar/Jänner !)

Wenn du möchtest, dass, wenn eine Namenszelle leer wird, diese sofort mit "N.N." befüllt wird, sodass sie damit nicht mehr leer ist, sondern die Zeichenkette "N.N." statt des Namens enthält, dann funktioniert das nur über ein VBA-Makro, indem mittels Change-Event-Behandlungsprozedur im Codemodul des Arbeitsblattes "Legende" dieses "N.N." gespeichert wird:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range([COLOR="#FF0000"]"A1:A30"[/COLOR])) Is Nothing Then
    On Error GoTo Ende_Change

    Application.EnableEvents = False
    If IsEmpty(Target) Then
      'Siehe Punkt 1): Gelöschter Name in der Namensliste: "N.N." speichern.
      Target.Value = "N.N."
    Else
      'Siehe Punkt 3): Geänderter Name in der Namensliste
      '                muss auch in Namensliste der Monats-Arbeitsblätter geändert sein:
      '                Dort in der Namenszeile die Werte löschen
      GeänderterName_WerteLöschen Target.Value
    End If
Ende_Change:
    Application.EnableEvents = True

  End If
End Sub
Sub GeänderterName_WerteLöschen(TargetName As String)
  Dim intMo As Integer, strMo As String, Nr As Byte
  Dim Ws As Worksheet, rgName As Range
  On Error GoTo Err_GeänderterName
  
  For intMo = 1 To 12
    Nr = 1
    strMo = MonthName(Month:=intMo, Abbreviate:=True) 'Erzeuge abgekürzte Monatsnamen
    Set Ws = Worksheets(strMo)
    Nr = 2
    With Ws.Range([COLOR="#FF0000"]"A1:A30"[/COLOR])
      Set rgName = .Find(What:=TargetName, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, Searchdirection:=xlNext)
      If Not rgName Is Nothing Then
        'Name wurde im Monatsblatt gefunden: Die Werte in dieser Zeile löschen
        rgName.EntireRow.Columns("B:AF").ClearContents
      End If
    End With
NxtMo:
  Next intMo
  Exit Sub
  
Err_GeänderterName:
  If Err.Number = 9 Then If Nr = 1 Then Resume NxtMo
  MsgBox "Err=" & Err.Number & " (" & Err.Description & ")" & vbNewLine & _
         "Mo=" & intMo, vbCritical, "Unbehandelter Fehler"
End Sub

Das VBA-Makro nimmt an, dass sich die Namensliste im Zellbereich A1:A30 (rot gekennzeichneter Code) befindet.

In diesem Fall müsstest du ins Codemodul des jeweiligen Monats-Arbeitsblattes ("Jan", "Feb", ..., "Dez") folgendes VBA-Makro aufnehmen:

Code:

Option Explicit

Private Sub Worksheet_Activate()
  Dim rgName As Range
    
  On Error GoTo Ende_SheetAktiv
  Application.EnableEvents = False
  
  For Each rgName In Range([COLOR="#FF0000"]"A1:A30"[/COLOR]).Cells
    If rgName = "N.N." Then
      rgName.EntireRow.Columns("B:AF").Value = "X"
    End If
  Next rgName
Ende_SheetAktiv:
  Application.EnableEvents = True
  
End Sub

Das VBA-Makro nimmt an, dass sich die verknüpfte Namensliste wiederum in A1:A30 (rot gekennzeichnet) befindet. Sobald du auf ein solches Monats-Arbeitsblatt klickst, um es anzuzeigen, wird das Activate-Ereignis dieses Blattes ausgelöst, und das Makro sucht im verknüpften Namenslistenbereich (A1:A30), ob dort ein Namenseintrag "N.N." vorliegt. Falls ja, wird in derjenigen Zeile im Spaltenbereich "B:AF" in allen Zellen ein "X" gespeichert.

Dieses VBA-Makro ist bereits im Makro von Punkt 1) enthalten. Es funktioniert aber nur unter der Voraussetzung, dass die Namensänderung im Blatt "Legende" auch automatisch in den Monatsblättern vollzogen worden ist.

 

Hallo,

vielen Dank erst mal für die schnelle Antwort.

1. Die Verknüpfung von Legende und den Monaten erfolgt folgendermaßen: (=Legende!C9, usw.)

2. Wo sehe ich die welche Version ich habe? Die Monate habe ich wie folgt benannt: Januar, Februar, usw.


1) ja genau das will ich.
habe allerdings schon eine VBA Code in Legende welcher mir Telefonnummern aus einer Liste löscht wenn sich der Name ändert.
Wie kann ich diesen hinzufügen?

Aktueller Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$9" Then Range("M9:M9").ClearContents
If Target.Address = "$C$10" Then Range("M10:M10").ClearContents
If Target.Address = "$C$11" Then Range("M11:M11").ClearContents
If Target.Address = "$C$12" Then Range("M12:M12").ClearContents
If Target.Address = "$C$13" Then Range("M13:M13").ClearContents
If Target.Address = "$C$14" Then Range("M14:M14").ClearContents
If Target.Address = "$C$15" Then Range("M15:M15").ClearContents
If Target.Address = "$C$16" Then Range("M16:M16").ClearContents
If Target.Address = "$C$17" Then Range("M17:M17").ClearContents
If Target.Address = "$C$18" Then Range("M18:M18").ClearContents
If Target.Address = "$C$19" Then Range("M19:M19").ClearContents
If Target.Address = "$C$22" Then Range("M22:M22").ClearContents
If Target.Address = "$C$23" Then Range("M23:M23").ClearContents
If Target.Address = "$C$24" Then Range("M24:M24").ClearContents
If Target.Address = "$C$25" Then Range("M25:M25").ClearContents
If Target.Address = "$C$26" Then Range("M26:M26").ClearContents
If Target.Address = "$F$9" Then Range("P9:P9").ClearContents
If Target.Address = "$F$10" Then Range("P10:P10").ClearContents
If Target.Address = "$F$11" Then Range("P11:P11").ClearContents
If Target.Address = "$F$12" Then Range("P12:P12").ClearContents
If Target.Address = "$F$13" Then Range("P13:P13").ClearContents
If Target.Address = "$F$14" Then Range("P14:P14").ClearContents
If Target.Address = "$F$15" Then Range("P15:P15").ClearContents
If Target.Address = "$F$16" Then Range("P16:P16").ClearContents
If Target.Address = "$F$17" Then Range("P17:P17").ClearContents
If Target.Address = "$F$18" Then Range("P18:P18").ClearContents
If Target.Address = "$F$19" Then Range("P19:P19").ClearContents
If Target.Address = "$F$20" Then Range("P20:P20").ClearContents
If Target.Address = "$F$21" Then Range("P21:P21").ClearContents
If Target.Address = "$F$22" Then Range("P22:P22").ClearContents
If Target.Address = "$F$23" Then Range("P23:P23").ClearContents
If Target.Address = "$F$24" Then Range("P24:P24").ClearContents
If Target.Address = "$F$25" Then Range("P25:P25").ClearContents
If Target.Address = "$F$26" Then Range("P26:P26").ClearContents
If Target.Address = "$F$27" Then Range("P27:P27").ClearContents
If Target.Address = "$F$28" Then Range("P28:P28").ClearContents
If Target.Address = "$F$29" Then Range("P29:P29").ClearContents
If Target.Address = "$F$30" Then Range("P30:P30").ClearContents
If Target.Address = "$F$31" Then Range("P31:P31").ClearContents
If Target.Address = "$I$9" Then Range("S9:S9").ClearContents
If Target.Address = "$I$10" Then Range("S10:S10").ClearContents
If Target.Address = "$I$11" Then Range("S11:S11").ClearContents
If Target.Address = "$I$12" Then Range("S12:S12").ClearContents
If Target.Address = "$I$13" Then Range("S13:S13").ClearContents
If Target.Address = "$I$14" Then Range("S14:S14").ClearContents
If Target.Address = "$I$15" Then Range("S15:S15").ClearContents
If Target.Address = "$I$16" Then Range("S16:S16").ClearContents
If Target.Address = "$I$17" Then Range("S17:S17").ClearContents
If Target.Address = "$I$18" Then Range("S18:S18").ClearContents
If Target.Address = "$I$19" Then Range("S19:S19").ClearContents
If Target.Address = "$I$20" Then Range("S20:S20").ClearContents
If Target.Address = "$I$21" Then Range("S21:S21").ClearContents
If Target.Address = "$I$22" Then Range("S22:S22").ClearContents
If Target.Address = "$I$23" Then Range("S23:S23").ClearContents
If Target.Address = "$I$24" Then Range("S24:S24").ClearContents
If Target.Address = "$I$25" Then Range("S25:S25").ClearContents
If Target.Address = "$I$26" Then Range("S26:S26").ClearContents
If Target.Address = "$I$27" Then Range("S27:S27").ClearContents
If Target.Address = "$I$28" Then Range("S28:S28").ClearContents
If Target.Address = "$I$29" Then Range("S29:S29").ClearContents
If Target.Address = "$I$30" Then Range("S30:S30").ClearContents
If Target.Address = "$I$31" Then Range("S31:S31").ClearContents
End Sub
Sub BereichFuellenUndLoeschen()
Dim Bereich As Range
Set Bereich = Legende.Range("C22:C26")
Bereich.Value = 0
Bereich.ClearContents
End Sub


2) klappt genau wie es soll :) super

Vielen vielen Dank schon mal bis hier hin.

Gruß
Muste85

 

Hallo,

da ich mit deinen einigermaßen geänderten Anforderungen (gegenüber dem 1. Posting) nicht wirklich zurechtkam, habe ich angenommen, dass
1) deine Namensliste im Arbeitsblatt "Legende" sich in den Zellen C9:C26, F9:F31, I9:I31 befindet,
2) eine Telefonnummer sich entsprechend in den Zellen M9:M26, P9:P31, S9:S31 befindet, die dann mit gelöscht wird, sobald ein Name aus 1) gelöscht wird.

Die Monatsarbeitsblätter heißen jetzt (plötzlich) nicht mehr "Jan", "Feb", ..., "Dez" , sondern "Januar", "Februar", ..., "Dezember"
Die restlichen Monatsarbeitsblätter ("März"..."November") musst du durch Kopieren von einem der bereits existierenden Monate einfügen.

 

Entschuldigung für die Verwirrung.

1) Also die Namen in "Legende" stehen in C9:C19, C22:C26, F9:F31, I9:I31
Diese werden dann in "Januar" ... "Dezember" in A4:A14, A16:A20, A22:A44, A46:A68 automatisch übertragen.

2) Die Telefonnummern stehen in "Legende" M9:MC19, M22:M26, P9:P31, S9:S31

Wenn ich den Code bei mir einfüge (angepasst mit C22:C26) bekomme ich den Fehler:

Fehler beim Kompilieren
Mehrdeutiger Name: Worksheet_Change

Womöglich weil ich in Legende schon folgenden Code habe?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$9" Then Range("M9:M9").ClearContents
If Target.Address = "$C$10" Then Range("M10:M10").ClearContents
If Target.Address = "$C$11" Then Range("M11:M11").ClearContents
If Target.Address = "$C$12" Then Range("M12:M12").ClearContents
If Target.Address = "$C$13" Then Range("M13:M13").ClearContents
If Target.Address = "$C$14" Then Range("M14:M14").ClearContents
If Target.Address = "$C$15" Then Range("M15:M15").ClearContents
If Target.Address = "$C$16" Then Range("M16:M16").ClearContents
If Target.Address = "$C$17" Then Range("M17:M17").ClearContents
If Target.Address = "$C$18" Then Range("M18:M18").ClearContents
If Target.Address = "$C$19" Then Range("M19:M19").ClearContents
If Target.Address = "$C$22" Then Range("M22:M22").ClearContents
If Target.Address = "$C$23" Then Range("M23:M23").ClearContents
If Target.Address = "$C$24" Then Range("M24:M24").ClearContents
If Target.Address = "$C$25" Then Range("M25:M25").ClearContents
If Target.Address = "$C$26" Then Range("M26:M26").ClearContents
If Target.Address = "$F$9" Then Range("P9:P9").ClearContents
If Target.Address = "$F$10" Then Range("P10:P10").ClearContents
If Target.Address = "$F$11" Then Range("P11:P11").ClearContents
If Target.Address = "$F$12" Then Range("P12:P12").ClearContents
If Target.Address = "$F$13" Then Range("P13:P13").ClearContents
If Target.Address = "$F$14" Then Range("P14:P14").ClearContents
If Target.Address = "$F$15" Then Range("P15:P15").ClearContents
If Target.Address = "$F$16" Then Range("P16:P16").ClearContents
If Target.Address = "$F$17" Then Range("P17:P17").ClearContents
If Target.Address = "$F$18" Then Range("P18:P18").ClearContents
If Target.Address = "$F$19" Then Range("P19:P19").ClearContents
If Target.Address = "$F$20" Then Range("P20:P20").ClearContents
If Target.Address = "$F$21" Then Range("P21:P21").ClearContents
If Target.Address = "$F$22" Then Range("P22:P22").ClearContents
If Target.Address = "$F$23" Then Range("P23:P23").ClearContents
If Target.Address = "$F$24" Then Range("P24:P24").ClearContents
If Target.Address = "$F$25" Then Range("P25:P25").ClearContents
If Target.Address = "$F$26" Then Range("P26:P26").ClearContents
If Target.Address = "$F$27" Then Range("P27:P27").ClearContents
If Target.Address = "$F$28" Then Range("P28:P28").ClearContents
If Target.Address = "$F$29" Then Range("P29:P29").ClearContents
If Target.Address = "$F$30" Then Range("P30:P30").ClearContents
If Target.Address = "$F$31" Then Range("P31:P31").ClearContents
If Target.Address = "$I$9" Then Range("S9:S9").ClearContents
If Target.Address = "$I$10" Then Range("S10:S10").ClearContents
If Target.Address = "$I$11" Then Range("S11:S11").ClearContents
If Target.Address = "$I$12" Then Range("S12:S12").ClearContents
If Target.Address = "$I$13" Then Range("S13:S13").ClearContents
If Target.Address = "$I$14" Then Range("S14:S14").ClearContents
If Target.Address = "$I$15" Then Range("S15:S15").ClearContents
If Target.Address = "$I$16" Then Range("S16:S16").ClearContents
If Target.Address = "$I$17" Then Range("S17:S17").ClearContents
If Target.Address = "$I$18" Then Range("S18:S18").ClearContents
If Target.Address = "$I$19" Then Range("S19:S19").ClearContents
If Target.Address = "$I$20" Then Range("S20:S20").ClearContents
If Target.Address = "$I$21" Then Range("S21:S21").ClearContents
If Target.Address = "$I$22" Then Range("S22:S22").ClearContents
If Target.Address = "$I$23" Then Range("S23:S23").ClearContents
If Target.Address = "$I$24" Then Range("S24:S24").ClearContents
If Target.Address = "$I$25" Then Range("S25:S25").ClearContents
If Target.Address = "$I$26" Then Range("S26:S26").ClearContents
If Target.Address = "$I$27" Then Range("S27:S27").ClearContents
If Target.Address = "$I$28" Then Range("S28:S28").ClearContents
If Target.Address = "$I$29" Then Range("S29:S29").ClearContents
If Target.Address = "$I$30" Then Range("S30:S30").ClearContents
If Target.Address = "$I$31" Then Range("S31:S31").ClearContents
End Sub
Sub BereichFuellenUndLoeschen()
Dim Bereich As Range
Set Bereich = Legende.Range("C22:C26")
Bereich.Value = 0
Bereich.ClearContents
End Sub

Gruß
Stefan

 

Jetzt fehlt eben nur noch dass, leere Namensfelder in "Legende" mit N.N. belegt werden.

Und Bei Namensänderung die belegten Tage in "Januar" bis "Dezember" gelöscht werden. (Welche bei N.N. ja schon mit X vorbelegt werden)

Vielen Dank im Voraus

 

Hallo,

• In der beiliegenden Excel-Datei ist ein "Legende"-Blatt und 3 Monatsblätter ("Januar", "Februar", "Dezember") enthalten. Jedes dieser 4 Blätter hat ein VBA-Code-Modul, das für die gewünschte Funktionalität verantwortlich ist. Weiters gibt es ein allgem. Codemodul "Modul1_Konstante", in dem alle wichtigen Konstanten definiert sind, die zum korrekten Ablauf nach deinen Wünschen nötig sind.
  .
• Das Blatt "Legende" hat die 4 Namenslisten-Bereiche (wie von dir gewünscht) und dazu passend, jeweils um 10 Spalten nach rechts versetzt, die 4 Telefonnummern-Bereiche.
  .
• Wird in den Namenslisten-Bereichen ein Name gelöscht, wird statt dessen ein "N.N." eingetragen; ferner wird auch der dazu gehörige Telefonnummern-Eintrag gelöscht.
  .
• Wird in den Namenslisten-Bereichen ein Name geändert, wird die Verknüpfung zu den Monatsarbeitsblättern aktualisiert und dort in der Zeile des geänderten Namens die Werte der Tagesspalten gelöscht.
  .
• Wenn du in ein Monatsarbeitsblatt umschaltest, wird beim Umschalten geprüft, ob im betreffenden Arbeitsblatt Namen mit "N.N." enthalten sind: Falls ja, werden in dieser Zeile die Tagesspalten mit "X" befüllt.
  .
• Weiters dürfte auch klar sein, dass man in einem Codemodul nicht 2 Prozeduren mit identischen Namen einfügen darf. Für welche von beiden sollte sich denn auch VBA dann entscheiden? Das alles, was im vorgenannten Worksheet_Change(...) enthalten ist, ist ersatzlos zu streichen. Es ist in meinen Makros mit einer einzigen Zeile Code enthalten (Target.Offset(0, 10).ClearContents).

 

Hallo,

vielen vielen Dank. Funktioniert genau so wie ich mir das vorgestellt habe. TOP
Eine kleine Optimierung bräuchte ich noch :)

In den Reitern "Januar" bis "Dezember" sollen die Eingaben in den Zellen B4:AF68 immer automatisch Großgeschrieben werden.

Vielen Dank nochmal

Gruß
Muste85

 

Leider gibt es doch noch ein Problem... wenn ich einen Namen ändere kommt folgender Fehler:

Err=1004 (Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden.)
Mo=1

 

Hallo,

bei mir funktionieren die Makros einwandfrei. Ich habe, wie du siehst, Excel2016. Da du großzügig darauf verzichtest hast anzugeben, welche Excel-Version du hast, bin ich von meiner Version ausgegangen - schließlich kann ich ja nicht wissen, dass du offenbar eine veraltete Version besitzt.

Ich habe jetzt eine Konversionsfunktion eingefügt, die die A1-Bezüge in R1C1-Bezüge umwandelt vor der Übergabe an eine Array/Matrix-Formel.
Hoffentlich ist dein Excel nicht auch für diese Funktion zu veraltet.

 

Hallo,

leider immer noch die Fehlermeldung.
Ich hatte nicht gefunden wo die Version steht... Es ist Microsoft Office Professional Plus 2013

Gruß
Muste85

 

Hallo,

funktioniert es bei Excel 2013 auch oder brauche ich hier eine andere Lösung?

Gruß
Muste85

 

Was mir gerade noch aufgefallen ist:
Obwohl ich alle Codes 1:1 übernommen habe, bekomme ich bei der von Ihnen erstellten Test Excel die Fehlermeldung nicht.
Das löschen der gesetzten X funktioniert hier allerdings auch nicht.

Und in Ihrer Test Excel ist "Jänner" angegeben, bei mir "Januar". (Das umbenennen hat aber auch keine Auswirkungen auf die Fehler gehabt)

Gruß
Muste85

 

Hallo,

du hattest mir in deinem Posting #5 vom 10.11.2020 folgende Zellbereiche mitgeteilt:

Bei mir lief das Makro ordnungsgemäß, denn ich habe mich exakt an deine Vorgaben gehalten.
Da du aber offensichtlich diese Zellbereiche im Nachhinein wieder abgeändert hast, trat das folgende Problem auf:

Das konnte ich jetzt endgültig aufgrund meiner Tests klären. Das rührt daher, dass man Bereiche, in denen Matrixfunktionen gespeichert sind, nicht durch überlappende Bereiche mit Formeln überspeichern kann.
In der beiliegenden Excel-Datei habe ich daher die automatische Verknüpfung der Monatsblätter in der Spalte A mit den Bereichen des Blattes "Legende" als Kommentar deaktiviert und damit unwirksam gemacht (unten rot gekennzeichnet). Das bedeutet für dich, dass du diese Verknüpfung in den Monatsblättern selber händisch erstellen musst, sobald du ein neues Monatsarbeitsblatt anlegst.

Code:

Sub GeänderterName_WerteLöschen(TargetName As String)
  Dim intMo As Integer, strMo As String, bNr As Byte
  Dim Ws As Worksheet
  On Error GoTo Err_GeänderterName
  
  For intMo = 1 To 12  'alle Monate
    bNr = 1
    strMo = MonthName(Month:=intMo, Abbreviate:=False) 'Erzeuge normal langen Monatsnamen
    Set Ws = Worksheets(strMo)
    bNr = 2
 [COLOR="#FF0000"] ' VerknüpfungLegendeMonat_Eintragen Ws[/COLOR]
    GeänderterName_SuchenLöschen Ws, TargetName
NxtMo:
  Next intMo
  Exit Sub
  
Err_GeänderterName:
  If Err.Number = 9 Then If bNr = 1 Then Resume NxtMo 'Monatsblatt existiert nicht -> nächstes
  MsgBox "Err=" & Err.Number & " (" & Err.Description & ")" & vbNewLine & _
         "Mo=" & intMo, vbCritical, "Unbehandelter Fehler"
End Sub

 

Hallo,

die Zellenbereiche sind noch immer so wie damals angegeben. Hier hat sich nichts geändert.

der geschickte Code ist doch bereits 1:1 der den ich schon habe...

Kann ich meine Excel hier irgendwie mit anhängen? Wäre warscheinlich leichter für uns beide.

Gruß
Muste85

P.S.: deine Test Excel funktioniert bei mir auch nicht