761 Aufrufe
Gefragt in Tabellenkalkulation von robbi58 Mitglied (966 Punkte)

Liebes Forum!

Dank eurer Hilfe habe ich mittlerweile viele Arbeitsblätter für meine SchülerInnen fertiggestellt. Doch beim Arbeiten stößt man immer wieder auf Probleme, an die man im Vorfeld gar nicht gedacht hatte.

Mein Problem ist folgendes : 

In den Spalten A bis J befindet sich meine "Datenbank", mit der ich meine Aufgaben in den Spalten M bis ZZ gestalte. Die Zellen in den Spalten A bis J sind nicht gesperrt (da diese mit dem Drücken eines CommandButtons neu definiert werden), die Zellen ab den Spalten M sind größtenteils gesperrt. In diesem Bereich aber befinden sich zahlreiche Zellen, die zur Bearbeitung freigegeben werden. Bis jetzt habe ich diese Zellen per Hand in das Makro geschrieben, da ich diese nach Beendigung leeren möchte (bei bis zu 70 Zellen ist das sehr mühsam).

Meine Frage lautet: Gibt es eine Möglichkeit, so wie hier an diesem Beispiel, alle nicht gesperrte Zellen einer ganzen Arbeitsmappe ab der Spalte M (dies kann ich ja dann anpassen) per CommandButton zu leeren und wenn möglich, gleichzeitig einzufärben. Das wäre eine enorme Arbeitserleichterung und würde mir bei der Fehlersuche äußerst hilfreich sein.

Danke und ein angenehmes Wochenende von Robert

16 Antworten

+1 Punkt
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Robert,

das kannst du z.B. so machen:

Sub leeren_faerben()
Dim rngZelle As Range

For Each rngZelle In Range("M1:ZZ100")
 If rngZelle.Locked = False Then
   rngZelle.ClearContents
   rngZelle.Interior.ColorIndex = 3
 End If
Next rngZelle

End Sub

Gruß

M.O.

0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Hallo M.O.

Zunächst danke für deine Antwort. Leider funktioniert dein Code nicht so wie gewünscht. Auf dem Blatt Level 1 werden ein paar Zellen gelöscht und eingefärbt, der Großteil bleibt unverändert. Auf den Blättern Level 2 und Level 3 passiert gar nichts (aber vielleicht ist der Code nur auf ein Arbeitsblatt ausgerichtet).

Zum besseren Verständnis und um lange Erklärungen zu vermeiden, lade ich die Datei mal hoch.

https://filehorst.de/d/cppAAjqD

Robert
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Bearbeitet von m-o
Hallo Robert,

das Makro war als Beispiel gedacht und funktioniert nur auf dem Blatt, aus dem es gestartet wird.

Außerdem hatte ich nicht bedacht, dass du auch verbundene Zellen in deinen Arbeitsblättern hattest (da gibt es mit dem Löschen ein Problem). Ich muss das erst mal an deinem Beispiel testen.

Gruß

M.O.
0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)

Hallo M.O.

Normalerweise versuche ich, die von dir geposteten Lösungen nachzuvollziehen. Du fügst ja dankenswerterweise auch Kommentare/Beschreibungen zu, die es mir erlauben, den Code besser zu verstehen. Manchmal ändere ich auch Kleinigkeiten ab, um zu sehen, welche Auswirkungen dies hat (learning by doing). Aber ich muss ehrlicherweise zugeben, dass ich nicht immer alles checke. 

Und ich habe mir schon gedacht, dass verbundene Zelle Schwierigkeiten bereiten. Das merke ich schon, wenn ich versuche, Formeln in diese hinein zu kopieren. Da erscheinen Fehlermeldungen, die ich so umgehe, dass ich die Formel in Word kopiere und von dort übertrage. Und plötzlich funktioniert es. Warum, dass weiß nur der liebe Gott oder vielleicht M.O. winkangel

Darum möchte ich mich an dieser Stelle recht herzlich bei dir für deine immense Hilfsbereitschaft und deine Ausdauer bedanken. Wie in der Schule: man braucht sehr viel Geduld mit Schützlingen, die überhaupt nichts kapieren (oder kapieren wollen).

Danke Robert

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Bearbeitet von m-o

Robert,

danke für dein Vertrauen. ich weiß zwar viel, aber nicht alleswink. Ich versehe den Code mit Kommentaren einmal um dem Fragesteller zu helfen und zum anderen auch mir, wenn ich mal nach Jahren nochmal mit dem Code zu tun habe laugh.

Ich habe jetzt mal einen Code für deine Beispieldatei geschrieben, der zu 95% funktioniert. Aber ein paar Zellen werden nicht gelöscht. Da bin ich noch auf der Fehlersuche. Heute komme ich aber leider nicht mehr dazu, weiter zu testen.

Gruß

M.O.

+1 Punkt
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Robert,

so hier ist das Makro zum Löschen und Einfärben der nicht gesperrten Zellen auf allen Level-Arbeitersblättern (hier 1 bis 3):

Sub leeren_faerben()
Dim rngZelle As Range
Dim rngStartZelle As Range 'erste Zelle des verbundenen Bereichs
Dim rngEndZelle As Range   'letzte Zelle des verbundenen Bereich
Dim rngBereich As Range    'Bereich der gelöscht werden soll
Dim strLevel As String
Dim lngASpalte As Long      '1. Spalte des Bereichs, der gelöscht werden soll
Dim lngESpalte As Long      'letzteSpalte des Bereichs der gelöscht werden soll
Dim lngLZeile As Long       'letzte Zeile, die gelöscht werden soll
Dim i As Long
Dim w As Long

'Level-Blätter durchlaufen
For w = 1 To 3
 strLevel = "Level" & w       'Namen des Blatts wird erstellt
 'Variablen für Spalten werden zurückgesetzt
 lngASpalte = 0
 lngESpalte = 0
 i = 0
 'der zu durchlaufende Bereich wird ermittelt
 With Worksheets(strLevel)
  'hier wird festgestellt, wo die erste sichtbare Spalte ist
  Do
    i = i + 1
  Loop Until .Columns(i).Hidden = False
  lngASpalte = i
  'nun die letzte beschriebene Spalte und Zeile ermitteln
  For i = lngASpalte To .UsedRange.SpecialCells(xlCellTypeLastCell).Column
    If Application.WorksheetFunction.CountA(.Columns(i)) > 0 Then
       If lngESpalte < i Then lngESpalte = i
       If .Cells(Rows.Count, i).End(xlUp).Row > lngLZeile Then lngLZeile = .Cells(Rows.Count, i).End(xlUp).Row
    End If
  Next i
  'nun Bereich definieren
  Set rngBereich = .Range(.Cells(1, lngASpalte), .Cells(lngLZeile, lngESpalte))
 
 End With
 
    For Each rngZelle In rngBereich
      With rngZelle
          If .Locked = False Then         'prüfen, ob nicht gesperrt
                If .MergeCells Then           'prüfen, ob Zelle verbunden
                  'Inhalt der verbundenen Zelle löschen
                   Set rngStartZelle = .MergeArea(1)
                   Set rngEndZelle = .MergeArea(.MergeArea.Cells.Count)
                   Worksheets(strLevel).Range(rngStartZelle.Address, rngEndZelle.Address).ClearContents
                   Set rngStartZelle = Nothing
                   Set rngEndZelle = Nothing
               Else
                   rngZelle.ClearContents    'Inhalt der nicht verbundenen Zellen löschen
               End If
              rngZelle.Interior.ColorIndex = 6              'Einfärben
          End If
      End With
   Next rngZelle
 Set rngBereich = Nothing
 
Next w
End Sub

Kopiere das Makro in ein Standardmodul deiner Arbeitsmappe.

Gruß

M.O.

0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Bearbeitet von robbi58

Hallo M.O.

Vielen Dank für deine Arbeit. Das hast du ja einen ganzen "Roman" verfasst. Ich habe das Makro im vorgegebenen Beispiel ausprobiert und es hat perfekt funktioniert.

Sobald ich aber dieses bei  anderen, ähnlich aufgebauten Beispielen versuche, funktioniert es leider nicht. Ich bekomme folgende "Fehlermeldungen":

1. Ich muss zunächst den Blatttschutz entfernen

2. Es erscheint folgende Debug-Fehlermeldung:

3. Das Resultat deiner/meiner Bemühungen sieht dann so aus:

Er löscht bzw. färbt im Blatt Level1 nur die verbundenen Zellen, in den Blättern Level2 und Level3 passiert gar nichts.

Interessant ist auch, dass nach dem Speichern der Datei und dem erneuten Öffnen die Aufforderung "Makros aktivieren" nicht erscheint und ich mit F5 dieses extra aktivieren muss (stellt für mich aber kein Problem dar).

Aber ich möchte deine Freizeit nicht weiter strapazieren und löse dies wie bisher durch Eingabe der Zellen ins Makro ( es betrifft in erster Linie das Thema Bruchrechnen, bei dem es extra viele ungeschützte Zellen gibt; bei den anderen Kapiteln sind es wesentlich weniger Bereiche).

Vielen, vielen Dank für deinen Einsatz, wobei ich mich jedesmal nur mit einem Lob/einem Dank revanchieren kann.

Robert

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Robert,

ich hatte vergessen zu schreiben, dass das Makro in ein allgemeines Modul kopiert werden muss.

Ohne die Datei, die die Fehlermeldung produziert, kann ich leider nicht viel dazu sagen.

Gruß

M.O.
0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Noch einen späten guten Abend an das Forum und M.O.!

Ich lade mal 2 Beispiele hoch:

Beim ersten Beispiel funktioniert der Code super:

https://filehorst.de/d/cxIFutoc

Beim zweiten Beispiel da muckt es, obwohl ich, wie du ( M.O. ) geschrieben hast, den Code in ein allgemeines Modul geschrieben habe.

https://filehorst.de/d/cDpfoxIx

Ich hoffe, dass ich beim zweiten Beispiel nicht wieder ein klassisches Eigentor (sprich falscher Codeeingabe) geschossen habe.

Danke von Robert
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Robert,

In der Tabelle Level1 sind außer den verbundenen Zellen alle anderen Zellen geschützt. Daher werden hier nur die verbundenen Zellen gelöscht und eingefärbt.
Im Tabellenblatt Level2 sind alle Zellen geschützt. Daher kann das Makro hier keine Zellen löschen und Einfärben.
Das Tabellenblatt Level3 ist mit Passwort geschützt Und auch hier sind, so wie ich das sehe, alle Zellen geschützt Daher kommt hier dann die Fehlermeldung.

Soll das Makro in geschützten Tabellen ausgeführt werden, so kann man das Aufheben und Wiedereinrichten des Passwortschutzes auch per VBA machen.

Gruß
M.O.
...