Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Code zum Sortieren

Betrifft: Code zum Sortieren von: Martin
Geschrieben am: 05.08.2014 07:34:40

Hallo, ich mal wieder :-)

nun habe ich mal versucht, tatsächlich ein Makro selbst zu erstellen (auch mithilfe des Makrorecorders), allerdings verzweifel ich gerade an einer bestimmten Aufgabe. Aber von Anfang:
Die zu sortierende Tabelle ist bereits als Tabelle2 definiert und befindet sich in der Range A3:K63, wobei in der Zeile 3 die Überschriften stehen.
Da die Tabelle schreibgeschützt sein soll kann das Sortieren in meinen Augen nur mithilfe eines Makros geschehen (korrigiert mich, wenn ich falsch liege), nach dem Motto: Entsperren - Sortieren - Sperren.
Nun soll über ein Dropdownfeld ausgewählt werden, nach welcher Spalte sortiert wird, und über ein Optionsfeld, ob auf- oder absteigend. Dank der Zellverknüpfung habe ich den "Output" momentan in P5 (Spaltennummer) bzw. Q5 (Aufsteigend oder absteigend) verknüpft. In Zelle P6 habe ich mit einer Index-Formel aus der Spaltennummer die Überschrift gezogen, in Q6 steht die Formel WENN(Q5=WAHR;"xlAscending";"xlDescending").

Wie bekomme ich den Zelleninhalt in das folgende Makro:

Sub Schaltfläche8_Klicken()
'
' Schaltfläche8_Klicken Makro
' Sortieren
'

'
    Tabelle2.Unprotect
    Tabelle2.ListObjects("Tabelle2").Sort.SortFields.Clear
    Tabelle2.ListObjects("Tabelle2").Sort.SortFields. _
         Add Key:=Range("Tabelle2[[#All],[Startplatz]]"), SortOn:=xlSortOnValues, _
         Order:=xlDescending, DataOption:=xlSortNormal
    With Tabelle2.ListObjects("Tabelle2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Tabelle2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


Eine weitere Frage wird sich mir danach noch stellen: Wie bekomme ich Schutz aufheben & schützen hin, wenn ich ein Passwort verwende. Das Passwort sollte möglichst nicht in Reinform im Makro auftauchen.

Danke und Grüße
Martin

  

Betrifft: AW: Code zum Sortieren von: Robert
Geschrieben am: 05.08.2014 07:54:38

Hallo Martin,

Deine Problemstellung lässt sich glaube ich lösen, indem du dir mal die Optionen beim Schützen eines Blattes anschaust. Da gibt es nämlich die Option, z.B. Filtern auch im geschützten Modus zu erlauben.

Das Passwort sollte möglichst nicht in Reinform im Makro auftauchen.
Ist leider schwer umzusetzen.
Warum vergibst du nicht einfach auch ein Passwort für den VBA Editor?
Dann kann ein Nutzer ein Makro zwar anwenden, aber nicht den Code anschauen.

Viele Grüße
Robert


  

Betrifft: AW: Code zum Sortieren von: Oberschlumpf
Geschrieben am: 05.08.2014 07:55:22

Hi Martin

zum PW:
... Das Passwort sollte möglichst nicht in Reinform im Makro auftauchen...

Anders geht es nicht.
Du könntest aber (auch) den VBA-Code mit Passwort schützen.

zur anderen Frage:
Zeig uns doc mal ne Bsp-Datei inklusive deines Codes, Bsp-Daten + noch mal Erklärungen.

Ciao
Thorsten


  

Betrifft: AW: Code zum Sortieren von: Martin
Geschrieben am: 05.08.2014 08:18:01

Hallo Thorsten,

da sich eine .xlsm-Datei hier nicht hochladen lässt habe ich die Datei in ein .xlsx-Format umgewandelt:
https://www.herber.de/bbs/user/91892.xlsx
Im Bereich ab Spalte O siehst Du das Dropdownmenü, das Optionsfeld sowie die Schaltfläche.
Mit Klick auf die Schaltfläche soll dann die Tabelle A3:K63 nach der im Dropdownfeld ausgewählten Spalte aufsteigend oder absteigend sortiert werden (inkl. dem entsperren und dem anschließenden Sperren)

@Robert: Möglicherweise haben wir unterschiedliche Excel-Versionen. Ich habe das Problem, dass ich eine Tabelle nicht sortieren kann, wenn auch nur ein gesperrtes Feld enthalten ist. In meiner angehängten Datei sind die Spalten D:I geschützt, daher kann ich noch nicht mal nach Spalte A sortieren, wenn das Arbeitsblatt geschützt ist, selbst wenn man Sortieren und Autofiltern erlaubt. Eine Internetrecherche brachte mich zum gleichen Ergebnis. Probiere bitte aus, meine Tabelle zu sortieren. Wenn das geht lass uns unterhalten, wie das möglich ist.

Danke und Grüße
Martin


  

Betrifft: AW: Code zum Sortieren von: Martin
Geschrieben am: 05.08.2014 08:21:09

Ps. noch eine Frage: wie kann ich den VBA-Code mit Passwort schützen?


  

Betrifft: AW: Code zum Sortieren von: Hajo_Zi
Geschrieben am: 05.08.2014 08:26:00

Hallo Martin,

im VBA Editor auf Datei gehen, rechte Maustaste, Eigenschaften, Schutz. Passwort nicht vergessen. Fragen zu Knackprogrammen sind im Forum nicht gerne gesehen.
In Excel ist nichts sicher.

GrußformelHomepage


  

Betrifft: AW: Code zum Sortieren von: Robert
Geschrieben am: 05.08.2014 08:30:19

Hallo Martin...

Wenn du das ganze Blatt sperrst, musst du nicht noch einzene Zellen Sperren, denn das macht das Sortieren der Tabelle unmöglich.

Alle Zellen der Tabelle dürfen nicht als "Gesperrt" markiert werden.
Danach das Blatt schützen mit der Option "Sortieren erlauben"

Viele Grüße
Robert


  

Betrifft: AW: Code zum Sortieren von: Oberschlumpf
Geschrieben am: 05.08.2014 08:37:40

Hi Robert

aber Zellen müssen doch gesperrt sein, z Bsp Formelzellen, um die Wirkung des Blattschutzes "erleben" zu können.

Entsperr mal ALLE Zellen einer Tabelle, setz den Blattschutz, und du wirst sehen, dass der Blattschutz null Wirkung hat.

Und es könnten auch ALLE Zellen gesperrt sein.
Im Code muss dann nur in der 1. Zeile stehen .Unprotect PW und in der letzten Zeile muss stehen .Protect PW

Ciao
Thorsten


  

Betrifft: AW: Code zum Sortieren von: Robert
Geschrieben am: 05.08.2014 08:55:58

Hallo Thorsten

Entsperr mal ALLE Zellen einer Tabelle, setz den Blattschutz, und du wirst sehen, dass der Blattschutz null Wirkung hat.

Dazu kann ich nur Sagen:
Entsperr mal ALLE Zellen einer Tabelle, setz den Blattschutz, und du wirst sehen, dass der Blattschutz seine volle Wirkung entfaltet.

Habe noch nie einzelne Zellen Gesperrt, bevor ich den Blattschutz setze.
Blattschutz gesetzt und alle Zellen sind schreibgeschützt.
Je nach Option kann man trotzdem noch sortieren/filtern/ein- und ausblenden/etc.


  

Betrifft: AW: Code zum Sortieren von: Robert
Geschrieben am: 05.08.2014 08:58:20

Mein Fehler, meine Zellen sind natürlich per Default alle Gesperrt.

Also muss man, um sortieren zu ermöglichen eben alle Zellen Sperren,

Sorry, my bad!


  

Betrifft: hehe..ok,alles is gut. ;-) ...owT von: Oberschlumpf
Geschrieben am: 05.08.2014 09:03:34




  

Betrifft: AW: Code zum Sortieren von: Oberschlumpf
Geschrieben am: 05.08.2014 08:30:37

Hi Martin

a) sollen 1,2,3 ALLE Bsp-Daten sein, die du bereit bist, in die Tabelle einzutragen????
b) wooo is der Code????
beim Ändern ins xlsx-Format wirst auch du die Meldung gelesen haben, dass der Code entfernt wird - super Plan!
Pack die Datei MIT Code und MEHR Bsp-Daten in eine ZIP-Datei. ZIP-Dateien können hier per Upload zur Verfügung gestellt werden.

c) wegen VBA-PW guckst du hier
http://bit.ly/1mhMnET

bis später vielleicht
Thorsten


  

Betrifft: AW: Code zum Sortieren von: Martin
Geschrieben am: 05.08.2014 09:02:28

Hallo Thorsten.

Punkt a Kannst Du mir die Aussage erklären? Ich möchte die Tabelle A3:K63 nach einer bestimmten Spalte sortieren. Z.B. sollen sämtliche Daten nach Spalte A (Startplatz) sortiert werden. Eben das Gleiche wie das manuelle Sortieren, wenn der Blattschutz aufgehoben ist. Dabei kann entweder die gesamte Tabelle gefüllt sein oder nur ein Teilbereich. Die Spalte wird über ein Dropdownmenü ausgewählt, über eine Checkbox wird ausgewählt, ob auf- oder absteigend.
Punkt b: Den Code habe ich im ersten Post per Code eingefügt. Nichts anderes steht im Makro, daher dachte ich, dass auch ein .xlsx als Beispiel reicht. Nichts desto trotz habe ich die Datei gepackt, siehe hier: https://www.herber.de/bbs/user/91895.zip
Punkt c: Danke

abschließend: Mir ist bewusst, dass ich als Bittsteller zu Euch komme! Trotzdem wurde ich bisher mit einem gewissen Respekt behandelt.
An welcher Stelle ich mich Dir gegenüber so verhalten habe, dass eine solche Reaktion Deinerseits nötig ist weiß ich nicht. Wenn wir uns gegenseitig als Menschen respektieren ist das Zusammenleben einfacher!!!

Gruß
Martin


  

Betrifft: wer anders auch gern bitte... von: Oberschlumpf
Geschrieben am: 05.08.2014 09:21:01

Hi Martin

erst mal...
..ich hab vor dir und jedem anderen Menschen immer Respekt.
Ähh, nee, falsch, Achtung is das richtige Wort...für Respekt bin ich zu frech :-)...Respekt hab ich nur, wenn mir gerad nix Lustiges einfällt :-)

Hab aber verstanden, was du meinst - und trozdem hab ich was dagegen zu sagen.

Ja, ich hätte den Code kopieren + an der richtigen Stelle im VBE einfügen können.
Und da fängt es schon an - die richtige Stelle

Ich weiß, wo im VBE welcher Code stehen muss/nicht stehen darf.
Aber weiß das auch JEDER, der ne Frage hat?
Nein, weiß er nicht! Hab ich schon oft genug erlebt. Es wird der richtige Code eingesetzt, komm nur leider nicht zur Ausführung, weil Excel ihn "nicht findet".

Und aus diesem Grund ist es mir immer am liebsten, wenn der Fragende Bsp-Daten + Code komplett "abliefert".

Nun zu den Bsp-Daten in deiner Datei.
mit 1,2,3 könnt ich nur nach der 1. Spalte sortieren.
Weiß ich aber, ob alles richtig funktioniert?
Ohne, dass ich sehe, wie andere Daten mitsortiert werden, weiß ich nicht 100%ig, ob alles im Code richtig ist.

Und, ja, ich bin zu faul, deinen Job zu erledigen (ausreichend Bsp-Daten eintragen), aber ....ich bin für diesen Job auch "zu doof".
Ich weiß gar nicht, was für Daten in den anderen Spalten stehen müssen - so gesehen, kann ich deinen Job gar nicht erledigen.

Nennt euch bitte nicht Bittsteller.
Ja, ihr habt ne Frage, wir oft ne Antwort.
Aber Bittsteller?...ich find, das hört sich so devot an....schlechter als der, der was weiß.

Ich hoffe + wünsche mir, dass meine direkte Art (ja, ich sag + schreib immer direkt, was ich meine) dich nicht davon abhält, hier weiter nach Antworten zu suchen.

Denn es gibt kein besseres, kompetenteres Excel-Forum als dieses hier!
(gibbs vllt doch, aber ich kenn es noch nich :-) )

Nun leider noch was "Negatives".
Ich hab vllt erst wieder heute nachmittag/abend Zeit, mich um deine Frage zu kümmern.
Ich muss ab morgen für 5 Wochen in eine Reha, und muss noch packen, etc :-)

Ciao erst mal
Thorsten


  

Betrifft: Sortkriterien aus Zelle auslesen von: Martin
Geschrieben am: 05.08.2014 13:45:48

okay, nach langer Suche konnte ich zumindest schon mal die Werte aus den Zellen lesen:
sortorder = Range("Q6").Value
SortBy = Range("P6").Value


In P6 steht z.B. "Startplatz", in Q6 entweder "xlAscending" oder "xlDescending". Nun müsste ich eigentlich nur noch im Makro die Werte variabilisieren:
Tabelle2.ListObjects("Tabelle2").Sort.SortFields. _
Add Key:=Range("Tabelle2[[#All],[Startplatz]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal



  

Betrifft: AW: Sortkriterien aus Zelle auslesen von: fcs
Geschrieben am: 05.08.2014 15:41:36

Hallo Martin,

Tabellen-Objekte haben ihre eigenen kleine Welt, da ist das eine odere andere etwas anders als innerhalb eines Tabellenblatts.

u.a. kann man sie nur ändern wenn der Blattschutz aufgehoben ist.

mit Verarbeitung der aus dem Tabellenblatt eingelesenen Werte für Spaltentitel und Sortierrichtung sieht dein Makro etwa wie folgt aus.

Gruß
Franz

Sub Schaltfläche8_Klicken()
'
' Schaltfläche8_Klicken Makro
' Sortieren
'

  Dim sortorder As Long, SortBy As String
  
  Const strPW As String = "test"
  
  SortBy = Range("P6").Text
  If SortBy = "" Then
    MsgBox "Bitte Spaltentitel in P6 auswählen"
  End If
  Select Case Range("Q6").Value
     Case "xlAscending"
        sortorder = xlAscending
     Case "xlDescending"
        sortorder = xlDescending
     Case Else
      MsgBox "unzulässige Vorgabe (nur xlAscending oder xlDescending) " _
        & "für Sorierreihenfolge in Zelle Q6"
      Exit Sub
  End Select
    
    Tabelle2.Unprotect strPW
    With Tabelle2.ListObjects("Tabelle2").Sort
        .SortFields.Clear
        .SortFields. _
             Add Key:=Range("Tabelle2[[#All],[" & SortBy & "]]"), SortOn:=xlSortOnValues, _
             Order:=sortorder, DataOption:=xlSortNormal
    
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Tabelle2.Protect Password:=strPW, DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub



  

Betrifft: AW: Sortkriterien aus Zelle auslesen von: Martin
Geschrieben am: 05.08.2014 18:44:34

Vielen Dank, Franz, funktioniert super!!!


 

Beiträge aus den Excel-Beispielen zum Thema "Code zum Sortieren"