Microsoft Excel

Herbers Excel/VBA-Archiv

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

Zeilenhöhe verbundene Zellen

Betrifft: Zeilenhöhe verbundene Zellen von: DirkR
Geschrieben am: 03.09.2014 11:34:11

Hallo Excelgemeinde,

ich brauche eure Hilfe!
Folgendes Problem beschäftigt mich seit STUNDEN!!!

Ich möchte in einer Tabelle die Zeilenhöhe von verbundenen Zellen automatisch einstellen. Ich habe im Forum auch schon einiges gefunden aber leider nicht passend genug.

Ich möchte, wenn ich in der Tabelle Angebotstext, nur die Zeilen automatisch anpassen, in dennen in Spalte H ein x steht. in den meisten fällen ist der verbundene Bereich in den Spalten A:F, kann aber auch schon mal E:F sein. In den verbundenen Zellen sind Verkettungen, mal mehr mal weniger.

Ich möchte nun, dass er zeile für zeile durchgeht, und in den Zeilen mit dem x in Spalte H die Zeilenhöhe automatisch ermittelt. Das ganze soll bei dem Ereignis Worksheet_Activate ausgelöst werden.

Habe den folgenden Lösungsansatz aus dem Forum gefunden, den ich aber nur teilweisse umgebaut bekomme:

Option Explicit
' nach: www.herber.de/mailing/137101h.htm
' "Zeilenhöhe bei verbundenen Zellen anpassen"

Sub ZeilenhoeheVerbundene(lngZeileNr As Long)
' Parameter ist die Zeilennummer.
' In einer Zeile kann es mehrere verbundene Zellen geben.
Dim sngHoehe As Single, cc As Integer, rngC As Range
Dim sngActWid As Single, rngM As Range, sngMergWid As Single

Application.ScreenUpdating = False
With Rows(lngZeileNr)
.AutoFit
sngHoehe = .RowHeight ' Mindesthöhe (insbes. nicht-verbundene Zellen)
End With
For cc = 1 To Cells(lngZeileNr, Columns.Count).End(xlToLeft).Column
If Cells(lngZeileNr, cc) > "" And Cells(lngZeileNr, cc).MergeCells Then
Set rngC = Cells(lngZeileNr, cc)
If Len(rngC) > 1000 Then
MsgBox "Der Text in " & rngC.Address(0, 0) & " hat über 1000 Zeichen !" _
& vbLf & vbLf & "Bitte kürzen!", vbCritical, "ZeilenhoeheVerbundene"
rngC.Select
Exit Sub
End If
With rngC.MergeArea
If .Cells(1).Address = rngC.Address And .WrapText = True Then
sngActWid = rngC.ColumnWidth ' Merken zum Wiederherstellen
' ---------------------------------------- Gesamtbreite rechnen
For Each rngM In .Cells
sngMergWid = rngM.ColumnWidth + sngMergWid
Next
sngMergWid = sngMergWid + (.Count - 1) * 0.71
' ----------------- Merge aufheben, Zellbreite auf Gesamtbreite
.MergeCells = False
rngC.ColumnWidth = sngMergWid
' ---------------------------------- max. optim. Höhe ermitteln
.EntireRow.AutoFit
sngHoehe = Application.Max(sngHoehe, rngC.Height)
' --------------------------- Breite und Merge wiederherstellen
rngC.ColumnWidth = sngActWid
.MergeCells = True
End If
End With
End If
Next cc
Rows(lngZeileNr).RowHeight = sngHoehe ' max. optim. Höhe einstellen
Application.ScreenUpdating = True
End Sub Sub test()
ZeilenhoeheVerbundene 20
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range, arrZ, colZ As New Collection, ii As Long

   arrZ = Array(13, 20, 28, 15)     ' Nummern der überwachten Zeilen anpassen

   For Each rng In Target           ' falls mehrere Zellen auf einmal geändert
      If Not IsError(Application.Match(rng.Row, arrZ, 0)) Then
         On Error Resume Next
         colZ.Add rng.Row, CStr(rng.Row)     ' hier werden Dubletten vermieden
         On Error GoTo 0
      End If
   Next rng
   For ii = 1 To colZ.Count
      ZeilenhoeheVerbundene colZ(ii) ' Höhen der gesammelten Zeilen optimieren
   Next ii
   Set colZ = New Collection
End Sub

' eine der möglichen Alternativen:
'
Private Sub xWorksheet_Change(ByVal Target As Range)
   Dim rng As Range, colZ As New Collection, ii As Long

   For Each rng In Target           ' falls mehrere Zellen auf einmal geändert
      If (rng.Row >= 15 And rng.Row <= 28) Or _
         (rng.Row >= 35 And rng.Row <= 38) Then      'überwachte Zeilennummern - anpassen
         On Error Resume Next
         colZ.Add rng.Row, CStr(rng.Row)     ' hier werden Dubletten vermieden
         On Error GoTo 0
      End If
   Next rng
   For ii = 1 To colZ.Count
      ZeilenhoeheVerbundene colZ(ii) ' Höhen der gesammelten Zeilen optimieren
   Next ii
   Set colZ = New Collection
End Sub
Habe auch schon folgenden Lösungsansatz versucht, der aber nicht bei jeder Zeile funktioniert!?!?
Er funktioniert nur dann, wenn die verbundene Zelle von A beginnt.
Private Sub Worksheet_Activate()
Dim I As Long
Dim ArrRows As String

ActiveSheet.DisplayPageBreaks = True
Application.ScreenUpdating = False

Rows("1:1200").EntireRow.Hidden = False

For I = 405 To 1200
  If UCase(Cells(I, 7).Value) = "X" And _
     Cells(I, 1).Value = "" Then Rows(I).Hidden = True

  If UCase(Cells(I, 8).Value) = "X" Then ZeilenhoeheVerbundene I
Next I

Application.ScreenUpdating = True
End Sub

kann mir bitte jemand helfen???

Danke schon mal!!!
Gruß DirkR

  

Betrifft: AW: Zeilenhöhe verbundene Zellen von: {Boris}
Geschrieben am: 03.09.2014 11:41:16

Hi,

bevor es hier an Bastelei geht: Sind die verbundenen Zellen UNBEDINGT nötig?
Die Dinger machen nämlich an allen Ecken und Enden mächtig Ärger.
Meist gibt es auch andere Lösungen.
Am Besten zeigst Du mal Deine Beispieldatei.

VG, Boris


  

Betrifft: AW: Zeilenhöhe verbundene Zellen von: DirkR
Geschrieben am: 03.09.2014 12:11:59

Hallo Boris,

ja leider!!!

Gruß DirkR


  

Betrifft: AW: Zeilenhöhe verbundene Zellen von: Daniel
Geschrieben am: 03.09.2014 11:46:07

Hi

bei verbundenen Zellen funktioniert die Automatische Ermittlung von Zeilenhöhe und Spaltenbreite nicht.

Der Workaround wäre folgender:

1. lege ein Dummy-Tabellenblatt an. Die Spaltenbreite und die Formatierung der Zellen sollte so sein wie im Originalblatt (vorallem Schriftart und Schriftgrad)
Ausnahem: im Dummyblatt dürfen die Zellen nicht verbunden sein.

2. kopiere jetzt den Inhalt der Verbundenen Zellen in dieses Dummyblatt.
und lasse die Zeilenhöhe automatisch einstellen (das geht, weil die Zellen nicht verbunden sind)

3. frage jetzt die Zeilenhöhe der Zelle ab.
im Origignalblatt müssen dann die Zeilen, die zum Zellverbund gehören, in Summe diese Höhe ergeben.

Gruß Daniel


  

Betrifft: AW: Zeilenhöhe verbundene Zellen von: DirkR
Geschrieben am: 03.09.2014 12:22:05

Hallo Daniel,

über diese Option habe ich auch schon nachgedacht. Möchte ich allerdings nicht so gerne machen.

Der Beispielcode funktioniert in den Fällen, wenn die verbundene Zelle von A:F verbunden ist.

Ich hätte gerne diesen Code verbessert, wenn möglich.

Gruß DirkR


  

Betrifft: AW: Zeilenhöhe verbundene Zellen von: DirkR
Geschrieben am: 03.09.2014 12:22:35

-- noch offen ---


  

Betrifft: AW: Beispieldatei ??? owt von: Daniel
Geschrieben am: 03.09.2014 12:51:06

Gruß Daniel


  

Betrifft: AW: Beispieldatei ??? owt von: DirkR
Geschrieben am: 03.09.2014 14:02:02

Hallo Daniel,

ich habe mal eine abgespeckte Version als Beispielmappe hochgeladen:
https://www.herber.de/bbs/user/92455.xlsm

Jetzt kommt das Saltsame, was ich nicht verstehe:
In Tabelle1 funktioniert der Code einwandfrei.
In der Tabelle Angebotstext habe ich einen Ausschnitt aus der Originaldatei. Dort funktioniert es in den Zeilen 8 und 9 nicht so wie in der Tabelle1. Obwohl ich keinen Unterschied erkennen kann.

WARUM ist das so????
In den Zeilen 8 und 9 der Tabelle Angebotstext ist doch das selbe Beispiel wie in Tabelle1 in der Zeile 3!

Oder nicht?
Warum verhält sich unterschiedlich?

Gruß DirkR


  

Betrifft: AW: Beispieldatei ??? owt von: Daniel
Geschrieben am: 03.09.2014 14:44:36

Hi
also bei mir passiert nichts, wenn ich in Angebotstexte auf den Button klicke.

wenn das Blatt so aufgebaut ist, wie die Seite angebot, dann ist das von mir beschriebene Verfahren doch deutlich einfacher vorallem weil du nicht jede Zelle einzeln bearbeiten musst, sondern für die ganze Spalte auf einen Schlag:

1. Spalte C kopieren und als Wert in eine freie Spalte einfügen (z.B. Spalte J)
2. Spalte J so breit machen wie die Zellen C-F (also die Spalten, die verbunden sind) und den Zeilenumbruch aktivieren
3. Automatsich für alle Zeilen die Zeilenhöhe einstellen
4. Spalte J wieder löschen.

die Formatierung der Spalte J kannst du ja vorab von Hand einstellen, dann brauchs du für das ganze nur 4 Zeilen Code

Gruß Daniel


  

Betrifft: AW: Beispieldatei ??? owt von: DirkR
Geschrieben am: 03.09.2014 15:23:31

Hallo Daniel,

bei mir werden die Zeilenhöhen von Zeile 8 und 9 verkleinert wenn ich auf den Button im Tabellenblatt Angebotstext klicke.

Habe aber wahrscheinlich das Problem gefunden. in den verbunden Zellen in dem Bereich war nicht in allen Zellen das Häckchen für Zeilenumbruch gesetzt. So scheint es zu funktionieren. Allerdings bin ich mir nicht sicher, ob es bei allen Eventualitäten auch einwandfrei funktioniert.

Deine Lösung wäre schon etwas umständlich für mich, da wir über 800 Zeilen immer wieder bearbeiten müssen.
Und nicht in jeder Zeile die Anpassung stattfinden soll.

Für den Fall, dass noch jemand eine Verbesserung oder einen Fehler sieht, lasse ich den Beitrag noch offen.

Grüße
DirkR


  

Betrifft: Ich sehe nach wie vor... von: {Boris}
Geschrieben am: 03.09.2014 16:01:02

Hi,

...keinen Grund, warum die Zellen verbunden sein müssen.
Wenn du beispielsweise die Zellen C4:F4 ENTbindest und stattdessen über die Zellen das Format "Über Auswahl zentrieren" anwendest, dann funktioniert auch die automatische Zeilenhöhe und alle Bastelei ist völlig überflüssig.

VG; Boris


  

Betrifft: AW: Ich sehe nach wie vor... von: DirkR
Geschrieben am: 03.09.2014 17:24:05

Hallo Boris,

wo finde ich: Über Auswahl zentrieren?

Gruß DirkR


  

Betrifft: AW: Ich sehe nach wie vor... von: DirkR
Geschrieben am: 03.09.2014 17:27:07

Hallo Boris,

habe es gefunden. Die Lösung geht leider auch nicht.
Der Text muss links ausgerichtet sein!

Gruß
DirkR


  

Betrifft: AW: Ich sehe nach wie vor... von: Daniel
Geschrieben am: 03.09.2014 18:01:54

Hi
ich würde weiterhin dafür plädieren, dass du die Inhalte der Spalte C in eine freie Spalte kopierst, deren Breite der Breite der verbundenen Zellen entspricht um dann hierfür automatisch die Zeilenhöhe zu ermitteln.
Wenn du das nicht für alle Zeilen machen willst, sondern nur für bestimmte, kannst du das ja trotzdem machen.

Gruß Daniel


 

Beiträge aus den Excel-Beispielen zum Thema "Zeilenhöhe verbundene Zellen"