Funktion für bestimme Ws ausführen.

Forumthread: Funktion für bestimme Ws ausführen.

01.02.2021 12:05:43

Jacek

Hallo zusammen,
verzeiht, dass ich eure Expertise erneut in Anspruch nehmen muss.
Ich hatte mir eben überlegt, ob ich einen Befehl oder eine Funktion für durch mich gewählte Sheets ausführen kann.
Wenn ich nun Blatt 1 bis Blatt 20 in meiner Datei habe, den Befehl aber nur für Blätter 5-13 ausführen möchte, kann ich dann folgendermaßen vorgehen?


Sub SpieltkeineRolle
Dim wb as Workbook
Dim ws1, ws2, ws3, ws4, etc as WorkSheet
Set ws1 = Worksheets ("Blatt1")
Set ws2 = Worksheets ("Blatt2")
Set usw...
Kann ich nun eine For-Schleife laufen lassen?
For ws1 & ws2 & ws3 & etc.
Code
Next (hier fehlt mir was)
Oder ginge es möglicherweise mit With?
With ws1 & ws2 & ws3 & etc
Code
End With
End Sub

?
Würde mir riesig weiterhelfen, weil ich dann bestimmte Funktionen einfach für mehrere Blätter anwenden könnte.
Und ich nehme an, das könnte evtl auch mein Problem mit dem Blattschutz lösen.
https://www.herber.de/forum/messages/1809740.html
VG und danke für eure Expertise
Jacek :)

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Funktion für bestimme Ws ausführen.

01.02.2021 12:14:10

Daniel

Hi
verwende Arrays..
statt

Dim ws1, ws2, ws3, ws4, etc as WorkSheet
Set ws1 = Worksheets ("Blatt1")
Set ws2 = Worksheets ("Blatt2")
Set usw...

dieses:

Dim ws(1 to 20) as worksheet
set ws(1) = Worksheets("Blatt1")
set ws(2) = Worksheets("Blatt2")
usw

dann kannst du programmieren:

for i = 1 to 20
ws(i)....
Next

oder eben auch, wenn s nicht alle Blätter sein sollen

for i = 5 to 13
ws(i)....
Next

solltest du die Blätter dem Array in der exakt gleichen Reihenfolge zuweisen wie sie der Reiteranordnung entspricht, dann kannst du die Variable auch ganz weg lassen und die Worksheets über die Indexnummer ansprechen:

For i = 5 to 13
Worksheets(i)....
Next

das mit der Array-Variablen brauchst du nur, wenn die Blätter für das Makro eine andere Reihenfolge bzw Anordnung haben sollen als über die Reiter vorgegeben bzw bestimmte generell nicht bearbeitet werden sollen.
Gruß Daniel

AW: Funktion für bestimme Ws ausführen.

01.02.2021 12:18:04

Jacek

Hi Daniel,
genialer Einfall!
Werde ich direkt nach meiner Mittagspause ausprobieren!
Vielen Dank für diesen Ansatz!
Es macht echt Spaß VBA zu lernen :D
VG
Jacek

AW: Funktion für bestimme Ws ausführen.

01.02.2021 14:24:52

Jacek

Hi Daniel,
ich habe es nun implementiert. Funktioniert an sich einwandfrei. Bis auf eine Sache:
Der Autofilter wird für jedes Blatt bis auf das erste gesetzt. Und ich bin mir nicht sicher warum. Denn das Blatt durchläuft die Schleife eigentlich auch.
Ich bin ein wenig verwirrt.
Der restliche Code wird ausgeführt. Nur nicht der Autofilter :D


Sub Blattschutz()
Dim i As Integer
Dim ws(1 To 20) As Worksheet
Set ws(1) = Worksheets("Hofer-Ranks")
Set ws(2) = Worksheets("AUS-Ranks")
Set ws(3) = Worksheets("AO-Ranks")
Set ws(4) = Worksheets("DE-Ranks")
Set ws(5) = Worksheets("CRI-Ranks")
Set ws(6) = Worksheets("GSIB-Ranks")
Set ws(7) = Worksheets("UK-Ranks")
Set ws(8) = Worksheets("US-Ranks")
For i = 1 To 8
ws(i).EnableAutoFilter = True
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("B2:N400").Select
Selection.Locked = True
Selection.FormulaHidden = False
If Not Range("B1:N1").AutoFilter Then
Range("B1:N1").AutoFilter
End If
ws(i).Protect userinterfaceonly:=True, Password:="GBC1"
Next i
End Sub

Eine Idee woran dies liegen könnte?
Vielen Dank für die Hilfe!
VG
Jacek :)

AW: Funktion für bestimme Ws ausführen.

01.02.2021 15:03:48

Daniel

Hi
Cells und Range ohne tabellenblattangabe davor beziehen sich immer auf aktive Tabellenblatt!
du bearbeitest daher immer das gleiche Blatt.
Anfängerprogrammierer lösen das Problem, in dem sie als ersten Schritt in der Schleife das jeweilige zu bearbeitende Tabellenblatt selektieren, damit dieses das aktive ist:

ws(i).Select

fortgeschrittene Programmierer verzichten auf Select und Selection und hängen die jeweiligen Befehle direkt an das Range oder Cells.
Damit das richtige Tabellenblatt verwendet wird, schreiben sie dieses noch direkt vor das Range oder Cells.
also aus

Range("B2:N400").Select
Selection.Locked = True
Selection.FormulaHidden = False

wird

ws(i).Range("B2:N400").Locked = True
ws(i).Range("B2:N400").FormulaHidden = False

für den rest dann analog.
Gruß Daniel

AW: Funktion für bestimme Ws ausführen.

01.02.2021 15:37:19

Jacek

Hallo Daniel,
nun habe ich es folgendermaßen eingebaut:


Sub Blattschutz()
Dim i As Integer
Dim ws(1 To 20) As Worksheet
Set ws(1) = Worksheets("Hofer-Ranks")
Set ws(2) = Worksheets("AUS-Ranks")
Set ws(3) = Worksheets("AO-Ranks")
Set ws(4) = Worksheets("DE-Ranks")
Set ws(5) = Worksheets("CRI-Ranks")
Set ws(6) = Worksheets("GSIB-Ranks")
Set ws(7) = Worksheets("UK-Ranks")
Set ws(8) = Worksheets("US-Ranks")
For i = 1 To 8
ws(i).EnableAutoFilter = True
ws(i).Cells.Locked = False
ws(i).Range("B2:N400").Locked = True
ws(i).Range("B2:N400").FormulaHidden = True
'.EnableAutoFilter = True   'lässt den Autofilter trotz Blattschutz zu
If ws(i).Range("B1:N1").AutoFilter = False Then
ws(i).Range("B1:N1").AutoFilter
End If
ws(i).Protect userinterfaceonly:=True, Password:="GBC1"
Next i
End Sub

Ich habe folgendes festgestellt:
Lasse ich es durchlaufen, wird der Filter überall gesetzt & der Blattschutz funktioniert.
Lasse ich es nochmals durchlaufen, erkennt meine if-Schleife nicht ob der Filter gesetzt ist.
Demnach wird der Autofilter wieder entfernt.
Eine Idee woran das liegen könnte?
Habe es mit


If Not ws(i).Range("B1:N1").AutoFilter Then

und


If ws(i).Range("B1:N1").AutoFilter = False Then

versucht.
Darüberhinaus habe ich festgestellt, dass wenn ich das jeweilige Arbeitsblatt in einer neuen Datei via VBA speichere mein Blattschutz flöten geht.
Baue ich dann nochmal einen Blattschutz ein, ist beim Öffnen der Datei jeweils der Autofilter geblockt. Beispielhafter Auszug für ein Blatt:


Sub SaveRanks()
Application.ScreenUpdating = False
Dim Datum As String
Datum = Format(Now, "mm_yyyy")
Sheets("Hofer-Ranks").Copy
ActiveWorkbook.SaveAs Filename:= _
"G:\GBC General\NeTr\Ranking_TEST\" & Sheets("Hofer-Ranks").Name & "_" & Datum & ".xlsx" _
BlattschutzSave
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

und natürlich zugehörig


Sub BlattschutzSave()
With ActiveSheet
If Not AutoFilterMode = True Then
.EnableAutoFilter = True
End If
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("B2:N400").Select
Selection.Locked = True
Selection.FormulaHidden = False
.Protect userinterfaceonly:=True, Password:="GBC1"
End With
End Sub

Ich habe echt noch einige Baustellen :D
Ich suche auch viel im Forum. Aber finde nicht immer die passende Lösung.
Mal sehen, wann ich zu den darauffolgenden Features komme :D
Hab noch bisschen was vor :D
Gruß Jacek

AW: Funktion für bestimme Ws ausführen.

01.02.2021 16:04:02

ralf_b

vielleicht liegts auch nur an dem Punkt

 If Not .AutoFilterMode = True Then

AW: Funktion für bestimme Ws ausführen.

01.02.2021 16:15:05

Jacek

Hi ralf_b,
habe ich auch schon versucht. Meine Bugs bleiben bestehen.
Irgendwo entdecke ich den springenden Punkt nicht.
Meine Abfragen nach dem aktiven Autofilter werden in keiner Funktion erkannt
+
Nach dem Speichern der Sheets in ein neues Workbook ist der Filter zwar gesetzt aber ich kann ihn nicht bedienen. Den Feldnamen kann ich aber ändern.
Es ist zum Verzweifeln
Viele Grüße und danke für die Hilfe
Jacek

AW: Funktion für bestimme Ws ausführen.

02.02.2021 08:11:58

Jacek

Hi ralf_b,
konnte meine Fehler gestern Abend noch lösen.
Weswegen die Abfrage des Autofilters nicht funktioniert hat:
Meine Abfrage war wie folgt.


If Not ws(i).AutoFilterMode = True Then
ws(i).EnableAutoFilter = True
End If

Was keinen Sinn macht. AutoFilterMode ist ein Boolean Wert. Also habe ich explizit erfragt:
"Wenn nicht wahr = wahr, dann"
Was ich aber eigentlich fragen möchte:
"Wenn nicht wahr, dann"
=>


If Not ws(i).AutoFilterMode Then
ws(i).EnableAutoFilter = True
End If

-------------------------------------------------------------------------------------------
Der zweite fehler hinsichtlich des Speicherns von Arbeitsblättern in neue Dateien und dass der Autofilter flöten gegangen ist.
Folgendes kleine Codestück hat gefehlt, um das zu ermöglichen:


, AllowFiltering:=True

Im Gesamtcode:


Sub BlattschutzSave()
With ActiveSheet
If Not .AutoFilterMode Then
.EnableAutoFilter = True
End If
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("B2:N400").Select
Selection.Locked = True
Selection.FormulaHidden = False
.Protect userinterfaceonly:=True, Password:="PW", AllowFiltering:=True
End With
End Sub

Jetzt funktioniert alles :)

AW: Funktion für bestimme Ws ausführen.

02.02.2021 08:27:18

ralf_b

Moin, die Abfrage not (wahr = wahr) ist die selbe wie not (wahr).daran kann es nicht gelegen haben.

AW: Funktion für bestimme Ws ausführen.

02.02.2021 10:58:59

Jacek

Hi,
per se gebe ich dir Recht.
Dennoch:
Nach dieser kleinen Änderung hat es funktioniert.
Ansonsten habe ich nichts bei der Abfrage verändert.

Beliebteste Forumthreads (12 Monate)

Funktion für bestimme Ws ausführen.

Forumthread: Funktion für bestimme Ws ausführen.

11 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread