Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Makros nur in gerade angezeigter Datei ausführen

Makros nur in gerade angezeigter Datei ausführen
22.06.2016 13:33:33
Katja
Hallo zusammen,
habe mal wieder ne Frage.
ich habe ca. 2800 Excel-Dateien.
700 Dateien enthalten eine Tabelle mit den Spalten B-F. Der Inhalt dieser Tabellen wird durch ein Makro geschützt.
2100 Dateien enthalten eine Tabelle mit den Spalten B-M. Der Inhalt dieser Tabellen wird ebenfalls durch ein Makro geschützt.
Beide Schutzmakros werden durch Strg+m gespeichert und geschützt.
Habe ich mehrere Datei der einen Sorte oder der anderen Sorte auf klappt alles.
Habe ich aber eine Mischung der Dateien auf läuft das falsche Makro.
Gibt es eine Möglichkeit dies zu verbessern/ändern? Das Makro liegt im Modul1.
Eigentlich müsste nur das Schutzmakro benutzt werden, welches in der jeweiligen Datei gespeichert wurde. Die Tastenkombination soll bleiben. Mit diesen Dateien arbeiten 70 Mitarbeiter.
Ich weiß einfach gedacht, aber lässt sich das auch umsetzen?
Ich hoffe ihr habt ne Idee.
LG Katja

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

Betreff
Datum
Anwender
Anzeige
AW: Makros nur in gerade angezeigter Datei ausführen
22.06.2016 13:44:23
ransi
Hallo,
Habe ich aber eine Mischung der Dateien auf läuft das falsche Makro.
Gibt es eine Möglichkeit dies zu verbessern/ändern?

Warum rufst du nicht einfach das richtige Makro auf ?
Zeigst du uns mal den Code und den Aufruf ?
ransi

Makros nur in gerade angezeigter Datei ausführen
22.06.2016 16:29:26
Katja
Hallo,
wenn ich wüsste, wie das geht würde ich nicht fragen.
Habe das Makro nicht geschrieben, den Kollegen gibt es bei uns nicht mehr.
Makro 1: Für die Spalten B-G
Sub Schutzmakro()
' Schutzmakro Makro
' Tastenkombination: Strg+m
ActiveSheet.Unprotect
' Spalte B ist die einzige durchlaufend besetzte Spalte,
' daher wird diese als Maß für das Auffüllen der Tabelle benutzt
' die erste mögliche Position in dieser Spalte gesetzt
Range("B7").Select
Counter = 0
' Schleife soll laufen, bis eine Zelle ohne Eintrag erreicht ist
' Überprüfung auf "NUL" genügt, da dann zumindest alle notwendigen Zellen geschützt sind.
Do   ' Jetzt werden alle Zellen pro Zeile hintereinander weg geschützt.
Selection.Offset(Counter, 0).Locked = True
Selection.Offset(Counter, 1).Locked = True
Selection.Offset(Counter, 2).Locked = True
Selection.Offset(Counter, 3).Locked = True
Selection.Offset(Counter, 4).Locked = True
Selection.Offset(Counter, 5).Locked = True
' Zähler wird um 1 erhöht, die nächste Zeile ist dran.
Counter = Counter + 1
' Schleife beginnt erneut.
Loop Until Selection.Offset(Counter, 0).Value = ""
' kompletter Blattschutz für die gesamte Tabelle wir wieder eingerichtet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
' Aktuelle Tabelle wird gesichert.
ActiveWorkbook.Save
End Sub
Makro 2: Für die Spalten B-M
Sub Schutzmakro()
' Schutzmakro Makro
' Tastenkombination: Strg+m
ActiveSheet.Unprotect     'für das aktuelle Blatt in der aktiven Arbeitsmappe Blattschutz  _
aufheben
' Spalte G (Riss-Nr.) ist die einzige durchlaufend besetzte Spalte,
' daher wird diese als Maß für das Auffüllen der Tabelle benutzt
' die erste mögliche Position in dieser Spalte gesetzt
Range("G5").Select
Zeile = 0
' Schleife soll laufen, bis eine Zelle ohne Eintrag erreicht ist
' Überprüfung auf "NUL" genügt, da dann zumindest alle notwendigen Zellen geschützt sind.
Do
' Jetzt werden alle Zellen pro Zeile hintereinander weg geschützt.
Selection.Offset(Zeile, 0).Locked = True
Selection.Offset(Zeile, 1).Locked = True
Selection.Offset(Zeile, 2).Locked = True
Selection.Offset(Zeile, 3).Locked = True
Selection.Offset(Zeile, 4).Locked = True
Selection.Offset(Zeile, 5).Locked = True
Selection.Offset(Zeile, 6).Locked = True
Selection.Offset(Zeile, -1).Locked = True
Selection.Offset(Zeile, -2).Locked = True
Selection.Offset(Zeile, -3).Locked = True
Selection.Offset(Zeile, -4).Locked = True
Selection.Offset(Zeile, -5).Locked = True
Zeile = Zeile + 1     'nächste Zeile  --> Schleife beginnt erneut
Loop Until Selection.Offset(Zeile, 0).Value = ""     'bedeutet: laufe bis beliebiger Zeile in  _
Rissspalte leer ist
' kompletter Blattschutz für die gesamte Tabelle wird wieder eingerichtet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save     ' Aktuelle Arbeitsmappe wird gespeichert.
End Sub

Anzeige
AW: Makros nur in gerade angezeigter Datei ausführen
22.06.2016 17:17:57
UweD
Hallo
das Makro macht NUR:
- Alle Zellen von B1 bis G (bis zur letzten Zeile (gefüllen Zeile in B) den Zellschutz setzen.
Vorher den Blattschutz rausnehmen und nachher wieder setzen, speichern
Das hat dein Kollege einzeln gemacht.
Lässt sich verkürzen auf
- eine Ermittlung der letzten Zeile in B
- einmaligen Schutz des ganzen Bereichs.
Sub Schutzmakro() 'B bis G
' Tastenkombination: Strg+m
Dim LR&
With ActiveWorkbook.ActiveSheet
.Unprotect
LR = .Cells(Rows.Count, "B").End(xlUp).Row 'letzte Zeile der Spalte B
.Range("B1:G" & LR).Locked = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
ActiveWorkbook.Save
End Sub
Wenn du uns jetzt noch angeben kannst, woran das Makro erkennen könnte ob es bis G oder bis M
arbeiten soll, könnte das auch noch eingebaut werden.
- kommen nach G bzw. nach M noch weitere Daten in den folgenden Spalten?
Gruß UweD

Anzeige
Makros nur in gerade angezeigter Datei ausführen
22.06.2016 18:00:44
Katja
Hallo Uwe,
danke für deine schnelle Antwort.
Ich habe dir mal 2 Bsp.-Dateien hochgeladen. Vielleicht findest du darin, was du brauchst.
Habe sie gekürzt und wegen Datenschutz ein wenig xxx.
https://www.herber.de/bbs/user/106433.xls
https://www.herber.de/bbs/user/106434.xls
Könnte man darin noch ein Prüfung einbauen, so dass in Spalte B (VN-Nr.) in der einen Tabelle oder in Spalte G (Riss-Nr.)der anderen Tabelle immer was stehen muss?
LG Katja

Anzeige
AW: Makros nur in gerade angezeigter Datei ausführen
23.06.2016 09:00:46
UweD
Hallo Katja
folgendes habe ich gemacht:
- eine Ermittlung der letzten benutzten Spalte
- - bis F gefüllt (warum dann bis G sperren?)
- - bis M gefüllt
- Dadurch ist das Makro jetzt in Jeder der 2 Varianten nutzbar
- Eine Prüfung eingebaut, ob in der betroffenen Spalte B bzw. G Leerzellen enthalten sind.
- - -
noch ein Tip deine Makro könntest du aus allen Dateien entfernen
und meins in die Personl ablegen, auch mit Ctrl-m belegen. Dann wäre es für alle Dateien nutzbar.
Sub Schutzmakro() 'B bis G / M 
    ' Tastenkombination: Strg+m 
    Dim LR&, SP%, Tmp
    With ActiveWorkbook.ActiveSheet
        Select Case .Cells(3, .Columns.Count).End(xlToLeft).Column 'letzte Spalte einer Zeile 3 
            Case 6 'prüfen bis F, dann bis G sperren 
                SP = 2
            Case 13 'prüfen bis M, dann bis M sperren 
                SP = 7
        End Select
        LR = .Cells(Rows.Count, SP).End(xlUp).Row 'letzte Zeile der Spalte B oder G 
        
        'Prüfung Vollständigkeit 
        If WorksheetFunction.CountBlank(.Range(.Cells(5, SP), .Cells(LR, SP))) > 0 Then
            Tmp = MsgBox("Lücken entdeckt in Spalte " & vbLf & vbLf & "    '" & .Cells(3, SP) _
                & "'" & vbLf & vbLf & "Bearbeitung abgebrochen!", vbOKOnly + vbCritical, "Fehler")
            Exit Sub
        End If
        
        .Unprotect
        .Range(.Cells(5, 2), .Cells(LR, SP)).Locked = True
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With
    ActiveWorkbook.Save
End Sub

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media

Code erstellt und getestet in Office 15 - mit VBAHTML 12.6.0

Gruß UweD

Anzeige
Makros nur in gerade angezeigter Datei ausführen
23.06.2016 14:22:55
Katja
Hallo Uwe,
ich habe deinen Code noch etwas verändert, da sonst nur die Spalte B (2)/ G (7) geschützt wurde.
In der Tabelle mit den Spalten B-G (2-7)muss die Spalte B auf Vollständigkeit geprüft werden aber von B-G geschützt werden - Hatte eine neue Spalte eingefügt deshalb G.
In der Tabelle mit den Spalten von B-M muss die Spalte G auf Vollständigkeit geprüft werden aber von B-M geschützt werden.
Sub Schutzmakro_UWE() 'B bis G / M
' Tastenkombination: Strg+m
Dim LR&, SP%, LS&, Text$
With ActiveWorkbook.ActiveSheet
Select Case .Cells(3, .Columns.Count).End(xlToLeft).Column 'letzte Spalte einer Zeile 3
Case 7 'wenn letzte Spalte = Spalte  7 (G) (VN-Liste)
SP = 2
LS = 7
Text = "FN-Nr."
Case 13 'wenn letzte Spalte = Spalte 13 (M) (Flur-Liste)
SP = 7
LS = 13
Text = "FFR"
End Select
LR = .Cells(Rows.Count, SP).End(xlUp).Row 'letzte Zeile der Spalte B oder G
'Prüfung Vollständigkeit
If WorksheetFunction.CountBlank(.Range(.Cells(5, SP), .Cells(LR, SP))) > 0 Then
Tmp = MsgBox("Lücken entdeckt in" & vbLf & vbLf & "Spalte :   " & Text _
& vbLf & vbLf & "Bearbeitung abgebrochen!", vbOKOnly + vbCritical, "Fehler")
Exit Sub
End If
.Unprotect
.Range(.Cells(5, 2), .Cells(LR, LS)).locked = True 'Zellen schützen bis G/M
.Range("B3").Select
MsgBox "Zellen geschützt", vbOKOnly, "Fertig !"
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
ActiveWorkbook.Save
End Sub
Funktioniert 1A.
Aber:
Du suchst jetzt nur in der Spalte 2/7 nach der letzten Zeile, aber der Teufel steckt ja im Detail.
Was wenn die Spalte am Ende nicht richtig ausgefüllt wurde?
Kannst du das so abändern, das nach der letzte Zeile egal in welcher Spalte gesucht wird? Ich hoffe du verstehst mich.
Dann habe ich noch eine Frage zu der PERSONAL.XLSB.
Bei uns arbeiten ca. 70 Mitarbeiter mit diesen Dateien und müssen dieses Makro ausführen. Die Dateien liegen im Firmennetzwerk. Da klappt das mit der PERSONAL.XLSB doch nicht, da diese doch auf der Festplatte jedes Rechners gespeichert wird. Oder?
LG, Katja

Anzeige
AW: Makros nur in gerade angezeigter Datei ausführen
23.06.2016 15:12:10
UweD
Hi
1)
da sonst nur die Spalte B (2)/ G (7) geschützt wurde
- Ok. das hatte ich nicht bedacht. Hast du aber doch gut gelöst.
2)
Kannst du das so abändern, das nach der letzte Zeile egal in welcher Spalte gesucht wird?
Tausche die LR= Zeile aus

LR = .Cells.SpecialCells(xlCellTypeLastCell).Row 'Letzte Zeile des gesamten Blattes
3)
PERSONAL.XLSB.
ja, das geht dann schlecht.
Gruß UweD

12 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige