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. Spalten Sortieren nach Zahlen mit Buchstaben (1a,1b usw)

    in Microsoft Excel Hilfe
    Spalten Sortieren nach Zahlen mit Buchstaben (1a,1b usw): Hallo, ich habe hier eine Bauteileliste erstellt um diese leichter wiederzufinden. Das Problem ist, dass die Baugruppen unterteilt sind (ist ein Kartonbaubogen mit mehreren Hundert Teilen,...
  3. Tabellensortierung kollidiert mit Formel

    in Microsoft Excel Hilfe
    Tabellensortierung kollidiert mit Formel: Liebes Forum, ich bin neu hier und habe eine vermutlich schnelle/einfache Frage: Ich habe eine (unsortierte) Tabelle und außerhalb der Tabelle eine Formel, die bestimmte Werte dieser Tabelle...
  4. Filtern von Datensätzen

    in Microsoft Excel Hilfe
    Filtern von Datensätzen: Guten Abend erst einmal, ich bin neu hier im Forum und wollte gerne Euer Schwarmwissen um Hilfe bitten. Meine Excelversion ist Version 16.87 (24071426) Ich habe folgendes Problem. Ich habe eine...
  5. Spalte A nach Buchstaben, dann erst nach Zahlen sortieren

    in Microsoft Excel Hilfe
    Spalte A nach Buchstaben, dann erst nach Zahlen sortieren: Hallo zusammen, ich bitte um eure Hilfe. Ich habe ein Exceldokument wo in einer Spalte z.B. folgende Infos eingetragen sind: A1: ELB02 A2: CBA01 A3: 90000 A4: CBB01 A5: 90001 A6: ELB03 A7: 90002...
  6. Spalten sortieren, wenn die ersten 2 Zeilen aus verbundenen Zellen bestehen

    in Microsoft Excel Hilfe
    Spalten sortieren, wenn die ersten 2 Zeilen aus verbundenen Zellen bestehen: Gruss an die Excel-Gemeinde Ich bin ein Greenhorn in Bezug auf Excel. Deshalb macht mir folgendes Problem Schwierigkeiten. Ich habe eine Datei mit 4 Spalten. Nach jeder Spalte möchte ich sortieren...
  7. Spalte unterschiedlich sortieren/ausblenden

    in Microsoft Excel Hilfe
    Spalte unterschiedlich sortieren/ausblenden: Hallo zusammen, ich würde gerne in einer sehr langen Liste - ähnlich der im Anhang - sowohl im Linken Bereich als auch im rechten Bereich -getrennt/unabhängig voneinander die Zeilen mit der...
  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....
  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