Office: Zeilen über DropDown-Liste automatisch ein- und ausblenden

Hallo zusammen,

ich habe das Forum nach meinem Problem durchstöbert, einiges gefunden, ein eigenes Makro gebastelt, aber komm nun mit meinen spärlichen Kenntnissen nicht weiter.

Wie der Titel bereits aussagt möchte ich mit einem Makro gewisse Zeilen meiner Excel-Tabelle ein- bzw. ausblenden. In verschiedenen Zellen können per DropDown-Liste Begriffe ausgewählt werden, wodurch bestimmte Zeilen ein- und andere ausgeblendet werden.

Ich habe es soweit hinbekommen, dass ich Zeilen ein- und ausblenden kann, wenn in einer Zelle Begriffe ausgewählt werden. Dies bezieht sich aber nur auf diese eine Zelle mit DropDown-Liste, sobald ich über eine zweite und dritte Zelle (ebenfalls mit DropDown-Liste) andere Zeilen aus- und einblenden möchte, klappt es nicht mehr und da brauch ich eure Hilfe.

Hier ist das Makro welches ich bisher verwende: In Zelle B3 befindet sich in meinem Fall die DropDown-Liste (Auswahl: Test und Test2), Zeilen 4 und 5 werden dabei angesprochen (und Inhalte aus Zellen gelöscht).

Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$3" Then

If WorksheetFunction.CountIf(Range("b3"), "Test") > 0 Then
Rows(4).Hidden = False
Rows(5).Hidden = True
Range("D5, F5").ClearContents
Else

If WorksheetFunction.CountIf(Range("b3"), "Test2") > 0 Then
Rows(4).Hidden = True
Rows(5).Hidden = False
Range("D4, F4").ClearContents
Else

End If
End If
End If
End Sub

Sicherlich keine schöne Lösung, aber es funktioniert einwandfrei.

Wie kann ich das Makro nun erweitern, sodass beispielsweise bei dem Wort „Hallo“ in Zelle B6 die Zeile 7 ein- und die Zeile 8 ausgeblendet werden und bei „Tschüss“ in B6 die Zeile 7 aus- und die Zeile 8 eingeblendet werden?

Hatte es einfach mit folgendem Code hinter dem letzten Else vom bisherigen Makro versucht, aber nichts passiert (wegen dem Else, da vorher bereits eine Bedingung erfüllt und daher nicht weiter verglichen wird?).

If Target.Address = "$B$6" Then
If WorksheetFunction.CountIf(Range("b6"), "Hallo") > 0 Then
Rows(7).Hidden = False
Rows(8).Hidden = True
Range("D8, F8").ClearContents
Else

If WorksheetFunction.CountIf(Range("b6"), "Tschüss") > 0 Then
Rows(7).Hidden = True
Rows(8).Hidden = False
Range("D8, F8").ClearContents
Else

End If
End If
End If

Ich komm aber leider nicht weiter. Ich wäre über eine „leicht“ verständliche Antwort sehr dankbar.

 

Hi,

benutze ElseIf für die weitere Verzweigung:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$3" Then
        ' Code für B3
    ElseIf Target.Address = "$B$8" Then
        ' Code für B6
    End If
End Sub

Ich persönlich bevorzuge in solchen Fällen allerdings die Select Case-Anweisung nach diesem Prinzip, weil sie (aus meiner Sicht) übersichtlicher ist:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$B$3"
            ' Code für B3
        Case "$B$8"
            ' Code für B6
    End Select
End Sub

Bis später,
Karin

 

Die Lösung mit der Select Case- Anweisung hat super funktioniert, vielen Dank :-)

Ich würde es gerne noch folgendermaßen erweitern, anderes Beispiel:

Wenn "Ja" in Zelle B6 steht, wird die Zeile 7 angezeigt. Sobald aber "Nein" in Zelle B3 steht, wird die Zeile 7 ausgeblendet (egal was in der Zelle B6 steht).

Kannst du mir hierbei auch weiterhelfen?

 

Hi,

so richtig verstehe ich dein Anliegen leider nicht. Was wird denn mit dem Inhalt in B6 wenn B3 geändert wird bzw. was wird mit dem Inhalt von B3 wenn B6 geändert wird? Die Inhalte müssten dann doch eigentlich "umgekehrt" werden - oder sehe ich das falsch?

Bis später,
Karin

 

Hallo,

und leider verstehe ich deinen Einwand nicht:-D

Ich versuche es mal mit einem anderen Beispiel zu erklären, was ich meine:

Über A1 kann mittels DropDown entweder Produkt 1 oder 2 ausgewählt werden und bei Zelle A2 kann zwischen Lackiert und Nicht Lackiert entschieden werden. Sobald Lackiert in Zelle A2 gewählt wird, wird die Zeile 3 eingeblendet, in der dann über Zelle A3 die Farbe Rot,Grün oder Blau gewählt werden kann. Wenn Nicht Lackiert in Zelle A2 steht wird die Zeile 3 wieder ausgeblendet.

Soweit bekomm ich es nun Dank deiner Hilfe hin, aber nun folgendes Problem: Produkt 2 gibt es Lackiert nur in der Farbe rot. Deshalb möchte ich, dass Zeile 3 nicht eingeblendet wird, wenn in Zelle A1 Produkt 2 und Zelle A2 Lackiert steht.

Gruß

 

Hi,

dass du meinen Einwand nicht verstehst, liegt daran, dass du deine Tabelle vor dir siehst, ich dagegen nicht. Deine Zellbezeichnungen sind nun auch andere als in deinem ersten Beitrag.

Ich kann also nur generell sagen: schließe mit einer If-Anweisung innerhalb der betreffenden Case-Anweisung aus, dass die relevante Zeile eingeblendet wird, wenn das entsrprechenden Wort ausgewählt wurde.

Bis später,
Karin

 

War auch nicht böse gemeint ;-)

Ich konnte nun auch alles dank deiner Hilfe anpassen. Nun möchte ich das Blatt noch schützen, damit die Benutzer es nicht mehr verändern können. Leider funktioniert das Makro dann nicht mehr, da die Zeilen ja aus- bzw. eingeblendet werden.

Ich habe dazu ein Makro aufgezeichnet und bin auf folgendes gekommen:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$B$4"
If WorksheetFunction.CountIf(Range("b4"), "Rund") > 0 Then
ActiveSheet.Unprotect Password:="123"
Rows(5).Hidden = False
Rows(6).Hidden = True
Rows(7).Hidden = True
Rows(8).Hidden = True
Range("D7, F7, D6, F6, H6, D8, F8, H8").ClearContents
Else
End If
ActiveSheet.Protect Password:="123"
End Select
End Sub

Leider funktioniert es überhaupt nicht. Bis jetzt habe ich es nur geschafft, dass Blatt zu schützen.

Kannst du mir helfen?

 

Moinsens!

Das sieht ein wenig... hmmm... vermurkst aus. ;-)

a) Ein "select", bei dem es nur einen einzigen "case" gibt, ist ziemlich sinnfrei. Das tut's auch ein "if".

Code:

if target.address="$b$4" then ..."

Schöner ist aber i.a. der Vergleich über die intersect() Funktion. Damit wird der Kram auch durchlaufen, wenn z.B. a3:c5 markiert ist und die "del" Taste gedrückt wurde.

b) Innerhalb des "case" hast Du dann ein "if", dessen "else" Zweig leer ist.
--> also eigentlich erstmal weg damit

c) Und die "countif" Funktion ist die Kanone mit den Spatzen. Auch hier tut's ein einfaches "if".

Code:

if range("b4").value="Rund" then ...

Ich vergleiche da übrigens gerne mit per trim$() bereinigten Werten...

d) Wenn Du bereits "target.address" auf b4 geklärt hast, kannst du "target" auch gerne in Folge benutzen.

Code:

If trim$(target.value)="Rund" Then ...

e) das "unprotect" ist innerhalb des "if", das "protect" aber außerhalb. Geht wohl, ist aber unschön.
--> das "protect" gleich hinter die "clearcontents" Zeile

f) Du blendest zwar die Zeilen 5 ein, 6:8 aus, wenn b4="Rund" ist. Aber was, wenn nicht? Sollte das dann nicht umgekehrt sein? Also doch den "else" Zweig nutzen.

Code:

    else
      Rows(5).Hidden = true
      Rows(6).Hidden = false
      Rows(7).Hidden = false
      Rows(8).Hidden = false

Das bedeutet aber auch, das "protect" / "unprotect" Paar eine Ebene weiter nach oben, außerhalb von "if ... end if" zu setzen.

Von daher:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)      
  if Target.Address="$B$4" then
    ActiveSheet.Unprotect Password:="123"
    If trim$(target.value)="Rund" Then
      Rows(5).Hidden = False
      Rows(6).Hidden = True
      Rows(7).Hidden = True
      Rows(8).Hidden = True
      Range("D7, F7, D6, F6, H6, D8, F8, H8").ClearContents
     else
      Rows(5).Hidden = true
      Rows(6).Hidden = false
      Rows(7).Hidden = false
      Rows(8).Hidden = false
    End If
    ActiveSheet.Protect Password:="123"
  end if
End Sub

Wenn natürlich in b4 nicht nur auf "rund", sondern auch "eckig", "oval", "sternförmig", ... abgeprüft werden soll und die Zeilen 5:8 davon abhängig unterschiedlich ein- und ausgeblendet werden sollen, dann bist Du mit "select case" doch wieder auf der richtigen Spur:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)      
  if Target.Address="$B$4" then
    ActiveSheet.Unprotect Password:="123"
    select case trim$(target.value)
      case "Rund"
        Rows(5).Hidden = False
        Rows(6).Hidden = True
        Rows(7).Hidden = True
        Rows(8).Hidden = True
        Range("D7, F7, D6, F6, H6, D8, F8, H8").ClearContents
      case "Eckig"
        [ ... ]
      case else
        [ ... ] ' alle zeilen ausblenden?
    End select
    ActiveSheet.Protect Password:="123"
  end if
End Sub

Vom Ablauf her ist es übrigens etwas schöner zu lesen / verfolgen, wenn Du z.B. zunächst alle Zeilen ausblendest und dann in den "if" oder "case" Zweigen nur die wieder einblendest, die Du brauchst. Das erspart Dir dann auch wieder den "else" bzw "case else" Zweig. Außerdem hat es den Vorteil, daß Du nicht in jedem "case" 4 Zeilen Code hast (1x einblenden, 3x ausblenden), sondern eben nur noch die 1x einblenden Zeile.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)      
  if Target.Address="$B$4" then
    ActiveSheet.Unprotect Password:="123"
    Rows(5).Hidden = true
    Rows(6).Hidden = True
    Rows(7).Hidden = True
    Rows(8).Hidden = True
    select case trim$(target.value)
      case "Rund"
        Rows(5).Hidden = False
        Range("D7, F7, D6, F6, H6, D8, F8, H8").ClearContents
      case "Eckig"
        Rows(6).Hidden = False '?
        [ ... ]
    End select
    ActiveSheet.Protect Password:="123"
  end if
End Sub

Eine dieser 3 Alternativen sollte eigentlich das machen, was Du brauchst und außerdem für spätere Erweiterungen gewappnet sein.

Gruß, Michael

 

Die dritte Alternative passt perfekt für meine Anwendung, vielen Dank für die ausführliche Erklärung und Hilfe :-)

Leider habe ich nun ein weiteres Problem: In der Zelle Z1 wird eine Rechnung angestellt und je nach Ergebnis die Werte "Ja" oder "Nein" ausgegeben, sobald Ja angezeigt wird, soll die Zeile 8 eingeblendet und bei Nein ausgeblendet werden. Dazu in Zelle8:

=WENN($C$20=0;"Nein";"Ja")


Ich hatte gehofft, dass es ebenfalls über diesen Weg gelöst werden kann. Es scheint aber nicht zu funktionieren, da die Werte nicht manuell eingetragen werden. Ich habe es mit Hilfe von Google unter Einsatz von Private Sub Worksheet_Calculate() versucht:

Private Sub Worksheet_Calculate()
If [Z1]=[X1] Then
ActiveSheet.Unprotect Password:="123"
Rows(8).Hidden = False
ActiveSheet.Protect Password:="123"
Else
If [Z1]=[Y1] Then
ActiveSheet.Unprotect Password:="123"
Rows(8).Hidden = True
ActiveSheet.Protect Password:="123"
End If
End Sub

Die Zeilen X1 und Y1 habe ich als Hilfszellen eingebaut, wobei in Zeile X1 Ja und Y1 Nein steht. Bei dieser Variante stürzt aber Excel ab und gibt vorher den Laufzeitfehler "Die Mthode Hidden für das Objekt Range ist fehlgeschlagen" aus.

Da ich den Fehler nicht beheben konnte, habe ich es auf eine andere Weise versucht: Ich habe folgende Makros geschrieben:

Public Function MakroJa() As String
Worksheets("Tabelle1").Unprotect Password:="123"
Worksheets("Tabelle1").Rows(8).Hidden = False
Worksheets("Tabelle1").Protect Password:="123"
End Function

Public Function MakroNein() As String
Worksheets("Tabelle1").Unprotect Password:="123"
Worksheets("Tabelle1").Rows(8).Hidden = True
Worksheets("Tabelle1").Protect Password:="123"
End Function

In Zelle Z1 werden dann statt "Ja" und "Nein" die Makros MakroJa() und MakroNein() aufgerufen:

=WENN($C$20=0;MakroNein();"MakroJa())

Auch hier keinerlei Verbesserung, in der Zelle Z1 wird lediglich #Name? angezeigt. Nun bin ich mal wieder mit meinem Latein am Ende, kann mir jemand weiterhelfen?

 

Hallo,

du musst statt

Code:

Rows(8).Hidden = False
'... bzw. ...
Rows(8).Hidden = True

Folgendes programmieren:

Code:

Rows(8)[COLOR="#0000CD"].EntireRow[/COLOR].Hidden = False
'... bzw. ...
Rows(8)[COLOR="#0000CD"].EntireRow[/COLOR].Hidden = True

Dann kennt Excel die Hidden-Eigenschaft (zuverlässig lesend/schreibend).

 

Hi,

so erstmal frohes Neues ;-) bin durch die Feiertage erst jetzt dazu gekommen, deinen Tipp umzusetzen. Leider ohne Erfolg, da er noch immer die gleiche Fehlermeldung anzeigt und abstürzt.

Hast du eventuell eine andere Idee?

Schonmal Danke und Gruß

 

Moinsens!

Zwei Sachen:

a) Wenn Dein Source wirklich genau so aussieht, müßte es einen Fehler "IF ohne END" geben. Wenn Du aber die Worte "Else" und "If" zu einem Wort "ElseIf" zusammenziehst, also ohne Leerzeichen, dann müßte es gehen.

b) Bist Du sicher, daß das "Calculate" Ereignis überhaupt aufgerufen wird? Probier mal ein
msgbox [z1]
als erste Zeile in dem Calculate-Sub und schau mal, ob da überhaupt was kommt.

Gruß, Michael

 

Heyho,

so nach mehreren Versuchen und etlichen Fehlermeldungen, bin ich nun auf folgende Lösung gekommen:

Private Sub Worksheet_Change(ByVal Target As Range)

If [z1] = [X1] Then
ActiveSheet.Unprotect Password:="123"
Rows(8).Hidden = False
ActiveSheet.Protect Password:="123"
Else

If [z1] = [Y1] Then
ActiveSheet.Unprotect Password:="123"
Rows(8).Hidden = True
ActiveSheet.Protect Password:="123"

End If
End If
End Sub

Keine Ahnung wieso ich es unbedingt über Calculate machen wollte :-D

Funktioniert aber einwandfrei. Vielen Dank für eure zahlreichen Bemühungen und Hilfen :-)

 

Leider ist meine Excel-Tabelle dadurch ziemlich langsam geworden, da er anscheinend bei jeder Änderung auf dem Blatt prüft.

Gibt es eine Möglichkeit,dass er nur prüft,wenn eine Änderung in Zelle B2,B3,F4 sowie H5 vorgenommen wurde (,sodass die Tabelle wieder flüssiger läuft)? Funktioniert das wieder mit Target.Adress?

 

Hallo,

das Change-Ereignis wird sehr oft ausgelöst und entsprechend oft wird dann natürlich die Behandlungsroutine aufgerufen und ausgeführt und, was das ganz Schlimme dabei ist, werden durch die Behandlungsroutine selbst wieder Change-Ereignisse produziert, die ihrerseits wieder durch die Behandlungsroutine behandelt werden müssen - aus einem Schneeball wird gewissermaßen eine Lawine.

Daher ist es extrem ratsam, sobald man sich entschließt, das Change-Event zu behandeln, sofort Vorsichtsmaßnahmen zu ergreifen:
1) Abschalten der Ereigniserzeugung am Anfang und Einschalten am Ende des Makros auf Anwendungsebene (Application.EnableEvents=False/True).
2) Zum Zweck der sicheren Wiedereinschaltung am Ende des Makros muss auch eine (minimale) Fehlerbehandlung eingebaut sein (On Error GoTo Err_Change, .... Err_Change:)
3) Am Anfang des Makros eine Abfrage einbauen, die bewirkt, dass nur wirklich in den unbedingt notwendigen Fällen das eigentliche Behandlungsmakro ausgeführt wird.
4) Die Anweisungen, die im eigentlichen Behandlungsmakro enthalten sind, möglichst auf Schnelligkeit optimieren, um langsame Befehle auszumerzen.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   
   With Target
     If InStr("$B$2|$B$3|$F$4|$H$5", .Address) = 0 Then Exit Sub
   End With
   
   Application.EnableEvents = False
   On Error GoTo Err_Change
   Dim Z1 As Variant
   
   With Me
      Z1 = .Range("Z1").Value
      If Z1 = .Range("X1").Value Then
         .Unprotect Password:="123"
         .Rows(8).Hidden = False
         .Protect Password:="123"
      Else
         If Z1 = .Range("Y1").Value Then
            .Unprotect Password:="123"
            .Rows(8).Hidden = True
            .Protect Password:="123"
         End If
      End If
   End With

Err_Change:
   Application.EnableEvents = True
   
End Sub