Excel, Zeilenhöhe abhängig von Cursorposition

[wabe60]

Win7, Excel 2010/2013
Habe eine Tabelle, die in mehreren Zellen einer Spalte jeweils mehrzeilige Einträge enthalten (mit Zeilenumbruch), teilweise bis zu 8 Zeilen pro Zelle. Damit die Übersicht der Tabelle gewahrt bleibt möchte ich die generelle Zeilenhöhe fixieren, so dass jeweils nur etwa die ersten 3 Zeilen einer Zelle sichtbar sind. Nun wäre es natürlich wünschenswert, dass sich die Zeilenhöhe bei der jeweiligen Cursorposition ausweitet, so dass die gesamten Zelleinträge auf dieser Zeile sichtbar sind. Wenn der Cursor dann wir nach oben oder unten gescrollt wird, müsste die Zeilenhöhe dann wieder auf den ursprünglichen Wert "einklappen".
Ist dies mittels einer bedingten Formatierung oder mit einem Makro machbar?

 

[weer]

Ist dies mittels einer bedingten Formatierung oder mit einem Makro machbar?

Salü Wabe

Mit bedingter Formatierung sehe ich keine Lösung. Mit einem Makro geht es (bei mir) so:

Dim lzadr   ' Adresse der zuletzt angewählten Zelle
Const ZHoehe = 28.5  ' Höhe einer "normalen" Zeile

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

Range(lzadr).RowHeight = ZHoehe
zadr = Selection.Address

If Target.Column = 2 Then
   Selection.EntireRow.AutoFit
End If

If Selection.RowHeight < ZHoehe Then
    Selection.RowHeight = ZHoehe
End If

lzadr = zadr
End Sub

Es handelt sich um eine Ereignisprozedur. Eine solche muss unter den Excel Objekten in eine Tabelle eingefügt werden - nicht in ein Modul! Gaby Salvisberg hat das in einem früheren PCTipp einmal so beschrieben:

Öffnen Sie den VisualBasic Editor, indem Sie Alt+F11 drücken. Klappen Sie in der linken Fensterhälfte allenfalls «Microsoft Excel Objekte» auf und doppelklicken Sie den Namen der betroffenen Tabelle (z.B. «Tabelle1»). Damit wendet sich das Makro ausdrücklich in jener Tabelle an. - Kopieren Sie den Makro-Code hinein.

Wichtig ist: Die beiden Zeilen
Dim lzadr ' Adresse der zuletzt angewählten Zelle
Const ZHoehe = 28.5 ' Höhe einer "normalen" Zeile
müssen zwingend zuoberst im Code-Bereich plaziert sein!

Im obigen Makro habe ich eine Einschränkung eingebaut: Es gilt nur für die Spalte B (If Target.Column = 2). Und zwar aus folgendem Grund: Da jeweils die Zeilenhöhe irgendeiner Zelle in einer bestimmten Zeile massgebend ist, würde das Arbeiten in "normalen" Feldern sehr mühsam. Selbstverständlich kann das Makro angepasst werden, dass es für andere oder für mehrere oder alle Spalten gilt.

Ganz oben im Makro habe ich unter ZHoehe einer "normalen" Zelle mit 28.5 Punkten definiert. Diese Zahl musst Du Deiner "Standard"-Zeilenhöhe entsprechend anpassen.

Wie gesagt, bei mir funkt das (zu meiner eigenen Ueberraschung) bestens. Ich hoffe, auch bei Dir!
Niclaus

 

[wabe60]

Ereignisprozedur

Hallo weer
Danke für deine Anleitung. Bei mir tut sich aber überhaupt nichts in dieser Richtung. Denke aber, dass ich schon mal grundsätzlich etwas falsch mache (kenne mich mit VBA nicht aus). Nachdem ich alles nach Anleitung erstellt habe, "speichern" in VBA-Editor, zurück zu Excel-Tabelle, hier auch "speichern". Wie gesagt, es tut sich gar nichts. Wenn ich Excel schliesse, dann wieder starte und auf VBA-Editor gehe, ist überhaupt keine Ereignisprozedur vorhanden. Muss ich irgendwo noch etwas aktivieren, damit dieses Makro ausgeführt und auch hinterlegt wird?
Für weitere Hilfestellung wäre ich dankbar.
Gruss Walter

 

[weer]

Hallo weer
Danke für deine Anleitung. Bei mir tut sich aber überhaupt nichts in dieser Richtung. Denke aber, dass ich schon mal grundsätzlich etwas falsch mache (kenne mich mit VBA nicht aus). Nachdem ich alles nach Anleitung erstellt habe, "speichern" in VBA-Editor, zurück zu Excel-Tabelle, hier auch "speichern". Wie gesagt, es tut sich gar nichts. Wenn ich Excel schliesse, dann wieder starte und auf VBA-Editor gehe, ist überhaupt keine Ereignisprozedur vorhanden. Muss ich irgendwo noch etwas aktivieren, damit dieses Makro ausgeführt und auch hinterlegt wird?
Für weitere Hilfestellung wäre ich dankbar.
Gruss Walter

Salü Walter

Seltsam! Zum Speichern: Du musst die Datei als XLSM speichern (Excel-Mappe mit Makros), nicht als "normale" XLSX. – Welche Excel-Version hast Du? – Am besten speicherst Du Deine Excel-Datei als allererstes so ab, bevor Du irgendetwas anderes machst.

Ob Du noch etwas aktivieren musst, fragst Du. Die Makrosicherheit muss so eingestellt sein, dass sie Makros zulässt. – Ob im VBA-Editor auch noch etwas eingestellt werden muss, weiss ich nicht. Möglicherweise im Menü Extras / Verweise / "Visual Basic for Applications" aktivieren.

Ich lade Dir eine PDF-Datei mit dem PrintScreen gemäss Anleitung von Gaby Salvisberg hoch.

https://drive.google.com/file/d/0B0pOZasARSo2ZFd3UzdvRFBRc3c/view?usp=sharing

Auf drive.google.com erscheint eine Vorschau. Herunterladen kannst Du die Datei mit dem Download-Pfeil im schwarzen Bereich über der Vorschau. – Leider kann ich hier im PCTipp nichts mehr hochladen. Die wollen nichts mehr von mir!

Falls das mit dem VBA-Editor klappen sollte: Mein Makro gilt nur für die eine Tabelle und dort nur für die Spalte B! So wie ich es oben beschrieben habe.

Schau mal, ob Du damit weiter kommst! Ich wünsche viel Erfolg
Niclaus

 

[slup]

...Leider kann ich hier im PCTipp nichts mehr hochladen. Die wollen nichts mehr von mir!...

Natürlich wollen wir was von dir! User haben nur beschränkten Platz für Anhänge zur Verfügung. Gehe zu 'Aktionen' - 'Profil bearbeiten' - 'Einstellungen' - 'Anhänge' und markiere die verzichtbaren (alten) Anhänge, die du dann löschen kannst. Dann hat es wieder Platz;)

 

[weer]

Natürlich wollen wir was von dir! User haben nur beschränkten Platz für Anhänge zur Verfügung. Gehe zu 'Aktionen' - 'Profil bearbeiten' - 'Einstellungen' - 'Anhänge' und markiere die verzichtbaren (alten) Anhänge, die du dann löschen kannst. Dann hat es wieder Platz;)

Salü Slup

Danke für den Hinweis. Ich habe es hier unten auf dieser Seite unter "Anhänge verwalten" gesucht und sogar die Hilfe zu Hilfe genommen - ohne Erfolg. Ein entsprechender Hinweis hier unten auf der Seite unter "Anhänge verwalten" wäre nicht schlecht. Denn mein Gedächtnis ist nicht mehr so, dass ich mich an Aktionen erinnern werde.

Grüsse Niclaus

 

[wabe60]

Ereignisprozedur

Hallo Niclaus
Bei meiner bestehenden Tabelle wollte das Ganze einfach nicht klappen. Jetzt habe ich das ganze mit einer neuen Tabelle nochmals durchgespielt - klappt bestens. Und auf jeden Fall besten Dank für deine Hilfe.
Nun stosse ich aber auf das nächste Hindernis: deine Einschränkung auf nur Spalte B habe ich rausgenommen, soweit sogut. Bei meiner Anwendung sollten die obersten 11 Zeilen eine konstante Höhe von 15 haben, dies sind quasi die Kopfzeilen der Tabelle. Ab Zeile 11 sollte dann die "normale" Zeilenhöhe 35 betragen und die Ereignisprozedur sollte nur auf diesem unteren Block zu Anwendung kommen. Kann ich irgendwie den Befehl ["Const ZHoehe = 35 ' Höhe einer "normalen" Zeile"] auf einen bestimmten Bereich der Tabelle beschränken??
Danke und Gruss, Walter

 

[weer]

Hallo Niclaus
Bei meiner bestehenden Tabelle wollte das Ganze einfach nicht klappen. Jetzt habe ich das ganze mit einer neuen Tabelle nochmals durchgespielt - klappt bestens.

Grüezi Walter

Vielleicht war das der Grund: Wenn die Tabellen andere Namen tragen als "Tabelle1" usw. kann es im VBA-Editor zu Verwechslungen kommen. Das hätte ich besser erklären sollen.

Bei meiner Anwendung sollten die obersten 11 Zeilen eine konstante Höhe von 15 haben, dies sind quasi die Kopfzeilen der Tabelle. Ab Zeile 11 sollte dann die "normale" Zeilenhöhe 35 betragen und die Ereignisprozedur sollte nur auf diesem unteren Block zu Anwendung kommen. Kann ich irgendwie den Befehl ["Const ZHoehe = 35 ' Höhe einer "normalen" Zeile"] auf einen bestimmten Bereich der Tabelle beschränken??

Genau:) Und zwar so:

Dim lzadr   ' Adresse der zuletzt angewählten Zelle
Const ZHoehe = 35  ' Höhe einer "normalen" Zeile


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

If Target.Row <= 11 Then GoTo weiter1

Selection.EntireRow.AutoFit
Range(lzadr).RowHeight = ZHoehe
zadr = Selection.Address

If Selection.RowHeight < ZHoehe Then Selection.RowHeight = ZHoehe
lzadr = zadr
Exit Sub

weiter1:
Range(lzadr).RowHeight = ZHoehe
End Sub

Das Oeffnen der Zellen funktioniert nicht immer so, wie man es gerne hätte. Deshalb habe ich noch ein zweites Makro gebastelt: Wenn Du eine Zelle anwählst und die öffnet sich nicht, dann mach einen Doppelklick auf diese Zelle. Mit Doppelklick kannst Du eine markierte offene Zelle auch schliessen.

Das folgende Makro kannst Du ganz einfach an der gleichen Stelle unter das bisherige einfügen.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row <= 11 Then Exit Sub
On Error Resume Next

If Selection.RowHeight <> ZHoehe Then
    Selection.RowHeight = ZHoehe
Else
   Selection.EntireRow.AutoFit
   If Selection.RowHeight < ZHoehe Then Selection.RowHeight = ZHoehe
End If
Cancel = True
End Sub

Ich hoffe, das klappt Deinen Wünschen gemäss, sonst melde Dich wieder.
Viele Grüsse Niclaus

 

[wabe60]

Ereignisprozedur - VBA spielt verrückt

Hallo Niclaus
Das Makro von dir funktioniert grundsätzlich ausgezeichnet. Besten Dank für deine Hilfe.
Nach wie vor (oder immer mehr) habe ich jedoch grosse Probleme mit dem VBA-Editor. Habe jetzt schon zig Beispieltabellen mit deinem Makro kreiert (mit deinen empfohlenen Einstellungen und abgespeichert als .xlsm). Ich speichere das Makro, speichere dann die Tabelle - beim erneuten Öffnen der Tabelle ist das Makro weg. Bei anderen Fällen ist beim Wiederöffnen das Makro zwar im VBA-Editor noch vorhanden, es ist aber irgenwie gar nicht aktiv (daran zu erkennen, dass die durchgezogene Trennlinie unterhalb der zweiten Makrozeile fehlt, s. auch Screenshot von Gaby Salvisberg). In zwei Tagen ist mir bisher eine einzige Mustertabelle gelungen, bei der das Makro erhalten bleibt und auch funktioniert (hier ist die erwähnte Trennlinie auch tatsächlich vorhanden). Diese verschiedenen Szenarien spielen sich beim Erstellen von neuen Tabellen mit Makros rein zufällig und ohne erkennbares Muster ab - obbwohl ich immer mit den selben Einstellungen arbeite. Habe die diversen Tabellen auch auf einem anderen PC ausprobiert - mit demselben Ergebnis. VBA scheint je nach Lust einmal zu funktionieren, dann wieder nicht.
Ist dieses Phänomen jemandem bekannt?
Gruss Walter

 

[weer]

VBA spielt verrückt

Hallo Niclaus
Nach wie vor (oder immer mehr) habe ich jedoch grosse Probleme mit dem VBA-Editor ...
VBA scheint je nach Lust einmal zu funktionieren, dann wieder nicht.
Ist dieses Phänomen jemandem bekannt?
Gruss Walter

Salü Walter

Ich habe keine Ahnung, woran das liegen könnte. Mit Google findet man zwar Hinweise, dass Makros überhaupt nicht funktionieren, z. B.
http://www.herber.de/forum/archiv/1400to1404/1401035_Makros_funktionieren_nicht_mehr.html
Aber dass es mal ok ist und dann wieder nicht, dazu habe ich nichts gefunden und selber auch noch nie erlebt.

Tut mir leid! Viele Grüsse Niclaus

 

[nochEinAndreas]

Salü Walter

Aber dass es mal ok ist und dann wieder nicht, dazu habe ich nichts gefunden und selber auch noch nie erlebt.

Hallo Niclaus,

ich habe jetzt nicht den ganzen Faden verfolgt. Aber könnte es sein, dass während der Ausführung eines Makros

EnableEvents = False

gesetzt wird? Wenn der Makro dann abstürzt, bleibt das evtl. so, und Ereignisprozeduren werden kommentarlos nicht mehr gestartet.
Ich habe mir bei so einer Gelegenheit auch schon mal einen Wolf gesucht.

Grüße, Andreas

 

[weer]

ich habe jetzt nicht den ganzen Faden verfolgt. Aber könnte es sein, dass während der Ausführung eines Makros

EnableEvents = False

gesetzt wird? Wenn der Makro dann abstürzt, bleibt das evtl. so, und Ereignisprozeduren werden kommentarlos nicht mehr gestartet.
Ich habe mir bei so einer Gelegenheit auch schon mal einen Wolf gesucht.

Hallo Andreas

Eingesetzt ist in den Makros "EnableEvents = False/True" nicht. Ich überlege mir, ob man es einsetzen müsste. Bei mir in Excel 2013 klappt alles ohne diese Zeilen.

Eine Frage an Dich in diesem Zusammenhang wegen den Einstellungen im VBA-Editor im Menü Extras / Verweise. Was muss dort alles aktiviert sein, damit die Makros funktionieren? - Ich habe mir (gefühlt im letzten Jahrtausend) notiert, man müsse nur "Visual Basic For Applications" aktivieren. Müssen da noch weitere Verweise aktiviert sein?

Vielen Dank für Deine Hilfe und herzliche Grüsse
Niclaus

 

[nochEinAndreas]

Weitere Verweise nötig?

Hallo Andreas
Eine Frage an Dich in diesem Zusammenhang wegen den Einstellungen im VBA-Editor im Menü Extras / Verweise. Was muss dort alles aktiviert sein, damit die Makros funktionieren? - Ich habe mir (gefühlt im letzten Jahrtausend) notiert, man müsse nur "Visual Basic For Applications" aktivieren. Müssen da noch weitere Verweise aktiviert sein?

Vielen Dank für Deine Hilfe und herzliche Grüsse
Niclaus

'nabend Niclaus,

ich hab jetzt noch mal den kompletten Faden gelesen. Du hast ja in deinen Makros nur Excel-interne Befehle benutzt. Dazu brauchst du eigentlich nichts weiter zu aktivieren. Ich hatte ab und zu den Fall, wenn ich z.B. auf einer UserForm einen Fortschrittsbalken einfügen wollte, oder wenn ich per "Regular Expressions" irgendwelche Dateinamen in Verzeichnisbäumen suchen wollte. Dann mussten weitere Verweise aktiviert werden. Aber in eurem Fall müsste da alles ohne weiteres funktionieren.
Ich hatte neulich bei uns in der Firma mal den Fall, dass nach einem bestimmten Windows-Update plötzlich einfache Funktionen wie Left, Right oder Mid nicht mehr gefunden wurden. Da musste ich mir dann mit VBA.Left o.ä behelfen. Aber da trat ja auch jedes Mal eine Fehlermeldung auf. So wie das hier bei euch beiden ist, dass etwas zufallsmäßig mal funtioniert und mal nicht, ganz ohne Fehlermeldunhg, habe ich auch noch nicht erlebt.
Sorry, dass ich momentan auch nicht weiter helfen kann.
Schön, dich hier wieder gefunden zu haben.

Liebe Grüße,
Andreas

 

[nochEinAndreas]

Update

Salü Niclaus,

ich hab im meinem Excel (2010) gerade noch mal nachgeschaut. Standardmäßig ist dort Folgendes aktiviert:

Visual Basic for Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library

Grüße, Andreas

 

[wabe60]

Hallo zusammen
Die von Andreas beschriebenen Einstellungen sind bei mir auch aktiviert. Warum die Makro-Codes öfters nicht aktiviert werden, ist mir nach wie vor schleierhaft.
@Niclaus: nun aber nochmals zurück zur eigentlichen Aufgabenstellung. Meine Tabelle hat sehr viele (über 1'000) und eher breite Spalten. Für eine Optimierung des "Aufklappens" von den Zeilen wäre es daher optimal, wenn jeweils nur die 5 Spalten links und rechts von der Cursorposition für die aktuelle Zeilenhöhe berücksichtigt würden. Zur Verdeutlichung ein Beispiel: die Zelle C13 enthält einen Texteintrag über 8 Zeilen (mittels Zeilenumbruch). Mein Cursur steht nun in Zelle AF13, und von den Zellen AA13 bis AK13 hat die grösste einen Eintrag über 5 Zeilen. Somit sollte sich nun die Höhe der Zeile 13 auf 5 Textzeilen einstellen (Spalte C ist derzeit nämlich am Bildschirm gar nicht sichtbar).
Wie würde der Makrocode für diese Anwendung aussehen?
schöne Grüsse, Walter

 

[weer]

Hallo zusammen
Die von Andreas beschriebenen Einstellungen sind bei mir auch aktiviert. Warum die Makro-Codes öfters nicht aktiviert werden, ist mir nach wie vor schleierhaft.

Grüezi Walter

Hast Du mal geschaut, ob die neuesten Updates für Dein Excel oder Office geladen sind?

nun aber nochmals zurück zur eigentlichen Aufgabenstellung. Meine Tabelle hat sehr viele (über 1'000) und eher breite Spalten. Für eine Optimierung des "Aufklappens" von den Zeilen wäre es daher optimal, wenn jeweils nur die 5 Spalten links und rechts von der Cursorposition für die aktuelle Zeilenhöhe berücksichtigt würden. Zur Verdeutlichung ein Beispiel: die Zelle C13 enthält einen Texteintrag über 8 Zeilen (mittels Zeilenumbruch). Mein Cursur steht nun in Zelle AF13, und von den Zellen AA13 bis AK13 hat die grösste einen Eintrag über 5 Zeilen. Somit sollte sich nun die Höhe der Zeile 13 auf 5 Textzeilen einstellen (Spalte C ist derzeit nämlich am Bildschirm gar nicht sichtbar).
Wie würde der Makrocode für diese Anwendung aussehen?

Damit habe ich von allem Anfang an gerechnet!! :o

Die bisherige Lösung arbeitet mit EntireRow.AutoFit: Die ganze Zeile wird so hoch angezeigt, wie die höchste Zelle an irgendeiner Stelle in dieser Zeile ist.

Du möchtest, dass nur die Höhe eines bestimmten Zellbereiches berücksichtig wird. Eine Grundsatzfrage 1: Du sprichst immer wieder von Zeilenumbruch. Meinst Du damit, dass man in einer Zelle mehrere Zeilen eingeben kann, indem man jeweils ALT-RETURN drückt, um eine neue Zeile zu bilden?

(Zeilenumbruch kann nämlich auch eine reine Zell-Formatierung bedeuten: Man kann einen langen Text in eine so formatiere Zelle eingeben. Dieser Text erscheint dann auch in mehreren Zeilen.)

Wenn JA, muss man nach meiner Ueberlegung die Anzahl Umbrüche (ALT-RETURN) in einer Zelle zählen. Das geschieht mit UBound(Split(Range("A1"), Chr(10))). Aber diesen Code so einzusetzen, dass er fünf Spalten rechts und links der angewählten Zelle anzeigt, dazu muss ich richtig hirnen und Du Dich gedulden!

Und eine Aufgabe für Dich: Du musst mir die Zeilenhöhen angeben. Die "normale" Zeilenhöhe ist bei Dir 35 Punkte (bei 3 Zeilen). Wie hoch ist die Zeilenhöhe bei 4 Zeilen, bei 5 Zeilen usw. bis bei 8 Zeilen.

Wenn NEIN: Dann fällt mir momentan keine Lösung ein! – Ausser: Eine Grundsatzfrage 2, die ich schon viel früher hätte stellen sollen: Soll/muss es bei der ganzen Prozedur möglich sein, dass man auch Eingaben oder Aenderungen machen kann in der jeweils angewählten Zelle? Oder würde ein "Bild" reichen, das den ganzen Zell-Inhalt zeigt, ohne dass man am Zell-Inhalt etwas ändern kann?

Und noch eine Frage 3: Klappt das mit dem Doppelklick? Es wäre einfacher, wenn man sich darauf beschränken könnte!

Wenn Du mir die drei Fragen beantwortest und die verschiedenen Zeilenhöhen angibst, kann ich weitermachen und hoffen, dass ich zu einem Ergebnis komme.

Viele Grüsse Niclaus

 

[nochEinAndreas]

Anregung für Niclaus

Hallo Niclaus,

nur als Anregung für dich. Wir wäre so etwas:?

Sub umbrueche()
    MsgBox zeilenZahl(Range("H1"))
End Sub

Function zeilenZahl(zelle As Range) As Long
    Dim sp As Long
    
    For sp = -5 To 5
        zeilenZahl = WorksheetFunction.Max(zeilenZahl, UBound(Split(zelle.Offset(0, sp), Chr(10))) + 1)
    Next sp
End Function

Gruß, Andreas

 

[wabe60]

Hallo Niclaus
Ja, mein Office und Excel halte ich stets auf dem neusten Stand. Ich vermute eher ein Problem bei einem Dienst, da ich in den meisten Fällen das Makro gar nicht speichern kann.

Zu deinen Fragen:
* Zeilenumbruch: sowohl als auch. D.h. alle Zellen sind als Typ "mit Zeilenumbruch" formatiert. Inhalte mit langem Text werden somit umgebrochen. Es gibt aber auch (oder mehrheitlich) Eingaben mit sanftem Umbruch (Alt-Return).
* Eingaben/Änderungen an der gewählten Zelle sind zwingend nötig
* derzeitige Zeilenhöhen mit Schrift Arial 10:
bei 3 Zeilen: 39.6
bei 4 Zeilen: 52.8
bei 5 Zeilen: 66.0
bei 6 Zeilen: 79.2
bei 7 Zeilen: 92.4
bei 8 Zeilen: 105.6
Die Zeilenhöhe habe ich anfänglich mit 35 gewählt, weil damit die Zeile nicht allzu hoch war und man bei nicht angewählter Zelle die angebrochene dritte Zeile noch halbwegs sehen konnte. Der Benutzer weiss dann, dass da noch Etwas versteckt sein könnte.
* Doppelclick: nein, das klappt bei mir überhaupt nicht (dieses Zusatz-Makro bringt nur Fehlermeldungen, ich musste es wieder rausnehmen). Ich hatte aber auch noch nie das von dir erwähnte Problem, dass ein Doppelclick nötig ist. Damit meine ich: bei all meinen Tabellen, bei denen das Makro für die dynamische Zeilenhöhe funktioniert, klappt es dann immer einwandfrei.

Hoffe, dass es nun nicht allzu kompliziert wird.
Schöne Grüsse, Walter

 

[weer]

nur als Anregung für dich. Wir wäre so etwas:?

Sub umbrueche()
    MsgBox Zeilenzahl(ActiveCell) 
         ' von mir geändert statt Zeilenzahl(Range("H1"))
End Sub

Function zeilenZahl(zelle As Range) As Long
    Dim sp As Long
    
    For sp = -5 To 5
        zeilenZahl = WorksheetFunction.Max(zeilenZahl, UBound(Split(zelle.Offset(0, sp), Chr(10))) + 1)
    Next sp
End Function

Hallo Andreas

Super! Vielen Dank! Was ich noch anpassen musste: Wenn die Spalten-Nummer der ActiveCell <= 5 ist, darf "sp" nicht -5 usw. sein. Das habe ich mit "On Error Resume Next" vor der Schleife gut hingekriegt.

Ich habe eine Frage an Dich. Walter schreibt am 24.11. um 20:38 h

* Zeilenumbruch: Alle Zellen sind als Typ "mit Zeilenumbruch" formatiert. Inhalte mit langem Text werden somit umgebrochen. Es gibt aber auch (oder mehrheitlich) Eingaben mit sanftem Umbruch (Alt-Return).

Gibt es eine einfache Möglichkeit, die Anzahl Zeilen in einer "Umbruch-Zelle" mit langem Text ohne ALT-RETURN , festzustellen? Ich habe dazu nichts gefunden.

Meine Ueberlegung dazu: Die Anzahl Zeilen hängt ja u. a. ab von der Länge des Textes und der Spaltenbreite. Annäherungsweise könnte man diese Anzahl Zeilen so bestimmen:

Set zelle = ActiveCell
AnzZeilen = Application.WorksheetFunction.RoundUp(Len(zelle) / zelle.ColumnWidth + C, 0)

C ist eine Konstante. - Bei meinen Tests hatte ich C = 1.5 gesetzt. Das hat in jedem Fall genug Zeilen ausgegeben. - Was meinst Du zu dieser Berechnung der Zeilen-Anzahl einer Zelle ohne ALT-RETURN?

Für die weitere Arbeit würde ich das Maximum der 10 umliegenden Stellen verwenden, das sich ergibt aus dem Maximum Zeilenzahl (gemäss Deiner Function) und dem Maximum meiner AnzZeilen.

Herzlichen Dank und liebe Grüsse
Niclaus

 

[nochEinAndreas]

Servus Niclaus,

Super! Vielen Dank! Was ich noch anpassen musste: Wenn die Spalten-Nummer der ActiveCell <= 5 ist, darf "sp" nicht -5 usw. sein. Das habe ich mit "On Error Resume Next" vor der Schleife gut hingekriegt.

Ich weiß, ich bin ein schrecklicher Pfennigfuchser. Aber mir widerstrebt dieses On Error - keine Ahnung warum. Hier noch schnell eine Version, die das anders löst:

Option Explicit

Sub umbrueche()
    MsgBox zeilenZahl(Range("B1"), 5)
End Sub

Function zeilenZahl(zelle As Range, breite As Long) As Long
    Dim sp As Long
    
    For sp = WorksheetFunction.Max(-breite, 1 - zelle.Column) To WorksheetFunction.Min(breite, Columns.Count - zelle.Column)
        zeilenZahl = WorksheetFunction.Max(zeilenZahl, UBound(Split(zelle.Offset(0, sp), Chr(10))) + 1)
    Next sp
End Function

Ich habe eine Frage an Dich. Walter schreibt am 24.11. um 20:38 h

Gibt es eine einfache Möglichkeit, die Anzahl Zeilen in einer "Umbruch-Zelle" mit langem Text ohne ALT-RETURN, festzustellen? Ich habe dazu nichts gefunden.

Ob eine Zelle per Formatierung auf Zeilenumbruch gestellt ist, kannst du mit

Range("A1").WrapText

prüfen. Ob das aber weiter hilft, weiß ich noch nicht.

Meine Ueberlegung dazu: Die Anzahl Zeilen hängt ja u. a. ab von der Länge des Textes und der Spaltenbreite. Annäherungsweise könnte man diese Anzahl Zeilen so bestimmen:

Set zelle = ActiveCell
AnzZeilen = Application.WorksheetFunction.RoundUp(Len(zelle) / zelle.ColumnWidth + C, 0)

C ist eine Konstante. - Bei meinen Tests hatte ich C = 1.5 gesetzt. Das hat in jedem Fall genug Zeilen ausgegeben. - Was meinst Du zu dieser Berechnung der Zeilen-Anzahl einer Zelle ohne ALT-RETURN?

Ich denke, das macht erst mal Sinn, bis uns etwas genaueres einfällt.

Etwas umständlich aber machbar wäre folgendes:
Man schaut sich die Schriftgröße der fraglichen Zelle an.
Dann nimmt man sich irgendwo eine leere Zeile und schreibt in die erste Zelle einen Buchstaben mit der selben Schriftgröße.
Jetzt macht man für diese Zeile ein Autofit für die Höhe und schaut sich die Höhe an.
Jetzt kopiert man die fragliche Zelle in die Zelle neben der, in die man eben den Buchstaben geschrieben hat.
Dann wieder eine AutoFit für die Höhe und auch die neue Höhe merken.
Die neue Höhe geteilt durch die alte Höhe sollte die Zeilenzahl sein.

Ich weiß, umständlicher gehts kaum. Aber mir fällt momentan nix besseres ein.
Zurück zu deiner Idee:

Für die weitere Arbeit würde ich das Maximum der 10 umliegenden Stellen verwenden, das sich ergibt aus dem Maximum Zeilenzahl (gemäss Deiner Function) und dem Maximum meiner AnzZeilen.

Ja, das klingt doch gut.

Wenn mir noch was anderes einfällt, melde ich mich noch mal.

Viele Grüße,
Andreas