Microsoft Excel

Herbers Excel/VBA-Archiv

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

Bereich in Bereich ausblenden | Herbers Excel-Forum


Betrifft: Bereich in Bereich ausblenden von: Kai Thomas
Geschrieben am: 23.10.2008 18:20:00

Hallo zusammen,
ich habe ein Tabellenblatt mit dem Bereich: A1:P38
In Abhängigkeit einer Zahl in einer anderen Zelle eines anderen Tabellenblatts, möchte ich Teilbereiche nicht anzeigen lassen, wie z.b. den Bereich: E20:H38
Ein ausblenden von Zeilen, oder Spalten kommt nicht in Frage, da der restliche Bereich ja weiterhin angezeigt werden soll. Die Zellen Weis zu machen kann als Lösung auch nicht gewählt werden, da die Zellen mit Hintergrundfarben und Rahmen formatiert sind und das ganze dann nicht rückgängig zu machen wäre.

Gibt es evtl. dazu noch weitere Lösungen?
Mit Hilfe von aBereich.NumberFormat = ";;;"
funktioniert es ja auch nicht, da dann die Zahlen zwar nicht sichtbar sind aber immernoch die Formatierung.

Sollte es gar nicht möglich sein, wäre es auch nett wenn ihr mir das sagen könntet :-)

Viele Grüße Kai

  

Betrifft: AW: Bereich in Bereich ausblenden von: Herbert
Geschrieben am: 23.10.2008 18:42:05

Hi,

zwei Möglichkeiten:

1. du schneidest den Bereich aus, kopierst ihn in ein(z.B. temporäres und/oder ausgeblendetes)
Blatt.

2. Du "merkst" dir alle Inhalte und Formate in einem Array und schreibst bei Bedarf wieder zurück.

Variante 1 geht auch nach Excelneustart, Variante 2 nur während einer Excelsitzung.

mfg Herbert


  

Betrifft: AW: Bereich in Bereich ausblenden von: Erich G.
Geschrieben am: 23.10.2008 20:51:07

Hallo Kai,
noch eine Möglichkeit:

Sub test()
   Verberge Range("E20:H38")
End Sub

Sub Verberge(rng As Range)  ' nach: L.Vira am 11.07.2002 - 09:34:22
   Const dd As Double = 0.4 ' www.herber.de/forum/archiv/132to136/t135599.htm#135616

   ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
      rng.Left + dd, rng.Top + dd, rng.Width - dd, rng.Height - dd).Select
   Selection.ShapeRange.Line.Visible = msoFalse
End Sub

Mit dem Wert 0.4 kannst du noch etwas spielen. Er soll bewirken,
dass die Rahmen/Gitternetzlinien der angrenzenden Zellen sichtbar bleiben.

Wenn es stzört, dass danach das Rechteck (statt einer Zelle oder eines Bereichs) selektiert ist,
kannst du am Ende z. B. "Cells(1, 1).Select" schreiben.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Bereich in Bereich ausblenden von: Kai Thomas
Geschrieben am: 23.10.2008 23:59:05

Hallo Erich,
diese Lösung gefällt mir sehr gut. Allerdings handelt es sich hier um 38 Bedingungen und Bereiche.

Es soll also so sein,
wenn Zelle A28

=1 dann im Tabellenblatt Teamausw. Bereich E1:P19 & A172:H190 ausblenden
=2 dann im Tabellenblatt Teamausw. Bereich I1:P19 & A172:H190 ausblenden
=3 dann im Tabellenblatt Teamausw. Bereich M1:P19 & A172:H190 ausblenden
=4 dann im Tabellenblatt Teamausw. Bereich A172:H190 ausblenden
=5 dann im Tabellenblatt Teamausw. Bereich E20:P38 & A172:H190 ausblenden
=6 dann im Tabellenblatt Teamausw. Bereich I20:P38 & A172:H190 ausblenden
=7 dann im Tabellenblatt Teamausw. Bereich M20:P38 & A172:H190 ausblenden
=8 dann im Tabellenblatt Teamausw. Bereich A172:H190 ausblenden
=9 dann im Tabellenblatt Teamausw. Bereich E39:P57 & A172:H190 ausblenden
=10 dann im Tabellenblatt Teamausw. Bereich I39:P57 & A172:H190 ausblenden
=11 dann im Tabellenblatt Teamausw. Bereich M39:P57 & A172:H190 ausblenden
=12 dann im Tabellenblatt Teamausw. Bereich A172:H190 ausblenden
...
=37 dann im Tabellenblatt Teamausw. Bereich E172:H190 ausblenden

Das heißt, das die Bereiche bei jeder Zahl anders sind und somit nicht nur aus sondern auch eingeblendet werden müßen.

ist das ein und ausblenden mit dieser Lösung möglich?

Viele Grüße Kai


  

Betrifft: AW: Bereich in Bereich ausblenden von: Erich G.
Geschrieben am: 24.10.2008 13:53:27

Hallo Kai,
nachdem einigen Blicken in das Blatt (der heruntergeladenen Mappe) vermute ich, dass die Rechtecke
das Ein- bzw.. Ausblenden von Zeilen quasi ergänzen sollen.

Ich hab das gleich in das bisherige Makro eingebaut:
Oben muss noch eine Deklaration ergänzt werden:
Dim ii As Long, jj As Long, rngK As Range ' war bisher schon
Dim myShape As Shape ' ist neu

'  ----------------------------------------------------- ab hier nach Spieltag
   If Not Intersect(Target, Range("A28")) Is Nothing Then
      With Sheets("Teamausw.")
                                             ' Rechtecke löschen
         For Each myShape In .Shapes
            myShape.Delete
         Next myShape
                                             ' Zeilen ein-/ausblenden
         If intT = 0 Then
            .Rows("1:190").Hidden = True
         Else
            .Range(.Rows(1), .Rows(19 * Fix((intT + 3) / 4))).Hidden = False
            .Rows("172:190").Hidden = False
         End If
         If intT < 33 Then _
            .Range(.Rows(1 + 19 * Fix((intT + 3) / 4)), .Rows(171)).Hidden = True
                                             ' Rechtecke einfügen
         If intT > 0 And intT < 37 And intT Mod 4 > 0 Then
            Verberge .Range(.Cells(1 + 19 * Fix((intT - 1) / 4), _
                                   1 + 4 * (intT Mod 4)), _
                            .Cells(19 * Fix((intT + 3) / 4), "P"))
         End If
         Select Case intT
            Case 0
            Case Is < 37:  Verberge .Range("A172:H190")
            Case 37:       Verberge .Range("E172:H190")
         End Select
      End With
      For zz = 1 To intT
         Sheets(CStr(zz)).Visible = True     ' Blätter einblenden
      Next zz
      For zz = intT + 1 To 38
         Sheets(CStr(zz)).Visible = False    ' Blätter ausblenden
      Next zz
   End If
End Sub

Nach Karins Tipp habe ich "Verberge" geändert.
Es funzt nun auch auf einem nicht aktivierten Tabellenblatt (rng.Parent):

Sub Verberge(rng As Range)
   Dim shRechteck As Shape
   Const dd As Double = 0.4

   Set shRechteck = rng.Parent.Shapes.AddShape(msoShapeRectangle, _
      rng.Left + dd, rng.Top + dd, rng.Width - dd, rng.Height - dd)
   shRechteck.Line.Visible = msoFalse
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Bereich in Bereich ausblenden von: Kai Thomas
Geschrieben am: 27.10.2008 00:45:37

Hallo Erich,
ich habe einen Anwendungs und Objektdefinierten Fehler in folgender Zeile:
myShape.Delete

Diese Zeile habe ich wie von dir geschrieben hinzugefügt:
Dim myShape As Shape

Du hast recht, das die Rechtecke das ausblenden der Zeilen unterstützen sollen.

Vielen Dank, für die bisherige Hilfe !!!

Gruß Kai


  

Betrifft: AW: Blattschutz aktiv? von: Erich G.
Geschrieben am: 27.10.2008 06:25:12

Hallo Kai,
ist das Blatt "Teamausw." vielleicht geschützt? Dann fehlte da noch ein Unprotect.

Bei mir läuft es sauber.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Blattschutz aktiv? von: Kai Thomas
Geschrieben am: 27.10.2008 17:07:04

Hallo Erich,
daran hatte es wohl gelegen, obwohl ich dachte ihn im Makro aufgehoben zu haben.

Ich habe aber ein Bild hochgeladen https://www.herber.de/bbs/user/56327.zip
, auf dem du siehst, das es irgendwiewie nicht so funktioniert wie ich es wollte. Ich denke, so kann ich das nicht lösen. Mir hatte die Lösung eigentlich sehr gut gefallen, da das Makro in der Bearbeitung sehr schnell war.

Hast du evtl. noch eine Idee dazu?

Gruß Kai


  

Betrifft: AW: Blattschutz aktiv? von: Erich G.
Geschrieben am: 27.10.2008 17:33:35

Hallo Kai,
an der Abbildung fällt mir eigentlich gar nichts auf. Da werden alle Zeilen ausgeblendet,
die gar nicht sichtbar sein sollen, und in den restlichen Zellen werden die noch nicht relevanten Spieltage
mit Rechtecken überdeckt.

Wie hättest du das denn gewollt? Sollte vielleicht "Gesamt" rechts neben "Spieltag 7" stehen?
Das geht so natürlich nicht.

Ein möglicher Weg:
Es gibt außer dem jetzigen (vollständigen) Blatt "Teamausw" ein neues Blatt "Teamausw-Anzeige",
in das alle relevanten Spieltage und "Gesamt" möglichst dicht kopiert werden.
Da stellt sich natürlich die Frage, wann das geschehen soll - bei jeder Änderung der Spieltage-Anzahl?.
Oder per Klick auf einen Button dann, wenn der Benutzer die "Anzeige"-Seite haben will.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Blattschutz aktiv? von: Kai Thomas
Geschrieben am: 27.10.2008 18:45:54

Hallo Erich,
das Problem ist, das die Rahmen wenn ein Spieltag komplett ausgeblendet werden soll noch da sind, allerdings lässt sich das ja auch nicht vermeiden, da wenn ein ausgeblendeter Spieltag an einen angezeigten grenzt ansonsten die Rahmen nicht angezeigt würden.

Das mit dem kopieren über ein Button wäre eine gute Lösung, da ich so auch die relevanten Daten aus den anderen Blättern kopieren könnte, da ich dafür die Anzeige auch noch hätte anpassen müßen und mir darüber schon den Kopf zerbrochen habe :-)

Wäre das möglich, über ein Button die relevanten Daten aus den Tabellenblättern in ein jeweiliges Tabellenblatt teamausw._ausdruck GÜU_ausdruck zu kopieren?

Ich habe schon folgendes makro in dem Zellen kopiert werden, nur die Verarbeitung dauert schon lange, würde es für die 4 Tabellenblätter für die ich es brauche nicht sehr lange dauern?

Private Sub Worksheet_Activate()
Dim i As Integer, wksA As Worksheet
Dim Zelle As Range, Bereich As Range

Set Bereich = Worksheets("Spieltagausw.").Range("A3:A682")
Set wksA = Sheets("Spieltagausw.")

With Worksheets("Datenerfassung")
For Each Zelle In Bereich
For i = 2 To 21 '*** soviele Zellen in 'Datenerfassung' ***
If Zelle.Value = .Cells(i, 1).Value Then
.Range("C" & i & ":D" & i).Copy Destination:=Zelle.Range("AN1")
Exit For
End If
Next i
Next Zelle
End With
End Sub


Vielen Dank und
Gruß Kai


  

Betrifft: AW: Blattschutz aktiv? von: Erich G.
Geschrieben am: 27.10.2008 19:50:36

Hallo Kai,
hier mal ein Versuch. Der Code kommt in ein normales Modul,
dann kannst du ihn einem Formular-Button (auf Blatt Datenerfassung) leicht zuordnen:

Option Explicit

Sub TeamAusdruck()
   Dim intT As Integer

   intT = Sheets("Datenerfassung").Cells(28, 1)             ' Anz. Spieltage
   If intT < 1 Then
      MsgBox "Kein Spieltag zu kopieren", vbCritical, "Abbruch"
      Exit Sub
   End If
   Application.Calculation = xlCalculationManual
   If WorksheetEx("Teamausw._Ausdruck") Then
      Application.DisplayAlerts = False
      Sheets("Teamausw._Ausdruck").Delete
      Application.DisplayAlerts = True
   End If
   Sheets("Teamausw.").Copy after:=Sheets("Teamausw.")
   ActiveSheet.Name = "Teamausw._Ausdruck"
   ActiveSheet.UsedRange = Sheets("Teamausw.").UsedRange.Value    ' nur Werte

   If intT < 37 Then
      Range("A172:H190").Delete xlUp
   ElseIf intT = 37 Then
      Range("E172:H190").Delete xlUp
   End If
   If intT < 33 Then _
      Range(Rows(1 + 19 * Fix((intT + 3) / 4)), Rows(171)).Delete
   If intT < 37 And intT Mod 4 > 0 Then
         Range(Cells(1 + 19 * Fix((intT - 1) / 4), 1 + 4 * (intT Mod 4)), _
               Cells(19 * Fix((intT + 3) / 4), "P")).Delete xlUp
   End If
   Application.Calculation = xlCalculationAutomatic
End Sub

Function WorksheetEx(strNam As String) As Boolean
   On Error Resume Next
   WorksheetEx = Worksheets(strNam).Index > 0
End Function

In der alten Prozedur musst du den Code, der in "Teamausw." Zeilen ausblendet bzw. Rechtecke einfügt, wieder rauswerfen.
Außerdem musst du einmalig die Rechtecke auf "Teamausw." löschen und alle Zeilen einblenden.

Nicht wundern: Am Ende des Makros vergeht einige Zeit für die Berechnung.
Das liegt nicht am neuen Blatt - da sind keine Formeln drin. Aber in deiner Mappe sind eben sehr viele Formeln....

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Blattschutz aktiv? von: Kai Thomas
Geschrieben am: 27.10.2008 20:43:17

Hallo Erich,
so wie es jetzt ist, ist es perfekt. Es wäre nett, wenn du mir sagen könntest, wie ich es für die 3 anderen Seiten machen muss, muss ich jetzt für jedes Blatt dann ein Modul erstellen? Also könnte ich das jetzige Modul 3 mal kopieren und dann die Blattnamen ändern sowie die zu kopierenden Bereiche anpassen?

Ausserdem habe ich noch eine Frage die wir im letzten Thread geklärt hatten, kann ich die hier stellen, oder wie muss ich da vorgehen?

Vielen Dank für die Hilfe die ist echt super!

Gruß Kai


  

Betrifft: AW: Blattschutz aktiv? von: Kai Thomas
Geschrieben am: 28.10.2008 01:23:47

Hallo Erich,

leider gibt es doch noch einen kleinen Fehler bei der Anzeige von 3,7,11, usw. Spieltagen, siehe Bild.





Ansonsten funktioniert es wie schon zuvor beschrieben perfekt, auch die Verarbeitungszeit ist nicht so lange wie ich gedacht habe, nochmal herzlichen Dank dafür!



Gruß Kai


  

Betrifft: AW: Störende Rahmen von: Erich G.
Geschrieben am: 28.10.2008 10:38:03

Hallo Kai,
das ist keine Sache des Makros - es hat mit Rahmenlinien nichts zu tun.
Du kannst das Problem leicht beseitigen:

Die Freifläche links neben dem "Gesamt"-Block hat einen Rahmen, den man erst dann sieht,
wenn "Gesamt" durch das Delete des darüberliegenden Bereichs nach oben verschoben wurde.
Die Rahmen um diese Freifläche solltest du löschen - er stört.
Wenn dann der linke Rahmen um "Gesamt" fehlt, kannst du ja "Gesamt" diesen linken Rahmen geben.
Das Gleiche gilt für Spieltag 38.

Man kann nicht so leicht unterscheiden, zu welcher Zelle ein Rahmen zwischen zwei Zellen gehört.
Er kann ja rechter Rahmen der einen oder linker Rahmen der anderen Zelle sein.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Störende Rahmen von: Kai Thomas
Geschrieben am: 28.10.2008 21:24:28

Vielen Dank,
es klappt super, jetzt hab ich nur festgestellt, das beim Druck immer alles gedruckt wird, also auch 1-3 leere Seiten, ich war eigentlich der Meinung, das du mir dazu schonmal was geschrieben hattest, habe es aber nicht gefunden.

Und ich wollte noch fragen, ob ich für die 3 anderen Tabllenblätter jeweils ein modul brauche, oder auch in das vorhandene schreiben kann.

Gruß Kai


  

Betrifft: AW: leere Seiten / mehrere Prozeduren? von: Erich G.
Geschrieben am: 29.10.2008 00:50:04

Hallo Kai,
zu den leeren Seiten hatte ich dir in https://www.herber.de/forum/archiv/1016to1020/t1018769.htm#1019695
das hier geschrieben:

Die leere(n) Seite(n) kannst du so wegbekommen:
Du stellt Zeile 191 auf eine geringe Höhe und Spalte Q auf eine geringe Breite.
Wenn das noch nicht ausreicht, verkleinerst du zusätzlich die Seitenränder des Blattes so,
dass Zeile 191 und Spalte Q noch auf die Seite passen.

Ob du die Bearbeitung beiden anderen Blätter in die gleiche Prozedur (nicht: Modul) packen
oder besser drei Prozeduren daraus machst, liegt in deinem Ermessen, hängt von der Funktion ab.
Sollen die drei Tabellen zu unterschiedlichen Zeitpunkten/Gelegenheiten oder immer gemeinsam erstellt werden?
Soll es einen oder drei Start-Buttons geben?
Wenn es besser drei getrennte Prozeduren sein sollen, kannst du ja den Teil, der in allen gleich ist,
in eine vierte Prozedur auslagern, evtl. mit dem Tabellennamen als Parameter.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: leere Seiten / mehrere Prozeduren? von: Kai Thomas
Geschrieben am: 29.10.2008 15:58:33

Hallo Erich,

Sollen die drei Tabellen zu unterschiedlichen Zeitpunkten/Gelegenheiten oder immer gemeinsam erstellt werden?


Die Tabellen sollen immer gleichzeitig erstellt werden.

Soll es einen oder drei Start-Buttons geben?


Es soll einen Start-Button geben.

Es soll so sein, das man die Einstellungen vornimmt, dann den Button drückt und dann die neuen Tabellen erscheinen, deshalb hatte ich auch gefragt, ob man die Tabellen aus denen kopiert wird auch irgendwie ausblenden kann, damit man nur noch die kopierten sieht.

p.s. das mit den leeren Seiten hat geklappt, vielen Dank für die erneute Linkangabe

Gruß Kai


  

Betrifft: AW: Blattschutz aktiv? von: Erich G.
Geschrieben am: 27.10.2008 17:50:29

Hallo Kai,
jetzt fällt mir ein, dass mir doch etwas aufgefallen ist: Es wird wohl eine überflüssige leere Seite ausgedruckt.
Meintest du das?

Die leere(n) Seite(n) kannst du so wegbekommen:
Du stellt Zeile 191 auf eine geringe Höhe und Spalte Q auf eine geringe Breite.
Wenn das noch nicht ausreicht, verkleinerst du zusätzlich die Seitenränder des Blattes so,
dass Zeile 191 und Spalte Q noch auf die Seite passen.

Wenn du etwas genauer beschrieben hättest, was dich stört, müsste ich jetzt nicht rätseln...

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Bereich in Bereich ausblenden von: Beverly
Geschrieben am: 24.10.2008 08:01:28

Hi Erich,

es geht auch ohne Select ;-)

Sub Verberge(rng As Range)  ' nach: L.Vira am 11.07.2002 - 09:34:22
    Const dd As Double = 0.4 ' www.herber.de/forum/archiv/132to136/t135599.htm#135616
    Dim shRechteck As Shape
    Set shRechteck = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
       rng.Left + dd, rng.Top + dd, rng.Width - dd, rng.Height - dd)
    shRechteck.Line.Visible = msoFalse
 End Sub



GrußformelBeverly's Excel - Innub


  

Betrifft: Dank dir, Karin! :-) (owT) von: Erich G.
Geschrieben am: 24.10.2008 13:39:54




  

Betrifft: Vielen Dank von: Kai Thomas
Geschrieben am: 30.10.2008 01:34:33

Hallo Erich,
hab den Code nun doch vollends verstanden! Vielen Dank für deine großartige Hilfe!

Viele Grüße Kai


Beiträge aus den Excel-Beispielen zum Thema "Bereich in Bereich ausblenden"