Microsoft Excel

Herbers Excel/VBA-Archiv

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

Buchhalternase zeichnen mit vba... Osterei


Betrifft: Buchhalternase zeichnen mit vba... Osterei von: Berni
Geschrieben am: 14.04.2017 19:45:47

Hallo vba Freaks,
ich habe noch Excel 2010, aber bald Excel 2016 64 bit.
Für beide Versionen möchte ich eine Buchhalternase in mein kleines Kassenbuch (jeden Monat neues Kassenbuch Tabellenblatt in neuem xlxm file) via vba programmieren (ich weiss nicht, ob es für die neue Excel Version einen anderen Code braucht).

Bisher habe ich testweise nur ein Makro aufgezeichnet, was einen waagrechten Strich mit Hilfe der Formen einzeichnet (irgendwo im file). Ich habe keine Ahnung von Linien mit vba :-(
Die Linienlänge (3.Wert im Makro = 213.5) des Makros passt für die beiden waagrechten Linien immerhin schon mal...
Aber das löst mein Problem nicht, da die Buchhalternase in Form und Position abhängig von der letzten bebuchten Buchungszeile ist, und nicht starr von vorselektierten Koordinaten.

Das Kassenbuch.xlxm besteht aus 8 gleichartigen, untereinander positionierten, mit Rahmen gezeichneten Kassenblättern (alle in einem Tabellenblatt). Dazu noch 2 weitere Tabellenblätter und vba Code (aber das nur am Rande).
Jedes Kassenblatt hat je 45 Buchungszeilen und je 7 Kopfzeilen darüber, und jeweils 3 Summenzeilen (Fusszeilen) darunter. 3 Leerzeilen trennen zusätzlich die Blätter voneinander (optisch besser).
Kassenblatt 1 beginnt in Excel Zeile 1, erste Buchungszeile (Blatt 1) ist wegen Kopfzeilen Excel Zeile 8.
Letzte mögliche Buchungszeile Nr. 360 ist Excel Zeile 458. Plus 3 Fusszeilen = bei Excel Zeile 461 ist das File zu Ende.
Kopf- und Fusszeilen sind unterschiedlich hoch, Buchungszeilen alle gleich hoch.
Die (unterschiedlich breiten) Spalten gehen von B bis Q, Spalte A ist Leerspalte.

Die Buchhalternase soll aus 2 gleich langen, waagrechten, blauen Linien (Excel Standard blau) der Dicke "2" bestehen, und einer gleichartigen diagonalen Verbindungslinie (fallweise unterschiedlicher Länge und Steigung natürlich).

Die 1. waagrechte Linie über die Spalten C (Einzahlungen, enthält Werte oder leer) und D (Auszahlungen, enthält Werte oder leer) und E (Bestand, Formel mit Null oder ungleich Null). Nur C ODER D einer Zeile kann Werte enthalten, oder beide leer (falls keine Buchungserfassung in dieser Zeile)... dann ist E auch gleich Null.
Ausrichtung der Linie: linksbündig in C, bis Mitte Spalte E, zellenmittig von der Höhe her.
Diese Linie wird ausschliesslich in die LETZTE VORHANDENE und zugleich LEERE Buchungszeile des LETZTEN mit Buchungen versehenen Blatts positioniert, also immer unten links im Kassenbuchblatt. Frühestens also Zeile 52, falls nur Blatt 1 Buchungen enthält. Oder Zeile 110, falls nur Blatt 1 und Blatt 2 Buchungen enthalten... oder dann jeweils 58 Zeilen (45 Buchungszeilen + 7 Kopfzeilen + 3 Fusszeilen + 3 Leerzeilen) tiefer, bis das erste unbebuchte Blatt gefunden wird.

Die 2. waagrechte Linie (gleich lang, dick und gefärbt) kommt in Spalten N,O,P,Q (rechtsbündig in Q bis Mitte N; Höhe dto. zellenmittig). Und zwar DIREKT UNTER die LETZTE BEBUCHTE Buchungszeile (falls diese nicht zugleich die letzte VORHANDENE Buchungszeile des betreffenden Kassenblatts ist, dann gibts nämlich keine Buchhalternase) des SELBEN Kassenblatts, wobei Spalte Q den USt.-Satz enthält (Formel), O und P in dieser Version leer sind (Kostenträger und Skonto), und N eine Formel für die Kostenstelle enthält.
Also befindet sich die 2. Linie immer relativ weit oben rechts in den Buchungszeilen des Kassenbuchblatts.

Eine 3. Linie (Verbindungslinie; gleiches Blau, Dicke "2") soll nun die beiden waagrechten Linien punktgenau verbinden (einrastern): vom rechten Endpunkt der waagrechten Linie unten links bis zum linken Endpunkt der waagrechten Linie oben rechts.
Diese Diagonale ist natürlich unterschiedlich lang und schräg relativ zur Horizontalen, je nachdem, in welcher Zeile der letzte Buchungssatz steht.

Im 1. Sonderfall, dass die betreffende 44. Buchungszeile (vorletzte vorhandene) des jeweiligen Kassenblatts als letztes bebucht wurde, ist die Diagonale (und mithin die ganze "Nase") eine waagrechte Linie in Excel Zeile 52 (= Buchungszeile 45) oder ggf. Excel Zeile 110 (= Buchungszeile 90) oder Excel Zeile 168 usw., abhängig vom Kassenblatt...
Im 2. Sonderfall, dass die betreffende 45. Buchungszeile (letzte vorhandene) des jeweiligen Kassenblatts (letzte mögliche Buchungszeile des betreffenden Kassenblatts) als letztes bebucht wurde, gibt es keine Buchhalternase, da es kein unvollständig bebuchtes Kassenblatt gibt.

In Pseudo-Code (Computerchinesisch) könnte ich mir die todo Liste für das vba in etwa so vorstellen:

1. Ermittle die LETZTE BEBUCHTE Buchungszeile des gesamten Kassenbuchs (nennen wir sie x, gemessen in Excel Zeilen), z.B. indem du feststellst, ob die Formel in Spalte E (= Bestand) dieser Zeile einen Wert gleich Null enthält. Triffst du auf den ersten Nullwert, so ist die Zeile davor die gesuchte Zeile x (Bestand ungleich Null = bebucht). Gehe hierzu alle Zeilen ab Excel Zeile 8 von Spalte E des Kassenbuchs (= Tabellenblatts) von oben nach unten durch. Triffst du dabei in Spalte E auf keine Formel, sondern auf Zeilen, die Text enthalten (= z.B. auf die Überschrift "Bestand" bzw. die Unterschriften "Summe", "gesamt" etc.), dann ignorierst du das... und suchst weiter im nächsten Kassenblatt, Spalte E.

2. Nach deren Ermittlung gehe eine Zeile tiefer ( = ERSTE UNbebuchte Zeile, nennen wir sie x+1, auch in Excel Zeilen). Triffst du dabei wiederum in Spalte E auf keine Formel, sondern auf den Text "Summe" (= 1. Zeile mit Text unterhalb der letzten vorhandenen Buchungszeile) , brich das vba ab: die letzte BEBUCHTE Zeile ist in diesem Falle zugleich die letzte VORHANDENE Buchungszeile des betreffenden Blatts, und es gibt keine Buchhalternase (Sonderfall 2).

3. Andernfalls gehe in die Spalten N bis Q dieser Zeile (x+1).
Zeichne hier rechtsbündig an Q, in Höhe der Zellenmitte eine waagrechte Linie der Dicke 2 in Excel Standard blau und der Länge (ca. lt. obigem Makro, aber vielleicht gibts auch noch andere Bemassungen?)) von 213.5 ein.

4. Ermittle nun die letzte VORHANDENE Buchungszeile des betreffenden Kassenblattes (die ja nach obiger Prüfung leer ist, nennen wir sie y, in Excel Zeilen), z.B. nach folgendem Algorithmus:
Die nun folgenden Ziffern beziehen sich wie gehabt auf die Excel Zeilen Nr.:

Wenn x kleiner 52, dann y = 52.
Wenn x = 52, dann vba Abbruch (aber wohl redundant mit 2., 2. Satz ;-)

Dann für n = 1 bis 7 (immer um eine Ganzzahl erhöhen, d.h., 7 mal durchrechnen):
Wenn 52 kleiner x kleiner 52 + (n*58), dann y = 52 + (n*58)
Wenn x = 52 + (n*58), dann vba Abbruch (aber wohl redundant mit 2., 2. Satz)

5. Gehe nun in die Spalten C bis E dieser Zeile y.
Zeichne hier linksbündig an C, in Höhe der Zellenmitte eine waagrechte Linie der Dicke 2 in Excel Standard blau und der Länge (ca. lt. obigem Makro, aber vielleicht gibts auch noch andere Bemassungen?) von 213.5 ein.

6. Verbinde die beiden waagrechten Linien mit einer diagonalen Linien gleicher Dicke und Farbe an den Eckpunkten wie folgt:
Rechter Eckpunkt waagrechte Linie unten links mit linkem Eckpunkt waagrechte linie oben rechts (im Sonderfall 1 liegen beide waagrechten Linien in einer Zeile, und die Diagonale wird ebenfalls eine Waagrechte).

Der Code soll in bereits vorhandenen Code in Modul 1 (Private Sub Sperren()) integriert werden, welcher das Kassenbuch am Schluss auch noch sperrt und schützt... und zwar zwischen die Code-Zeilen "MsgBox tralala..." (Sicherheitsabfrage vor dem Sperren) und "Range("A1:w500").Locked = True ' alle Zellen sperren".

Kennt sich da wer aus?

Vielen Dank für Eure Hilfe bei der Suche nach meinem Osterei ;-)

Berni

  

Betrifft: AW: Buchhalternase zeichnen mit vba... Osterei von: ransi
Geschrieben am: 15.04.2017 10:09:00

HAllo Berni,

Deine Beschreibung ist reichlich wirr...
Hier ist ein Code der dir eine Buchalternase zeichnet.
Die Function gehört in einModul und wird aufgerufen wie jede andere Funktion auch.
Die Function erwartet 2 Argumente. Die Startzelle und die Zielzelle.

' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit




Public Function Buchhalternase(Startzelle As Range, Endzelle As Range)
    Dim Punkte(1 To 4, 1 To 2) As Single
    Dim Linie1 As Shape
    On Error Resume Next
    Startzelle.Parent.Shapes("Buchhalternase" & Application.ThisCell.Address(0, 0)).Delete
    With Startzelle
        Punkte(1, 1) = .Left
        Punkte(1, 2) = .Top + .Height / 2
        Punkte(2, 1) = .Left + .Width
        Punkte(2, 2) = .Top + .Height / 2
    End With
    With Endzelle
        Punkte(3, 1) = .Left
        Punkte(3, 2) = .Top + .Height / 2
        Punkte(4, 1) = .Left + .Width
        Punkte(4, 2) = .Top + .Height / 2
    End With
    Set Linie1 = Startzelle.Parent.Shapes.AddPolyline(Punkte)
    Linie1.Name = "Buchhalternase" & Application.ThisCell.Address(0, 0)
    If Err = 0 Then
        Buchhalternase = 1
        Else:
        Buchhalternase = 0
    End If
End Function



Ist "mit der heißen Nadel gestrickt."
Also ohne Feintuning und vernünftige Fehlerroutine.
ICh hoffe du kannst das auf deine Bedürfnisse anpassen.

ransi


  

Betrifft: Verbundene Zellen mit Diagonale geht auch owT von: lupo1
Geschrieben am: 15.04.2017 21:53:56




  

Betrifft: AW: Verbundene Zellen mit Diagonale geht auch owT von: Berni
Geschrieben am: 17.04.2017 11:56:44

Hallo ransi,

ja, das war bissl kompliziert formuliert ;-)

Vielen Dank für Deine Hilfe, er zeichnet die Buchhalternase perfekt.
Mit bissl "wenn" Gefummel in der Formel, malt er sie auch dort rein, wo sie hingehört.

Wie kann ich die Farbe und Dicke der Linie(n) noch verändern?

Hab in deiner Funktion mit with linie1 .weight rum experimentiert, aber das funzt nicht :-(

Nochmals vielen Dank für deine Unterstützung!

Berni


  

Betrifft: AW: Verbundene Zellen mit Diagonale geht auch owT von: ransi
Geschrieben am: 17.04.2017 13:24:55

Hallo Berni,

Das kannst du in der Line-Eigenschaft des Shapes einstellen.
Setz in Zeile 95 mal einen Haltepunkt und schau dir im Lokalfenster die Eigenschaften von Linie1 an.

Option Explicit

Public Function Buchhalternase(Startzelle As Range, Endzelle As Range)
5 Dim Punkte(1 To 4, 1 To 2) As Single
10 Dim Linie1 As Shape
15 On Error Resume Next
20 Startzelle.Parent.Shapes("Buchhalternase" & Application.ThisCell.Address(0, 0)).Delete
25 Err.Clear
30 With Startzelle
35    Punkte(1, 1) = .Left
40    Punkte(1, 2) = .Top + .Height / 2
45    Punkte(2, 1) = .Left + .Width
50    Punkte(2, 2) = .Top + .Height / 2
55 End With
60 With Endzelle
65    Punkte(3, 1) = .Left
70    Punkte(3, 2) = .Top + .Height / 2
75    Punkte(4, 1) = .Left + .Width
80    Punkte(4, 2) = .Top + .Height / 2
85 End With
90 Set Linie1 = Startzelle.Parent.Shapes.AddPolyline(Punkte)
95 With Linie1
100   .Name = "Buchhalternase" & Application.ThisCell.Address(0, 0)
105   .Line.ForeColor.RGB = RGB(0, 0, 0)
110   .Line.Weight = 3
115 End With
120 If Err = 0 Then
125   Buchhalternase = 1
130   Else:
135   Buchhalternase = 0
140 End If

End Function

ransi


  

Betrifft: AW: Verbundene Zellen mit Diagonale geht auch owT von: Berni
Geschrieben am: 17.04.2017 20:33:26

Hallo ransi,

jetzt läufts prima, die "Nase" schaut hübsch aus, besten Dank nochmals! :-)

Mein Fehler war, auf deine "alte" Zeile 95 folgendes zu schreiben:
95 (alt): With Linie1.Name = "Buchhalternase" & Application.ThisCell.Address(0, 0)
100: With Linie1
105: .Weight = 3
110: End With

Das hat er nicht geschluckt, warum auch immer...

Aber problem solved :-)

Lieben Dank und schönen Abend noch,
Berni


  

Betrifft: AW: Buchhalternase zeichnen mit vba... Osterei von: Berni
Geschrieben am: 18.04.2017 00:05:18

Hallo ransi,

kann ich diese public function buchhalternase auch aus einem Private sub heraus aufrufen?

Problem ist, dass ich mit einem vba mit intersect target.locked und activesheet.protect jede Zelle nach einmaliger Daten-Erfassung sperre (steuerliche Vorschrift).
Es steht unter MS Excel Objekte, diese Arbeitsmappe, Tabelle1 (Kassenbuch).... also nicht unter "Module".
Sobald dieses vba "scharf" ist (und das ists im laufenden Buchungs-Betrieb immer), sperrt und schützt es mir das Kassenbuch, noch bevor die Buchhalternase eingezeichnet werden kann :-(
Und in der Zelle mit der function Buchhalternase steht Null (statt 1, wenn die Nase bei "Entschärfung" dieses vba's korrekt gezeichnet wird).
Es lässt also deine buchhalternasen Funktion, die ich mit ja, nein Listenfeld aufrufen wollte nach Buchungsende, nicht rechnen.... ist wohl zu schnell....

In Modul1 steht noch ein weiteres vba, was den o.g. target.locked Dauer-Blattschutz mit activesheet.unprotect noch mal aufhebt (Private sub), bevor das gesamte Kassenbuch nach einer Msgbox Abfrage mit ok komplett und endgültig gesperrt wird (Aufruf Modul1 über click Button, sobald Buchungserfassung zu Ende).

Deine Buchhalternase function steht bei mir in Modul5.

Frage: Kann ich Modul5 in Modul1 einbauen, so à la call.buchhalternase oder call.modul5 o.ä.... z.B. vor die Msgbox Abfrage?
Ziel ist es, deine Buchhalternasen Funktion im ungeschützten Zustand überhaupt "zum Zuge" kommen zu lassen...

Ich danke dir für deine Hilfe!

Berni


Beiträge aus den Excel-Beispielen zum Thema "Buchhalternase zeichnen mit vba... Osterei"