Office: (Office 2016) Sortieren mit Autofilter bei aktivem Blattschutz

Helfe beim Thema Sortieren mit Autofilter bei aktivem Blattschutz in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo zusammen, ich habe in meiner Tabelle 2 Registerblätter die mit Blattschutz versehen werden um eine fehlerhafte Bedienung durch die späteren... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von ertaa, 8. Mai 2019.

  1. ertaa Erfahrener User

    Sortieren mit Autofilter bei aktivem Blattschutz


    Hallo zusammen,

    ich habe in meiner Tabelle 2 Registerblätter die mit Blattschutz versehen werden um eine fehlerhafte Bedienung durch die späteren User zu verhindern.
    Die jeweils oberste Zeile und die ersten paar Spalten sind fixiert, der Autofilter ist für den gesamten Bereich gesetzt.

    Mit dem folgenden Code habe ich bisher problemlos arbeiten können, allerdings erlaubt er mir nur die Filter zu setzen, aber nicht zu sortieren:
    HTML:
    With Sheets("Tab1")
         .Protect userinterfaceonly:=True
         .EnableOutlining = True
         .EnableAutoFilter = True
       End With
    Um das Sortieren zu ermöglichen habe ich es jetzt mit diesem Code probiert, bekomme aber immer einen Laufzeitfehler 1004 und finde meinen Fehler nicht:
    HTML:
    With Sheets("Tabelle1")
         .Unprotect
         .Protection.AllowEditRanges.Add Title:="Tabelle1", Range:=Columns("A:AP")
         .Protect userinterfaceonly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
           AllowSorting:=True, AllowFiltering:=True
        End With
    Vielleicht kann mich hier jemand auf die richtige Spur führen..
    Danke auf jeden Fall schon Mal im Voraus
     
    ertaa, 8. Mai 2019
    #1
  2. Exl121150 Erfahrener User
    Hallo,

    Du verwendest folgende VBA-Anweisung:
    Sheets("Tabelle1").Protection.AllowEditRanges.Add Title:="Tabelle1", Range:=Columns("A:AP")

    Diese bewirkt, dass für das Arbeitsblatt "Tabelle1" ein "AllowEditRanges"-Objekt erzeugt wird und diesem über die ".Add"-Methode ein ".Item" hinzugefügt wird, das über seine Nummer innerhalb des "AllowEditRanges"-Objektes oder über seinen Namen (Title:="Tabelle1") angesprochen werden kann. Dieser Name muss daher eindeutig sein und es darf daher nicht versucht werden, diesen ein zweites Mal bei Verwendung der Add-Methode zu vergeben. Tut man das, wird der Laufzeitfehler 1004 produziert.
    D.h. wenn du das erste Mal vorgenannte Anweisungszeile durchführen lässt, wird sie ohne Brummen und Murren von Excel durchgeführt - beim zweiten/dritten... Versuch erzeugt Excel den Fehler 1004.

    Warum lässt du nicht einfach diese Zeile im Makro weg? Die nachfolgende ".Protect"-Anweisung erlaubt in der Benutzeroberfläche ohnehin nur das Sortieren und Filtern im aktiven Arbeitsblatt.
     
    Exl121150, 9. Mai 2019
    #2
  3. ertaa Erfahrener User
    Danke für das Hintergrundwissen. Ich hatte vorher allerdings schon eindeutige Namen ausprobiert, ohne dass es funktioniert hat.

    Generell ist diese Zeile wegen der Range drin und auch, weil ich nicht sortieren kann wenn die Zeile raus ist. Ohne die Zeile läuft zwar der Code durch, allerdings kommt dann beim Sortieren mit aktivem Blattschutz immer die Meldung "Die Zelle oder das Diagramm, die bzw. das Sie ändern möchten, befindet sich auf einem schreibgeschützten Blatt. Um eine Änderung vorzunehmen, heben Sie den Schutz des Blatts auf. Möglicherweise werden Sie aufgefordert, ein Kennwort einzugeben." …. und genau das soll das Script ja umgehen.

    Habe jetzt gerade nochmal rumjongliert ..
    Sheets("Tabelle1").Protection.AllowEditRanges.Add Title:="Plan", Range:=Columns("A:AP")

    Sheets("Plan").Protection.AllowEditRanges.Add Title:="Tabelle1", Range:=Columns("A:AP")

    Sheets("Tabelle1").Protection.AllowEditRanges.Add Title:="Tabelle1", Range:=Columns("A:AP")

    Sheets("Plan").Protection.AllowEditRanges.Add Title:="Plan", Range:=Columns("A:AP")


    Das macht alles keinen Unterschied, es kommt immer der 1004er und einmal kam der Laufzeitfehler 9 … den konnte ich aber nicht reproduzieren.
     
    Zuletzt bearbeitet: 9. Mai 2019
    ertaa, 9. Mai 2019
    #3
  4. Exl121150 Erfahrener User

    Sortieren mit Autofilter bei aktivem Blattschutz

    Hallo,

    im nachfolgenden VBA-Makro habe ich den Blattschutz für Arbeitsblatt "Tabelle1" eingebaut, wobei aber die Spalten A:AP editierbar bleiben sollen (Objekt "AllowEditRanges" mit Item-ID="Tab1"). Das Makro kann jetzt beliebig oft aufgerufen werden. Entsprechender Kommentar ist enthalten.
    Code:
    Option Explicit
    
    Sub BlattSchutzTabelle1()
      Dim EdRg As AllowEditRange
      
      On Error GoTo Err_Blattschutz
      
      With Sheets("Tabelle1")
         'Aufheben des Blattschutzes für Blatt "Tabelle1":
         .Unprotect
         
         With .Protection
           If .AllowEditRanges.Count Then
              'Alle AllowEditRanges-Objekte des Blattes "Tabelle1" werden entfernt:
              For Each EdRg In .AllowEditRanges
                 EdRg.Delete
              Next EdRg
           End If
           'Die Spalten A:AP des Blattes "Tabelle1" dürfen bei aktivem Blattschutz trotzdem editiert werden (ID: "Tab1")
           .AllowEditRanges.Add Title:="Tab1", Range:=Columns("A:AP")
         End With 'protection
         
    Err_Blattschutz:
         'Der Blatschutz für Blatt "Tabelle1" wird wieder eingeschaltet, auch wenn vorher ein Fehler eintrat:
         .Protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
      End With
      
    End Sub
    Im folgenden VBA-Makro habe ich dir ein Beispiel für eine Tabellensortierung trotz aktivem Blattschutz beigefügt: Dieser wird zu Beginn ausgeschaltet und nach dem Sortiervorgang wieder eingeschaltet. Kommentar ist wieder enthalten.
    Code:
    Sub Sortieren()
      Dim Rg As Range
      
      With Sheets("Tabelle1")
    
          On Error GoTo Err_Sort
          'Der Blattschutz von Blatt "Tabelle1" wird aufgehoben:
          .Unprotect
          
          'Aktueller Datenbereich (Sortierbereich), der die Zelle Tabelle1!A1 umgibt
          Set Rg = .Range("A1").CurrentRegion
          
          With .AutoFilter.Sort
            With .SortFields
              .Clear
              'Sort-Order: xlAscending=1 / xlDescending=2
              'Im aktuellen Datenbereich werden die Zeilen nach Spalte B aufsteigend sortiert
              '        und bei gleichen Werten in Spalte B nach Spalte D  absteigend sortiert:
              .Add Key:=Rg.Columns(2), SortOn:=xlSortOnValues, Order:=1, DataOption:=xlSortNormal
              .Add Key:=Rg.Columns(4), SortOn:=xlSortOnValues, Order:=2, DataOption:=xlSortNormal
            End With
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply                    '<-- Sortierung wird ausgeführt
          End With 'AutoFilter.Sort
          
    Err_Sort:
         'Der Blattschutz von Blatt "Tabelle1" wird wieder eingeschaltet, auch wenn vorher ein Fehler eintrat:
         .Protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
     
     End With 'Ws
    
    End Sub
    
     
    Exl121150, 10. Mai 2019
    #4
  5. ertaa Erfahrener User
    Oha, vielen Dank! Das sieht alles toll aus, aber damit bin ich als VBA-Laie leider überfordert und habe es bei mir nicht zum Laufen bekommen.
    Spontan habe ich die folgenden Fragen:

    Was genau fällt denn im ersten Teil unter "editierbar"? Ersetzt das das Sperren der Zellen?
    Die Zeile 1 und die Spalten A:L, sowie einige der Spalten danach sollen gesperrt und/oder ausgeblendet (keine Formel sichtbar) bleiben wenn der Blattschutz aktiv ist.

    Muss man im zweiten Teil die zu sortierenden Zellen/Spalten/Zeilen anwählen oder im Code vorgeben wie sortiert werden soll? Also kein (Autofilter)Dialog?

    Muss man die beiden Makros durch einen Button o.ä. auslösen bzw. wo muss ich den Code einfügen - einfach in "DieseArbeitsmappe" oder in "Tabelle1" etc.?
     
    Zuletzt bearbeitet: 13. Mai 2019
    ertaa, 13. Mai 2019
    #5
  6. Exl121150 Erfahrener User
    Hallo,

    wieso gebärdest du dich wie ein VBA-Kenner, indem du ein so spezielles Objekt verwendest? Ich dachte daher, dass zumindest die elementaren VBA-Grundlagen bekannt seien.

    Ich dachte, dass auch das bei dir bekannt sein müsste, da du dieses Objekt verwendet hast - nicht ich habe dieses Objekt vorgeschlagen.
    Übersetzt man das englische Wort "AllowEditRanges", so meint es, dass Bereiche erzeugt werden sollen, in denen das Editieren erlaubt sein soll. Dabei bedeutet "Editierbar" in der EDV-Branche, dass Daten verändert werden können.
    D.h. trotz der "Protect"-Methode fürs Arbeitsblatt (die eine Datenveränderung unmöglich machen sollte) sollen Bereiche festgelegt werden, in denen trotzdem auch der User Zellen ändern darf/kann. Das Sperren/Entsperren von Zellen ist die weitaus einfachere Möglichkeit, Ausnahmen vom Blattschutz einzurichten - als ein solches (relativ exotisches) Objekt zu verwenden.

    Ich dachte bisher, es sollen die Spalten A:AP editierbar (=änderbar) bleiben - plötzlich sind es ganz andere Bereiche, die geschützt/ausgeblendet etc. werden sollen. Da ich nicht wissen kann, was mir im nächsten Posting an Überraschungen bevorsteht, beende ich damit meine Unterstützung - vielleicht fährt jemand anderer an meiner Stelle fort.
     
    Exl121150, 14. Mai 2019
    #6
Thema:

Sortieren mit Autofilter bei aktivem Blattschutz

Die Seite wird geladen...
  1. Sortieren mit Autofilter bei aktivem Blattschutz - Similar Threads - Sortieren Autofilter aktivem

  2. Seltsames Problem beim Sortieren

    in Microsoft Excel Hilfe
    Seltsames Problem beim Sortieren: Hallo! :) Ich wollte in Microsoft Excel folgende Auflistung so sortieren, dass die Werte neben den Jahreszahlen absteigend aufgelistet werden, die Jahre mit den höchsten Zahlen oben stehen:...
  3. Sortierung und Zusammenfassung nach Kostenstelle

    in Microsoft Excel Hilfe
    Sortierung und Zusammenfassung nach Kostenstelle: Servus ich muss Lieferscheine wo die Materialien immer gleich deklariert werden ( jedes Material bestimmten Namen und auch immer den gleichen) sortieren und dann eine Summe daraus bilden sodass...
  4. Benutzerdefiniertes Sortieren nach eigener Liste funktioniert nicht

    in Microsoft Excel Hilfe
    Benutzerdefiniertes Sortieren nach eigener Liste funktioniert nicht: Hallo zusammen, ich erstelle eine etymologische Datenbank zu einer Sprachgruppe in Nigeria. Die Spalte "SORTIEREN" soll dabei nach dem unten angegebenen Muster benutzerdefiniert sortiert werden....
  5. Sortieren in einer Matrix

    in Microsoft Excel Hilfe
    Sortieren in einer Matrix: Hallo, ich habe folgendes Problem: ich habe im Tabellenblatt: Firmenumsätze eine Matrix die mir die Firmen incl. aufsummierten Bruttobeträge anzeigt. ich möchte aber gerne sortieren können,...
  6. Leider falsches Datumsformat - sortieren möglich?

    in Microsoft Excel Hilfe
    Leider falsches Datumsformat - sortieren möglich?: Guten Morgen. Ich habe eine ellenlange Liste mit Geburtstagen. Eigentlich ist es ja einfach, diese von 1.Jan bis 31.Dez zu sortieren. Leider fehlt mir - das Jahr - die richtige Formatierung -...
  7. Tabelle automatisch Sortieren

    in Microsoft Excel Hilfe
    Tabelle automatisch Sortieren: Ich habe eine Tabelle wo ich Namen von Kollegen eintrage Vorname Nachname Geburtsdatum Alter A3 B3 C3 D3 Wenn ich in der letzten Zeile einen neuen...
  8. [Excel 2003] VBA: Sortieren und Autofilter via Makrorekorder

    in Microsoft Excel Hilfe
    [Excel 2003] VBA: Sortieren und Autofilter via Makrorekorder: hallo leute ich habe eine tabelle, die sortiert werden soll. dafür muss ich schaltflächen erstellen und das sortieren per knopfdruck auslösen. dies alles mit hilfe des makro-rekorders....

Users found this page by searching for:

  1. Blattschutz in exel sperrt nicht trotz aktiven Blattschutz

  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden