Microsoft Excel

Herbers Excel/VBA-Archiv

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

seitenumbruch steuern

Betrifft: seitenumbruch steuern von: thomas
Geschrieben am: 11.09.2015 11:46:27

liebe helfer!

Ich sitze seit Tagen an einer Aufgabe - ein Raumplan soll erstellet werden: Wann ist welcher Raum, mit welcher Veranstaltung belegt. Die Daten sollen von wechselndem, teils unqualifiziertem Personal eingetragen werden. Einmal in der Woche wird ein zweiwöchiger Zeitraum (markierter Bereich) ausgedruckt und allen Mitarbeitern ausgehändigt um sich auf dieser Basis zu besprechen.

Aufbau der Tabelle bis jetzt: in Zeile 1 stehen die Räume 1 bis 5. In Spalte1 die Tage (datum) nach unten fortlaufend.

Das Problem: Ein Tag besteht nicht aus einer Zeile, sondern aus drei: Vormittags, Nachmittags, Abends.

Meine Lösung: In der ersten Spalte nehmen je drei verbundene Zellen einen Tag auf. rechts davon stehen dann drei zeilen für die drei Tagesabschnitte pro Tag.

Für das laufende Einpflegen von Daten am Bildschirm ist diese Aufteilung gut. in der Natur der Sache liegt es aber, dass die Zelleninhalte mal groß, mal leer, mal klein sind (automatische Anpassung der Spaltenhöhe bei feststehender Spaltenbreite). Dadurch passen mal zwei Tage auf ein Blatt, mal eine ganze Woche. Das ist okay. Was aber nicht passieren soll, dass ein Tag (bestehen aus drei zeilen) durch einen Seitenumbruch zerrissen wird. Ich glaubte das durch die verbundene Zelle verhindern zu können, doch die wird gnadenlos zerteilt.

Hatte schon einige Forumsbeiträge zu dieser Problematik gefunden. Als Antwort gab es immer nur die Geißelung der ach so bösen Verbunden Zellen. Die Lösungsvorschläge waren meist zu kompliziert und soweit ich das überblicken konnte, wurde das eigentliche Problem nicht verstanden. Bestimmte zeilen sollen einfach nicht durch einen Umbruch auf zwei Seiten verteilt werden.

Bin auch für lösungen ohne verbunden zellen offen, wenn dann in der ersten spalte nur in jeder dritten zeile das fortlaufende Datum erscheint und leicht durch "Ziehen nach unten" fortgeführt werden kann.

Vielen Dank fürs aufmerksame Lesen und drüber Grübeln

  

Betrifft: AW: seitenumbruch steuern von: EtoPHG
Geschrieben am: 11.09.2015 14:50:24

Hallo Thomas,

Mit diesem Code in einem Modul:

Sub CorrectHPBreak()
    Dim hpB As HPageBreak
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each hpB In ws.HPageBreaks
        If hpB.Location.Address <> hpB.Location.MergeArea.Cells(1, 1).Address Then
            ws.HPageBreaks.Add Before:=hpB.Location.MergeArea.Cells(1, 1)
        End If
    Next
End Sub
werden auf dem aktiven Blatt die Seitenumbrüche so korrigiert, dass sie immer nach der letzten vollständigen verbunden Zelle (in Spalte A) stehen.

Gruess Hansueli


  

Betrifft: ...oder vielleicht besser vollautomatisch von: EtoPHG
Geschrieben am: 11.09.2015 15:03:26

Hallo Thomas,

Statt obigen Vorschlag, diesen Code in DieseArbeitsmappe:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim hpB As HPageBreak
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each hpB In ws.HPageBreaks
            If hpB.Location.Address <> hpB.Location.MergeArea.Cells(1, 1).Address Then
                ws.HPageBreaks.Add Before:=hpB.Location.MergeArea.Cells(1, 1)
            End If
        Next
    Next ws
End Sub
sorgt automatisch vor dem Drucken für das Richtigstellen der Seitenumbrüche bei verbundenen Zellen in Spalte 1 aller Tabellenblätter.

Gruess Hansueli


  

Betrifft: AW: ...noch besser vollautomatisch und richtig von: EtoPHG
Geschrieben am: 11.09.2015 15:40:32

Hallo,

Kleine Korrektur/Ergänzung/Verbesserung:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim hpB As HPageBreak
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.ResetAllPageBreaks
        For Each hpB In ws.HPageBreaks
            If hpB.Location.Address <> hpB.Location.MergeArea.Cells(1, 1).Address Then
                ws.HPageBreaks.Add Before:=hpB.Location.MergeArea.Cells(1, 1)
            End If
        Next
    Next ws
End Sub
Gruess Hansueli


  

Betrifft: AW: ...noch besser vollautomatisch und richtig von: thomas
Geschrieben am: 12.09.2015 12:15:48

Hallo Hansueli

leider habe ich keine ahnung vom programmieren. So musste ich ertmal in erfahrung bringen, wo ich denn deinen code hin zu kopieren habe. Habe also einen Grundkurs überflogen (ohne mir mühe zu geben das programmieren wirklich zu lernen) und erfahren, wo sich die schaltfläche "Makros" befindet. Kam so schließlich zum VBA-Editor, wo ich deinen Code einfach mal reinkopiert habe. Bitte nicht Lachen. Bin nun mal ein Dummie in diesen Dingen. So ließ sich das Makro nicht starten.
Dann habe ich planlos herum probiert: als Makro-Namen habe ich erst mal dein "Workbook_BeforePrint" übernommen. Dann habe ich, ohne zu wissen was ich da tue "Option Explicit" rausgeschmissen. Nur mal eben so, damit es mehr so aussieht, wie die einfachen codes in besagtem grundkurs. Deshalb habe ich dann noch aus "Private Sub" ein "Sub" gemacht. Erst jetzt ließ sich das Makro bei mir starten.
Ergebnis: Bis zu einem gewissen grad springt der Umbruch tatsächlich zwischen die Tage. nach dem ersten Umspringen kommt es dann aber zu einer verdoppelung des umbruchs. es gibt dan kurz hintereinander zwei umbrüche: einen korrekten und einige zeilen weiter einen weiteren mitten durch einen Tag.
Ich gehe mal davon aus, dass das an meinen Änderungen liegt. Wie bekomme ich deinen originalcode ans laufen? Ich vermute mal das ist eine dumme frage.
Trotzdem dank für die bisherige mühe und einen weitern Tip


  

Betrifft: Code gehört in DieseArbeitsmappe von: EtoPHG
Geschrieben am: 12.09.2015 13:19:54

Hallo Thomas,

Im VDE Explorer den Eintrag DieseArbeitsmappe doppelklicken.
Den 2 t'en Code ohne Änderung in das leere Fenster kopieren. Er läuft automatisch (ohne Zutun des Benutzers) ab, sobald gedruckt wird.

Gruess Hansueli


  

Betrifft: AW: Code gehört in DieseArbeitsmappe von: thomas
Geschrieben am: 12.09.2015 21:21:53

Hallo Hansueli...
...ach doch so einfach *grins* Jaa! so läuft es jetzt- FAST. Zusammengefasste Zellen werden tatsächlich nicht mehr durch Umbrüche geteilt. Leider gibt es jetzt unnötig viele Umbrüche. Soll heißen: Oft entstehen Seiten, auf denen nur ein Tag erscheint, obwohl die folgenden tage klein genug wären, das mehrere auf der Seite platz gehabt hätten. Wir sind ein ökologisch orientierter Betrieb. Papierverschwendung findet bei den Kollegen keine Akzeptanz.
Von dem kleinen Fehler abgesehen ist die Tabelle jetzt top. Aber mit diesem Fehler bekomme ich das bei den Kolleginnen nicht durchgesetzt. Vielleicht fällt dir ja noch was ein?


  

Betrifft: AW: Code gehört in DieseArbeitsmappe von: thomas
Geschrieben am: 12.09.2015 22:02:38

nachtrag: nach betätigen der schaltfläche "Seitenumbrüche zurücksetzten" kommt es beim drucken zu einem bug oder besser gesagt: es erscheint die aufforderung zum debuggen, womit ich natürlich überfordert bin...


  

Betrifft: Komplizierter als gedacht... von: EtoPHG
Geschrieben am: 14.09.2015 10:30:22

Hallo Thomas,

Ich weiss zwar nicht, was du mit schaltfläche "Seitenumbrüche zurücksetzten" meinst, da ich keine Ahnung habe, was du machst und ich keinen Blick auf deinen Computer werden kann, aber...

Ich habe festgestellt, dass es zu den von Dir beschriebenen Effekten kommen kann und habe den Code korrigiert und mit verschiedenen zufälligen Grössen von verbundenen Zellen in Spalte 1 getestet.
Ersetze den alten Code in DieserArbeitsmappe durch den neuen hier und teste mal:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim ws As Worksheet
    Dim iHPCnt As Integer
    For Each ws In ThisWorkbook.Worksheets
        ' reset all page breaks
        ws.ResetAllPageBreaks
        ' correct all automatic breaks if broken within merged area in up-direction
        For iHPCnt = 1 To ws.HPageBreaks.Count
            With ws.HPageBreaks(iHPCnt)
                If .Type = xlPageBreakAutomatic Then
                    If .Location.Address <> .Location.MergeArea.Cells(1, 1).Address Then
                        ws.HPageBreaks.Add Before:=.Location.MergeArea.Cells(1, 1)
                        .Location = .Location.MergeArea.Cells(1, 1)
                    End If
                End If
            End With
        Next iHPCnt
    ' Correct the last HPagebreak, only if necessary
        If iHPCnt > 1 Then
            With ws.HPageBreaks(iHPCnt)
                If .Type = xlPageBreakAutomatic Then
                    If .Location.Address <> .Location.MergeArea.Cells(1, 1).Address Then
                        ws.HPageBreaks.Add Before:=.Location.MergeArea.Cells(1, 1)
                        .Location = .Location.MergeArea.Cells(1, 1)
                    End If
                End If
            End With
        End If
    Next ws
End Sub

Gib bitte Bescheid, ob es immer noch zu dem Effekt kommt und in diesem Fall möchte ich eine Bespielmappe mit der Spalte 1 & 2 und deren Zeilenhöhen (alle anderen Daten sind unwichtig, bzw. können gelöscht werden!). Damit kann ich den Effekt genauer analysieren.

Gruess Hansueli


  

Betrifft: AW: Komplizierter als gedacht... von: thomas
Geschrieben am: 14.09.2015 16:49:05

Hallo Hansueli! Ganz ganz große Klasse! Es funktioniert!!! Danke!!!!

In der Umbruchvorschau werden von excel noch überflüssige Umbrüche generiert, aber mit dem Drucken bereinigt sich das Ganze.

Das ganze funktioniert nicht, wenn man nur einen markierten Bereich drucken möchte. Aber dafür habe ich schon eine Lösung gefunden

Leider erscheint beim Drucken immer noch diese Fehlermeldung: Laufzeitfehler "9".: Index liegt außerhalb des gültigen Bereichs. Beantworte ich diese Meldung mit "beenden" (statt "debuggen") öffnet sich der ganz normale Druckdialog und ich kann das Blatt drucken und alle Umrüche sind Korrekt. Dachte das läge vielleicht an meinem Tabellendesign. Habe alles nochmal neu aufgebaut, zeilen einfrieren und drucktitel abgeschaltet. die Fehlermeldung bleibt aber. Hochladen kann ich dir das leider nicht. Warum auch immer!?! vielleicht weil ich an einem mac sitze

Wenn dir die Fehlermeldung was sagt und sich das leicht beheben lässt, freue ich mich nochmal von die zu lesen. Ansonsten bin ich schon seeeehr glücklich damit. jetzt muss das ganze nur noch auf arbeit laufen (andere excel-version) :-s


  

Betrifft: Back to square 1... von: EtoPHG
Geschrieben am: 15.09.2015 09:44:40

Hallo Thomas,

Ich hab jetzt an die 100 Versuche mit verschiedenen Konfigurationen gemacht.
Dabei habe ich auch den Index-Fehler rekonstruieren können.
Ich bin nun zu folgendem Resultat gelangt.
1. Der Code in DieserArbeitsmappe soll gelöscht werden.
2. Der folgende Code kommt in ein allgemeines Modul (im VBE Explorer Rechtsklick auf VBAProject - Einfügen - Modul)
3. Er muss manuelle über Makros oder einen Button ausgelöst werden.
4. Er stellt nur die Pagebreaks für das gerade aktive Blatt richtig.
5. Er kann nicht für (individuelle) Druckbereiche eingesetzt werden! Bei solchen müssen die Seitenumbrüche manuell durch den Benutzer gesetzt werden!

Sub CorrectPageBreaks()
    Dim ws As Worksheet
    Dim iHPCnt As Integer
    Set ws = ActiveSheet
    If ws.PageSetup.PrintArea = "" Then
        ws.Cells(1, 1).SpecialCells(xlLastCell).Select
    ' reset all page breaks
        ws.ResetAllPageBreaks
        ' correct all automatic breaks if broken within merged area in up-direction
        For iHPCnt = 1 To ws.HPageBreaks.Count
            With ws.HPageBreaks(iHPCnt)
                If .Type = xlPageBreakAutomatic Then
                    If .Location.Address <> .Location.MergeArea.Cells(1, 1).Address Then
                        ws.HPageBreaks.Add Before:=.Location.MergeArea.Cells(1, 1)
                    End If
                End If
            End With
        Next iHPCnt    ' Correct the last HPagebreak, only if necessary
        If iHPCnt > 1 Then
            With ws.HPageBreaks(iHPCnt)
                If .Type = xlPageBreakAutomatic Then
                    If .Location.Address <> .Location.MergeArea.Cells(1, 1).Address Then
                        ws.HPageBreaks.Add Before:=.Location.MergeArea.Cells(1, 1)
                    End If
                End If
            End With
        End If
    Else
        MsgBox "Bei Druckbereichen müssen Sie" & vbCrLf & _
               "die Seitenumbrüche manuell setzen!", vbExclamation, "Druckbereich"
    End If
End Sub

Bei meinen Versuchen, habe ich den Indexfehler nicht mehr produzieren können. Anscheinend verhindert das Selektieren der letzten Zelle im Blatt, dass Excel mit den Umbruch-Zählern durcheinander kommt.
Ein vollständig automatisierte Lösung wäre vielleicht möglich aber extrem aufwändig zu realisieren.

Gruess Hansueli


  

Betrifft: AW: Back to square 1... von: thomas
Geschrieben am: 15.09.2015 18:29:30

Hallo Hansueli,
jetzt habe ich ja schon ein schlechtes Gewissen. Du machst dir ja ganz schön Mühe wegen mir.
Wie gesagt, habe ich keine Programmiererfahrung. Einen button und ein Makro dazu aufzeichnen - das bekomme ich noch hin. Auch deine codes nach deiner Anweisung an die entsprechende stelle kopieren geht mittlerweile...
Ich habe einer Schaltfläche deinen Code zugeordnet, in einem allgemeinen Modul. Das Makro kann nun nicht ausgeführt werden, weil das Blatt geschützt ist. Ohne Blattschutz kommt wieder die Meldung Laufzeitfehler "9".: Index liegt außerhalb des gültigen Bereichs. Die Meldung beantworten mit "beenden" und dann läuft es. Habe ich da was falsch gemacht oder muss ich damit leben. (das könnte ich!)

Nun habe ich schon einen Button mit einem aufgezeichneten Makro, welches den Schutz aufhebt, Spalten markiert, Zeilenhöhen anpasst und schließlich den schutz wieder aktiviert. Das sieht dann so aus:

Sub Schaltfläche1_BeiKlick()
'
' Schaltfläche1_BeiKlick Makro
' passt zeilenhöhe an
'

'
    ActiveSheet.Unprotect
    Columns("B:N").Select
    Range("N1").Activate
    Selection.Rows.AutoFit
    Range("N2:N4").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowInsertingHyperlinks:=True
End Sub
Jetzt habe ich, unwissend wie ich nun mal bin, versucht deinen Code in dieses Makro zu klöppeln oder - der umgekehrte Weg - das Ein- und Ausschalten des Blattschutzes aus meinem Makro in deinen Code. Weder das eine, noch das andere ist mir gelungen. Vermutlich weil ich einfach keine Ahnung von der richtigen Syntax habe. Für dich ist das vermutlich ne Kleinigkeit...

Gruss Thomas


  

Betrifft: Letzter Versuch ;-) von: EtoPHG
Geschrieben am: 15.09.2015 22:35:01

Hallo Thomas,

Versuch's mal so:

Sub Schaltfläche1_BeiKlick()
    Dim ws As Worksheet
    Dim iHPCnt As Integer
    Set ws = ActiveSheet
    ws.Unprotect
    ws.UsedRange.Rows.AutoFit
    If ws.PageSetup.PrintArea = "" Then
        ws.Cells(1, 1).SpecialCells(xlLastCell).Select
    ' reset all page breaks
        ws.ResetAllPageBreaks
        ' correct all automatic breaks if broken within merged area in up-direction
        For iHPCnt = 1 To ws.HPageBreaks.Count
            With ws.HPageBreaks(iHPCnt)
                If .Type = xlPageBreakAutomatic Then
                    If .Location.Address <> .Location.MergeArea.Cells(1, 1).Address Then
                        ws.HPageBreaks.Add Before:=.Location.MergeArea.Cells(1, 1)
                    End If
                End If
            End With
        Next iHPCnt    ' Correct the last 2 HPagebreaks, only if necessary
        For iHPCnt = ws.HPageBreaks.Count - 2 To ws.HPageBreaks.Count
            With ws.HPageBreaks(iHPCnt)
                If .Type = xlPageBreakAutomatic Then
                    If .Location.Address <> .Location.MergeArea.Cells(1, 1).Address Then
                        ws.HPageBreaks.Add Before:=.Location.MergeArea.Cells(1, 1)
                    End If
                End If
            End With
        Next iHPCnt
    Else
        MsgBox "Bei Druckbereichen müssen Sie" & vbCrLf & _
               "die Seitenumbrüche manuell setzen!", vbExclamation, "Druckbereich"
    End If
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowInsertingHyperlinks:=True
End Sub
Gruess Hansueli


  

Betrifft: Ultimative Lösung? von: EtoPHG
Geschrieben am: 16.09.2015 09:12:14

Hallo Thomas,

Noch mein letzte Verbesserung. Ich habe festgestellt, das im Durchschnitte 3 Durchläufe nötig sind, damit alle Umbrüche richtig gestellt sind. Dieser Code macht soviele wie nötig.

Sub Schaltfläche1_BeiKlick()
    Dim ws As Worksheet
    Dim iHPCnt As Integer
    Dim bMoved As Boolean
    ' Execute only for Active Worksheet
    Set ws = ActiveSheet
    If ActiveSheet.Type <> xlWorksheet Then Exit Sub
    ' Unprotect
    ws.Unprotect
    ' Fit rows to content
    ws.UsedRange.Rows.AutoFit
    ' do only for entire sheet not for printareas
    If ws.PageSetup.PrintArea = "" Then
            ws.Cells(1, 1).SpecialCells(xlLastCell).Select
    ' reset all page breaks
        ws.ResetAllPageBreaks
        ' Loop until all Pagebreaks are at Mergedcell-boundries
        Do
            bMoved = False
            ' Do for all horizontal pagebreaks
            For iHPCnt = 1 To ws.HPageBreaks.Count
                With ws.HPageBreaks(iHPCnt)
                    If .Type = xlPageBreakAutomatic Then
                        ' if broken within merged area move pagebreak up to boundry
                        If .Location.Address <> .Location.MergeArea.Cells(1, 1).Address Then
                            ws.HPageBreaks.Add Before:=.Location.MergeArea.Cells(1, 1)
                            bMoved = True
                        End If
                    End If
                End With
            Next iHPCnt
        Loop While bMoved
    Else
        MsgBox "Bei Druckbereichen müssen Sie" & vbCrLf & _
               "die Seitenumbrüche manuell setzen!", vbExclamation, "Druckbereich"
    End If
    ' protect sheet again
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowInsertingHyperlinks:=True
End Sub
Ich hoffe damit sind diese Probleme aus der Welt ;-)

Gruess Hansueli


  

Betrifft: AW: Ultimative Lösung? von: thomas
Geschrieben am: 17.09.2015 17:27:35

Hallo Hansueli,
habe deine ultimative Lösung benutzt. Habe meine Tabelle nochmal ganz von vorne aufgebaut. Alles funktioniert sehr zuverlässig... ...bis ich die erste Zeile als Titel auf jedem Blatt wiederholen lasse. Das hätte ich Dir wohl besser gleich sagen sollen. Ist ja klar, dass auch das Einfluss auf den Umbruch hat

Gruss Thomas


  

Betrifft: AW: seitenumbruch steuern von: fcs
Geschrieben am: 11.09.2015 15:11:14

Hallo Thomas,

ich würde mit 2 Tabellenblättern arbeiten.
Blatt 1: Eingabeblatt jeweils 3 Zeilen pro Tag, Datums-Spalte ist schon ausgfüllt für größeren Zeitraum


Blatt 2: 2-Wochenblatt, der Starttag wird vorgegeben, der Rest per Formeln aus dem Blatt 1 geholt.

Bei jeweils 2 Zeilen Text pro Raum für Vormittags, Nachmittags und Abends sollte das klappen.

Gruß
Franz

Beispieldatei: https://www.herber.de/bbs/user/100140.xlsx


  

Betrifft: AW: seitenumbruch steuern von: thomas
Geschrieben am: 12.09.2015 12:25:28

Danke für den Tip mit den 2 Tabellenblättern. Das entkrampft die ganze Sache etwas, weil ich nicht mehr den Kompromiss zwischen guter Bildschirm-usability und übersichtlichen DIN-A4-Ausdruck suchen muss.
Leider komme ich nicht statisch mit 3x2 zeilen hin. die einträge sind sehr unterschiedlich lang. so dass sich immer noch umbrüche mitten durch einen tag ergeben.
Trotzdem Dank für die Anregung und die Mühe
Thomas


 

Beiträge aus den Excel-Beispielen zum Thema "seitenumbruch steuern"