Microsoft Excel

Herbers Excel/VBA-Archiv

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

Makro Korrektur

Betrifft: Makro Korrektur von: Steffen
Geschrieben am: 14.11.2014 11:58:05

Hallo Zusammen,

ich brauch mal wieder eure Hilfe,

Ich habe ein Makro welches mich fragt in welchem Ordner Exceldateien liegen welche ich bearbeiten will. Diese werden dann geöffnet und im SheetA in einem von mir definiertem Bereich von A7:A12 wird die letzte freie Zelle gesucht und dann der Wert von der darüberliegenden Zelle +1 eingetragen. Leider klappt es nicht, das das Makro zu dem SheetA springt. Mag sich das mal einer anschauen und Optimieren?

Sub neuesJahr()


    With Application.FileDialog(msoFileDialogFolderPicker)
     .Title = "Bitte Verzeichnis mit den Excel-Dateien auswählen, die " _
       & "ergänzt werden sollen"
     If .Show = -1 Then
       varVerzeichnis = .SelectedItems(1)
     Else
       Exit Sub
     End If
   End With
   strDatei = Dir(varVerzeichnis & "\*.xls*")
   If strDatei = "" Then
     MsgBox "Keine Excel-Dateien im gewählten Verzeichnis"
     Exit Sub
   End If
   With Application
     .ScreenUpdating = False
     .EnableEvents = False
     .Calculation = xlCalculationManual
   End With
   Do Until strDatei = ""
   
   With Application.Workbooks.Open( _
           Filename:=varVerzeichnis & "\" & strDatei, _
           ReadOnly:=False, UpdateLinks:=False)
           Workbook.Unprotect
        Sheets("SheetA").Select
      
   Set Bereich = Range("A7:A12")
   Set Bereich2 = Range("A7:A12")
   
   Bereich.Cells(1, 1).End(xlDown).Offset(1, 0).Select
ActiveCell.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Bereich2.Cells(1, 1).End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "0"
Selection.NumberFormat = "0%"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    
      
   ActiveWorkSheet.Protect
   ActiveWorkbook.Save
   ActiveWorkbook.Close
   End With
   
   Loop
   
   
   End Sub

Danke für eure Hilfe vorab.

Freundliche Grüße
Steffen

  

Betrifft: AW: Makro Korrektur von: Rudi Maintaire
Geschrieben am: 14.11.2014 12:22:16

Hallo,
wo hast du sowas her?
Workbook.Unprotect
ActiveWorkSheet.Protect

Selbst ausgedacht?

Wieso 2 Variablen für den gleichen Bereich?
Set Bereich = Range("A7:A12")
Set Bereich2 = Range("A7:A12")


Gruß
Rudi


  

Betrifft: AW: Makro Korrektur von: Steffen
Geschrieben am: 14.11.2014 12:35:23

Hi Rudi,

ich probiere natürlich ein bisschen rum da meine VBA Kenntnisse noch recht bescheiden sind hab ich das nicht alles so im Kopf wie was muss. Habe schon die verschiedensten Varianten durch welche irgentwie alle nicht funktionieren.

Der Bereich2 sollte eigentlich B7:B12 sein, tippfehler von mir.

Freundliche Grüße
Steffen


  

Betrifft: Kommt noch 'With Application.Workbooks.Open(... von: Luc:-?
Geschrieben am: 14.11.2014 12:37:58

…)', noch dazu ohne End With, hinzu, Rudi;
ziemlich unübersichtlich und zT Kauderwelsch ohne IntelliSense-Beachtung, mal ganz abgesehen von der überflüssigen Selectiererei…
Sieht hier nicht so aus, als ob der Sinn eines With-Konstrukts wirklich verstanden wurde. Vielleicht alles irgendwie zusammengetragen und eine Recorder-Aufzeichnung damit „verbessert“…?!
Gruß + schöWE, Luc :-?


  

Betrifft: AW: Kommt noch 'With Application.Workbooks.Open(... von: Steffen
Geschrieben am: 14.11.2014 12:56:36

Hallo Luc,

ich hab das With vor dem öffnen der Dateien gesetzt da die folgenden fomatierungen alle für die geöffnete Datei übernommen werden sollen. Das End With steht vor dem Loop am Ende des Makros...

Mit Selectiererei meinst du warscheinlich die ganzen "Selection.Borders" Anführungen. Hierbei handelt es sich tatsächlich um eine Aufzeichnung mit dem Recorder welche mir den Rahmen um die neu eingetragen Werte erstellt. Dies funktioniert ohne Probleme und erfüllt seinen Zweck daher habe ich dem keine weitere Aufmerksamkeit geschenkt da es mich auch nicht umbedingt stört.

Deine Antwort ist nicht wirklich hilfreich, wie wäre es mit einem Verbesserungsvorschlag?

Gruß
Steffen


  

Betrifft: AW: Kommt noch 'With Application.Workbooks.Open(... von: Rudi Maintaire
Geschrieben am: 14.11.2014 14:43:43

Hallo,
ich hab das With vor dem öffnen der Dateien gesetzt
warum benutzt du es dann nicht?

Gruß
Rudi


  

Betrifft: Das meine ich, ... von: Luc:-?
Geschrieben am: 14.11.2014 17:23:15

…Steffen:

With Application.Workbooks.Open(Filename:=varVerzeichnis & "\" & strDatei, _
     ReadOnly:=False, UpdateLinks:=False)
     Workbook.Unprotect
Dem folgt endlos später direkt vor Loop tatsächlich noch ein End With, aber wie auch Rudi schrieb, das so festgelegte Workbook-Objekt nutzt du gar nicht, wie schon der nächste, noch dazu falsche Befehl beweist! Außerdem wäre wohl in diesem Fall das Setzen einer neuen ObjektReferenz sinnvoller.
Das Workbook-Objekt hat zwar auch eine .[Un]Protect-Eigenschaft, aber dann eben ein bestimmtes und man sollte ein (ggf leeres) Passwort und was geschützt wdn soll angeben. Falls das so bleiben soll, hier also nur .Unprotect schreiben, was aber nicht den Blattschutz aufhebt. Es ist auch meist besser, wenn man den Zugriff von Pgmm auf die Blätter zulassen will, aber dem User bestimmte Eingriffe nicht, mit dem .Protect-Parameter UserInterfaceOnly:=True zu arbeiten.
Selection ist ein allgemeines Objekt, das vom VBE-Intellisense nicht unterstützt wird, weil es alles mögliche sein kann. Es ist also besser, direkt mit dem eigentlichen Objekt zu arbeiten.
Gruß, Luc :-?


  

Betrifft: AW: Das meine ich, ... von: Steffen
Geschrieben am: 14.11.2014 22:48:34

Was macht die Eigenschaft -[Un]protect denn dann, wenn nicht den Blattschutz aufheben? Wie müsste es dann aussehen, wenn ich die ausgewählten Dateien öffnen möchte, den Blatschutz aufheben will da die Zelle in welche etwas eingetragen wird schreibgeschützt ist, und das ganze dann bevor es gespeichert wird wieder geschützt werden soll?

Es würde reichen wenn nur in dem Worksheet in dem die Werte eingetragen werden, nennen wir es einfach Worksheet2, der Blatschutz aufgehoben wird.






  

Betrifft: Ich schrieb ja, dass du den Blattschutz NICHT ... von: Luc:-?
Geschrieben am: 14.11.2014 23:23:13

…aufheben musst, wenn du etwas per VBA in irgendwelche (auch geschützte) Zellen eintragen willst, Steffen,
du musst ihn dann nur bei Aktivierung des entsprd Blattes (Ereignis Worksheet_ActivateMe.Protect …) auch per Pgm setzen und dabei den zusätzlichen (4., benannten) Parameter UserInterfaceOnly:=True setzen. Dann gilt die Sperre nicht für VBA-gesteuerte Aktionen!
Ansonsten wird der BlattSchutz, wie schon der Name sagt, natürlich nur mit (hier das gewünschte Blatt) Worksheets(2).Unprotect Password:="…" aufgehoben.
Luc :-?


 

Beiträge aus den Excel-Beispielen zum Thema "Makro Korrektur"