Sortierung auf geschütztem Blatt

Forumthread: Sortierung auf geschütztem Blatt

04.07.2014 09:28:44

Erik

Liebe Gemeinde,
mit folgendem Makro schütze ich ein Tabellenblatt:


Public Sub ProtectSheets(Optional pSheetName As String = "")
Dim ws As Worksheet
If pSheetName = "" Then
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
.Protect _
UserInterfaceOnly:=True, _
AllowSorting:=True, _
AllowFiltering:=True
.EnableAutoFilter = True
.EnableOutlining = True
.EnableSelection = xlUnlockedCells
End With
Next ws
Application.ScreenUpdating = True
Else
With Sheets(pSheetName)
.Protect _
UserInterfaceOnly:=True, _
AllowSorting:=True, _
AllowFiltering:=True
.EnableAutoFilter = True
.EnableOutlining = True
.EnableSelection = xlUnlockedCells
End With
End If
End Sub

Wobei pSheetname ein optionaler Parameter ist. In meiner Anwendung kann das ein vorgegebener Name oder ActiveSheet.Name o.a. sein.
Auf meinem Tabellenblatt habe ich einen Autofilter in einem Rangebereich "B5:G154". Alle andern Zellen in diesem Blatt werden durch o.g. Makro gesperrt.
Mein Problem ist, dass der Autofilter funktioniert, jedoch nicht das Sortieren.
Die Überschriften des Rangebereiches, in denen sich auch die Autofilterpfeile befinden, habe ich schon entsperrt, aber es tritt keine Besserung ein.
Es kommt die klassische Meldung, dass ich den Blattschutz aufheben soll... :(
Kann mir jemand weiterhelfen? Ich bin für jeden Hinweis dankbar - also auch den Anfang eines roten Fadens.
Gruß
Erik

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Sortierung auf geschütztem Blatt

04.07.2014 10:17:49

Nepumuk

Hallo,
das funktioniert nur, wenn sich innerhalb der zu sortierenden Liste keine gesperrte Zelle befindet.
Übriges, optionale Parameter haben, wenn kein Wert übergeben wird, den Initialisierungswert des Variablentyps. Der ist bei einem String leer, sprich du kannst dir die Angabe:
(Optional pSheetName As String = "")
sparen und einfach:
(Optional pSheetName As String)
schreiben.
Ich würde da aber an Stelle des Tabellenblattnamens das Tabellenblatt als Objekt übergeben.
Gruß
Nepumuk

AW: Sortierung auf geschütztem Blatt

04.07.2014 11:58:02

Erik

Hallo Nepumuk,
vielen, vielen Dank! Das war es! Jetzt geht es wieder. Es hatte sich eine gesperrte Zelle eingeschlichen.
Danke auch für den Hinweis.
Kannst du mir kurz erklären, warum du dem Makro das Sheet-Objekt übergeben würdest? Würde es verstehen, damit ich den Code verbessern kann.
Gruß
Erik

AW: Sortierung auf geschütztem Blatt

04.07.2014 12:14:02

Nepumuk

Hallo,
zeig mal eine Prozedur mit der du das Makro "ProtectSheets" für eine einzelne Tabelle aufrufst. Ich muss erst sehen wie du grundsätzlich auf Tabellen zugreifst.
Gruß
Nepumuk

AW: Sortierung auf geschütztem Blatt

04.07.2014 12:39:17

Erik

Hallo Nepumuk,
ich habe das Makro geschrieben, um es immer dann zu verwenden, wenn ich etwas per VBA in ein Tabellenblatt eintrage.
Zum Beispiel:


Option Explicit
Public Sub SaveDirectory()
Dim FilePath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "C:\"
.Title = "Rechnungsordner auswählen"
.ButtonName = "Auswählen"
.InitialView = msoFileDialogViewList
If .Show = -1 Then
FilePath = .SelectedItems(1)
If Right(FilePath, 1)  "\" Then
FilePath = FilePath & "\"
Else
FilePath = ""
End If
End With
If FilePath = "" Then
MsgBox ("Kein Ordner gewählt!")
Else
Call UnprotectSheets("Verknüpfungen")
Sheets("Verknüpfungen").Range("B27").Value = FilePath
Sheets("Verknüpfungen").Range("B28").Value = Now
Call ProtectSheets("Verknüpfungen")
MsgBox ("Der Ordner wurde eingebunden.")
End If
End Sub

Diese Makro öffnet einen Auswahldialog, mit dem der Anwender/die Anwenderin einen Ordner auswählen kann. Der Pfad wird dann in der Zelle "B27" des Tabellenblattes "Verknüpfungen" gespeichert. Das Blatt ist standardmäßig geschützt, weshalb der Schutz vor dem Eintrag aufgehoben werden muss und anschließend wieder gesetzt wird.
Danke dir für dein Interesse.
Gruß
Erik

AW: Sortierung auf geschütztem Blatt

04.07.2014 12:54:02

Erik

Hallo Nepumuk,
mir ist gerade aufgefallen, dass ich dir einen defekten Code geliefert habe.


Option Explicit
Public Sub SaveDirectory()
Dim FilePath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "C:\"
.Title = "Rechnungsordner auswählen"
.ButtonName = "Auswählen"
.InitialView = msoFileDialogViewList
If .Show = -1 Then FilePath = .SelectedItems(1)
End With
If Right(FilePath, 1)  "\" Then
FilePath = FilePath & "\"
Else
FilePath = ""
End If
If FilePath = "" Then
MsgBox ("Kein Ordner gewählt!")
Else
Call UnprotectSheets("Verknüpfungen")
Sheets("Verknüpfungen").Range("B27").Value = FilePath
Sheets("Verknüpfungen").Range("B28").Value = Now
Call ProtectSheets("Verknüpfungen")
MsgBox ("Der Ordner wurde eingebunden.")
End If
End Sub

Gruß
Erik

AW: Sortierung auf geschütztem Blatt

04.07.2014 17:54:37

Nepumuk

Hallo,
ich würde das so machen:

Public Sub SaveDirectory()
    Dim FilePath As String
    Dim objTargetSheet As Worksheet
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\"
        .Title = "Rechnungsordner auswählen"
        .ButtonName = "Auswählen"
        .InitialView = msoFileDialogViewList
        If .Show = -1 Then
            FilePath = .SelectedItems(1) & "\" 'bei Ordnern immer !!!
            Set objTargetSheet = Worksheets("Verknüpfungen")
            Call UnprotectSheets(objTargetSheet)
            With objTargetSheet
                .Range("B27").Value = FilePath
                .Range("B28").Value = Now
            End With
            Call ProtectSheets(objTargetSheet)
            Set objTargetSheet = Nothing
            MsgBox ("Der Ordner wurde eingebunden.")
        Else
            MsgBox "Kein Ordner gewählt!"
        End If
    End With
End Sub

Public Sub ProtectSheets(Optional ByRef pSheet As Worksheet)
    Dim ws As Worksheet

    If pSheet Is Nothing Then
        Application.ScreenUpdating = False
        For Each ws In ThisWorkbook.Worksheets
            With ws
                .Protect _
                    UserInterfaceOnly:=True, _
                    AllowSorting:=True, _
                    AllowFiltering:=True
                .EnableAutoFilter = True
                .EnableOutlining = True
                .EnableSelection = xlUnlockedCells
            End With
        Next ws
        Application.ScreenUpdating = True
    Else
        With pSheet
            .Protect _
                UserInterfaceOnly:=True, _
                AllowSorting:=True, _
                AllowFiltering:=True
            .EnableAutoFilter = True
            .EnableOutlining = True
            .EnableSelection = xlUnlockedCells
        End With
    End If
End Sub

Noch besser wäre es, wenn du direkt mit dem Objektnamen der Tabellen arbeiten würdest. Die Objektnamen findest du im Projektexplorer vor dem in Klammern stehenden Tabellennamen auf der Exceloberfläche. Das ist erstens schneller da du nicht über den Umweg der Auflistungsklasse gehen musst, und zweites macht es dich unabhängig von dem Tabellennamen welchen der User sieht. Der darf also ruhig Tabellen umbenennen, dein Programm greift trotzdem auf die richtigen Tabellen zu. Drittens bekommst du direkt beim kompilieren des Codes eine Fehlermeldung falls sich eine Tabelle nicht mehr in der Mappe befindet, das hast du beim Benutzen der Auflistungsklassen nicht.
Wenn z.B. deine Tabelle "Verknüpfungen" den Objektnamen "Tabelle3" hat, dann einfach so:

Public Sub SaveDirectory()
    Dim FilePath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\"
        .Title = "Rechnungsordner auswählen"
        .ButtonName = "Auswählen"
        .InitialView = msoFileDialogViewList
        If .Show = -1 Then
            FilePath = .SelectedItems(1) & "\" 'bei Ordnern immer !!!
            Call UnprotectSheets(Tabelle3)
            With Tabelle3
                .Range("B27").Value = FilePath
                .Range("B28").Value = Now
            End With
            Call ProtectSheets(Tabelle3)
            MsgBox ("Der Ordner wurde eingebunden.")
        Else
            MsgBox "Kein Ordner gewählt!"
        End If
    End With
End Sub

Wobei ich den Objektnamen der Tabelle z.B. auf tabCatenation ändern würde. Damit siehst du im Code sofort, dass hier auf eine Tabelle zugegriffen wird, und welche Funktion die Tabelle hat (entschuldige dass ich grundsätzlich alles in englisch halte was Programme betrifft, aber das habe ich so gelernt). Ändern kannst du den Namen im Eigenschaftsfenster ganz oben.
Gruß
Nepumuk

AW: Sortierung auf geschütztem Blatt

08.07.2014 08:38:50

Erik

Hallo Nepumuk,
danke! Ich hätte nicht gedacht, dass da noch so viel aus dem Code rauszuholen ist. Vielen Dank auch für den Hinweis mit den Objektnamen, da werde ich in Zukunft mal mehr darauf schauen.
Also für dich: 2x thumb up!
Gruß
Erik

Beliebteste Forumthreads (12 Monate)

Sortierung auf geschütztem Blatt

Forumthread: Sortierung auf geschütztem Blatt

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread