Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1796to1800
1796to1800
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Private Sub Worksheet_Change bei Formeln

Forumthread: Private Sub Worksheet_Change bei Formeln

Private Sub Worksheet_Change bei Formeln
09.12.2020 12:48:10
Thierry
Hallo
Ich habe fast keine VBA Kenntnisse. Dennoch habe ich folgenden Code gefunden und kopiert.
Dieser funktioniert auch einwandfrei, wenn ich die Daten manuell in Zelle A2 eingebe und mit Enter bestätige.
Das Problem besteht darin, dass in Zelle A2 eine Formal (=Tabelle1!B2) ist. Es scheint, dass der VBA-Code die Änderungen in Telle A2 nicht merkt.
Was muss geändert werden, damit die Zelle bzw. die Formal die Änderungen erkennt.
Vielen Dank für Eure Hilfe
Thierry

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A2" Then
Select Case Target.Value
Case "Gennaio"
Rows.EntireRow.Hidden = False
Rows("10:52").EntireRow.Hidden = True
Case "Febbraio"
Rows.EntireRow.Hidden = False
Rows("14:52").EntireRow.Hidden = True
Case "Marzo"
Rows.EntireRow.Hidden = False
Rows("18:52").EntireRow.Hidden = True
Case "Aprile"
Rows.EntireRow.Hidden = False
Rows("22:52").EntireRow.Hidden = True
Case "Maggio"
Rows.EntireRow.Hidden = False
Rows("26:52").EntireRow.Hidden = True
Case "Giugno"
Rows.EntireRow.Hidden = False
Rows("30:52").EntireRow.Hidden = True
Case "Luglio"
Rows.EntireRow.Hidden = False
Rows("34:52").EntireRow.Hidden = True
Case "Agosto"
Rows.EntireRow.Hidden = False
Rows("38:52").EntireRow.Hidden = True
Case "Settembre"
Rows.EntireRow.Hidden = False
Rows("42:52").EntireRow.Hidden = True
Case "Ottobre"
Rows.EntireRow.Hidden = False
Rows("46:52").EntireRow.Hidden = True
Case "Novembre"
Rows.EntireRow.Hidden = False
Rows("50:52").EntireRow.Hidden = True
Case Else
Rows.EntireRow.Hidden = False
End Select
End If
End Sub

Anzeige

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:12:58
Nepumuk
Hallo Thierry,
gibst du den Wert in Tabelle1 B2 manuell ein? Wenn ja, dann setzt das Makro in dessen Modul und verweise so auf die andere Tabelle:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B2" Then
        With Worksheets("Tabelle2") ' Anpassen !!!
            Select Case Target.Value
                Case "Gennaio"
                    .Rows.EntireRow.Hidden = False
                    .Rows("10:52").EntireRow.Hidden = True
                Case "Febbraio"
                    .Rows.EntireRow.Hidden = False
                    .Rows("14:52").EntireRow.Hidden = True
                Case "Marzo"
                    .Rows.EntireRow.Hidden = False
                    .Rows("18:52").EntireRow.Hidden = True
                Case "Aprile"
                    .Rows.EntireRow.Hidden = False
                    .Rows("22:52").EntireRow.Hidden = True
                Case "Maggio"
                    .Rows.EntireRow.Hidden = False
                    .Rows("26:52").EntireRow.Hidden = True
                Case "Giugno"
                    .Rows.EntireRow.Hidden = False
                    .Rows("30:52").EntireRow.Hidden = True
                Case "Luglio"
                    .Rows.EntireRow.Hidden = False
                    .Rows("34:52").EntireRow.Hidden = True
                Case "Agosto"
                    .Rows.EntireRow.Hidden = False
                    .Rows("38:52").EntireRow.Hidden = True
                Case "Settembre"
                    .Rows.EntireRow.Hidden = False
                    .Rows("42:52").EntireRow.Hidden = True
                Case "Ottobre"
                    .Rows.EntireRow.Hidden = False
                    .Rows("46:52").EntireRow.Hidden = True
                Case "Novembre"
                    .Rows.EntireRow.Hidden = False
                    .Rows("50:52").EntireRow.Hidden = True
                Case Else
                    .Rows.EntireRow.Hidden = False
            End Select
        End With
    End If
End Sub

Gruß
Nepumuk
Anzeige
sorry..
09.12.2020 13:18:42
Werner
Hallo,
..keine Ahnung warum deine Antwort bei mir noch nicht angezeigt wurde.
Gruß Werner
AW: sorry..
09.12.2020 13:23:12
Nepumuk
Hallo Werner,
ist doch egal. Dafür musst du dich nicht entschuldigen.
Gruß
Nepumuk
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:21:37
Nepumuk
Hallo Thierry,
ich nochmal. Rows + EntireRow ist doppelt gemoppelt. Das genügt:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B2" Then
        With Worksheets("Tabelle2") ' Anpassen !!!
            Select Case Target.Value
                Case "Gennaio"
                    .Rows.Hidden = False
                    .Rows("10:52").Hidden = True
                Case "Febbraio"
                    .Rows.Hidden = False
                    .Rows("14:52").Hidden = True
                Case "Marzo"
                    .Rows.Hidden = False
                    .Rows("18:52").Hidden = True
                Case "Aprile"
                    .Rows.Hidden = False
                    .Rows("22:52").Hidden = True
                Case "Maggio"
                    .Rows.Hidden = False
                    .Rows("26:52").Hidden = True
                Case "Giugno"
                    .Rows.Hidden = False
                    .Rows("30:52").Hidden = True
                Case "Luglio"
                    .Rows.Hidden = False
                    .Rows("34:52").Hidden = True
                Case "Agosto"
                    .Rows.Hidden = False
                    .Rows("38:52").Hidden = True
                Case "Settembre"
                    .Rows.Hidden = False
                    .Rows("42:52").Hidden = True
                Case "Ottobre"
                    .Rows.Hidden = False
                    .Rows("46:52").Hidden = True
                Case "Novembre"
                    .Rows.Hidden = False
                    .Rows("50:52").Hidden = True
                Case Else
                    .Rows.Hidden = False
            End Select
        End With
    End If
End Sub

Gruß
Nepumuk
Anzeige
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:44:06
Thierry
Hallo Nepumuk, vielen vielen Dank für die Lösung.
Ich habe noch eine zusätzliche Frage: Wie sieht der Code aus, wenn mehrere Tabellenblätter geändert werden müssen (Tabelle10, Tabelle11, Tabelle12)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "B2" Then
With Worksheets("Tabelle10")
Gruss. Thierry
Anzeige
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:47:18
Nepumuk
Hallo Thierry,
soll in allen diesen Tabellen das ausblenden nach dem selben Muster stattfinden?
Gruß
Nepumuk
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:49:55
Thierry
Ja, genau.
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:54:07
Nepumuk
Hallo Thierry,
dann so:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vntWorksheet As Variant
    If Target.Address(0, 0) = "B2" Then
        For Each vntWorksheet In Array("Tabelle2", "Tabelle3", "Tabelle4") ' Anpassen !!!
            With Worksheets(vntWorksheet)
                Select Case Target.Value
                    Case "Gennaio"
                        .Rows.Hidden = False
                        .Rows("10:52").Hidden = True
                    Case "Febbraio"
                        .Rows.Hidden = False
                        .Rows("14:52").Hidden = True
                    Case "Marzo"
                        .Rows.Hidden = False
                        .Rows("18:52").Hidden = True
                    Case "Aprile"
                        .Rows.Hidden = False
                        .Rows("22:52").Hidden = True
                    Case "Maggio"
                        .Rows.Hidden = False
                        .Rows("26:52").Hidden = True
                    Case "Giugno"
                        .Rows.Hidden = False
                        .Rows("30:52").Hidden = True
                    Case "Luglio"
                        .Rows.Hidden = False
                        .Rows("34:52").Hidden = True
                    Case "Agosto"
                        .Rows.Hidden = False
                        .Rows("38:52").Hidden = True
                    Case "Settembre"
                        .Rows.Hidden = False
                        .Rows("42:52").Hidden = True
                    Case "Ottobre"
                        .Rows.Hidden = False
                        .Rows("46:52").Hidden = True
                    Case "Novembre"
                        .Rows.Hidden = False
                        .Rows("50:52").Hidden = True
                    Case Else
                        .Rows.Hidden = False
                End Select
            End With
        Next
    End If
End Sub

Gruß
Nepumuk
Anzeige
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:50:33
onur
Hi,
Ich finde es ja toll von Dir, dass du dich bei EINEM bedankst, wenn MEHRERE Leute sich die Arbeit gemacht haben, dir zu helfen.
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 14:17:05
Thierry
Hallo Onur
VBA ist für mich eine Fremdsprache, sei mir bitte daher nicht böse, wenn ich mich auf eine Lösung konzentriert habe. Mein Dank gilt auch Dir und allen anderen. Thierry
Anzeige
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 14:23:21
onur
"VBA ist für mich eine Fremdsprache" - ja klar, daran wird es wohl liegen.
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:17:08
Werner
Hallo,
Ich habe fast keine VBA Kenntnisse
Warum dann Status VBA gut?
In dem Fall würde ich die Eingabe in Zelle B2 in Tabelle1 überwachen.
Den Code also ins Codemodul von Tabelle1 
Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Tabelle2")
If Target.Address(0, 0) = "B2" Then
Select Case Target.Value
Case "Gennaio"
.Rows.EntireRow.Hidden = False
.Rows("10:52").EntireRow.Hidden = True
Case "Febbraio"
.Rows.EntireRow.Hidden = False
.Rows("14:52").EntireRow.Hidden = True
Case "Marzo"
.Rows.EntireRow.Hidden = False
.Rows("18:52").EntireRow.Hidden = True
Case "Aprile"
.Rows.EntireRow.Hidden = False
.Rows("22:52").EntireRow.Hidden = True
Case "Maggio"
.Rows.EntireRow.Hidden = False
.Rows("26:52").EntireRow.Hidden = True
Case "Giugno"
.Rows.EntireRow.Hidden = False
.Rows("30:52").EntireRow.Hidden = True
Case "Luglio"
.Rows.EntireRow.Hidden = False
.Rows("34:52").EntireRow.Hidden = True
Case "Agosto"
.Rows.EntireRow.Hidden = False
.Rows("38:52").EntireRow.Hidden = True
Case "Settembre"
.Rows.EntireRow.Hidden = False
.Rows("42:52").EntireRow.Hidden = True
Case "Ottobre"
.Rows.EntireRow.Hidden = False
.Rows("46:52").EntireRow.Hidden = True
Case "Novembre"
.Rows.EntireRow.Hidden = False
.Rows("50:52").EntireRow.Hidden = True
Case Else
.Rows.EntireRow.Hidden = False
End Select
End If
End With
End Sub
Worksheet_Change reagiert nur auf händische Eingabe und nicht auf Änderungen durch Formeln.
Für Änderungen in Formeln gibt es das Worksheet_Calculate Ereignis. Dort gibt es allerdings kein Target. Du kannst also nicht konkret eine bestimmte Zelle auf Formeländerung überwachen. Das Ereignis löst bei jeder Formelberechnung irgendwo auf dem Blatt aus.
Gruß Werner
Anzeige
AW: Private Sub Worksheet_Change bei Formeln
09.12.2020 13:46:16
Thierry
Hallo Werner, bei den Kenntnissen habe ich mich vertippt! Sorry
;

Forumthreads zu verwandten Themen

Private Sub Worksheet_Activate()
Vera

Liebe Forumsmitglieder
mir ist aufgefallen dass Worksheet_Activate() nur bei Tabellen funktioniert. z.B.
Private Sub Worksheet_Activate() MsgBox "hallo" End Sub
Bei eingefügten Diagrammen (als eigene Seite) funktioniert das aber leider nicht.
Gibt es da auch eine Methode um...
05.09.2008 09:09:00
Thread-ID: 1007583
4 Beiträge
Anzeige
Private Sub Worksheet_Change
Gordon

Moin,
ich habe folgenden Code:
Sub Anzahl_Klassen() Dim anzKl As Integer anzKl = Worksheets("Tabelle1").Cells(1, 2).Value With Worksheets("Tabelle1") If anzKl = 0 Then .Rows("5:102").RowHeight = 0 Else: .Rows("5:" & anzKl * 11 + 3).RowHeight = 12.75 .Rows(anzKl * 11 + 5 & ":10...
19.11.2008 10:35:41
Thread-ID: 1025907
5 Beiträge
Private Sub Worksheet_Change
Larissa

Hallo VBA-Künstler,
Ich habe in meiner Excelmappe schon ein längeres
Private Sub Worksheet_Change(ByVal Target As Range)
auf dem ersten Tabellenblatt eingebaut.
Bisher habe ich es nur so hinbekommen, dass die direkte Spalte angesprochen wird, in der man gerade etwas einträgt.
05.10.2009 19:39:36
Thread-ID: 1107325
18 Beiträge
Anzeige
Frage zu Private Sub WorksheetActivate() | Herbers Excel-Forum
Angelo Wi
Frage zu Private Sub Worksheet_Activate()
Kann ich ein anderes Sub starten zB Application.Run "BLZusKopieren"
funktioniert bei mir nicht
03.02.2010 21:28:30
Thread-ID: 1135596
6 Beiträge
Private Sub WorksheetActivate + Application.Run ? | Herbers Excel-Forum
Angelo Wi
Guten Tag
ich möchte dass in diesem sub über Application.Run "Kontrolle" ein weiteres Makro sofort gestartet wird
damit das gesamte Rechenwerk überprüft und kontrolliert werden kann
bei mir funktioniert das so nicht
Herzlichen Dank für die Lösung
mfG Angelo
Private Sub Works...
05.02.2010 09:48:04
Thread-ID: 1136029
5 Beiträge
Private Sub Worksheet_Activate()
Bastian

Liebe Leute,
ich habe ein Makro mit einem "Private Sub Worksheet_Activate()" - Ereignis.
Das Makro filtert eine Tabelle mit Werten, die dann in einer Grafik verwendet werden, die in diesem Tabellenblatt liegt.
Wenn die Grafik aktiviert ist (durch Zufall bzw. unbeabsichtigt) und man...
22.04.2010 16:56:25
Thread-ID: 1153351
9 Beiträge
Private Sub Worksheet_Change(ByVal Target As Range
ing.grohn

Hallo Forum,
Worksheet_Change ist eine wunderbare Sache! (wenn sie funktioniert)
Also:
ich arbeite in einem Blatt mit: Private Sub Worksheet_Change(ByVal Target As Range)
funktioniert alles super. Nur manchmal tut das nicht!!!!
Excel beenden, neu starten, alles klar und funk...
17.06.2010 09:43:52
Thread-ID: 1162378
11 Beiträge
Private Sub Worksheet_Change in Modul auslagern
Claudia

Hallo zusammen,
möchte folgenden Code aus Private Sub Worksheet_Change(ByVal Target As Range) in ein Modul auslagern. Soll heissen: unabhä _ _ ngig von einer Eingabe wie dargestellt soll ein Makro hingehen und prüfen, ob aufgrund der _ Werte im genannten I-Bereich noch Änderungen / Ergän...
12.09.2010 21:11:38
Thread-ID: 1177131
3 Beiträge
Private Sub Worksheet_Change
Claudia

Hallo zusammen,
brauche noch einmal Eure Hilfe! Ich nutze folgenden Code.
Wie müsste ich den Code ändern, wenn die Änderungen nicht in dem aktiven Blatt sondern im Reiter mit dem Namen "Test" der gleichen Datei erfolgen müsste. Geht das übergreifend?
Vielen DAnk!
LG
Claudia<...
19.12.2010 18:39:40
Thread-ID: 1192114
9 Beiträge
Problem mit Private Sub Worksheet_Change(ByVal Tar
Hans

Hallo, ich habe folgendes Problem,
Ich habe im Datenblatt ein PrivateSub Worksheet_Change .. wodurch 4 Formeln in die Zellen C5, D5 .. eingetragen.
Man gibt in Zelle B5 eine Ersatzteilnummer ein, dann werden in den folgenden 4 Zellen Bezeichnung, Preis und so weiter aus der Tabelle Pre...
06.01.2011 06:38:20
Thread-ID: 1194082
2 Beiträge
Private Sub Worksheet_Select???
Marcy


Hallo Forum,
wie muss ich dass VBA ändern, dass es ausreicht, wenn in Stalte A ein Zelle markiere (nur ein Mausklick).
Wegen einer freigegebenen Mappe will ich so Konflikte vermeiden.
Gruß Marcy
Private Sub Worksheet_Select(ByVal Target As Excel.Range) If Target.Row = 1 And...
06.03.2012 12:58:51
Thread-ID: 1253846
5 Beiträge
Private Sub Worksheet_Change(ByVal Target As Excel
Naklov

Hallo,
Kann man "Private Sub Worksheet_Change(ByVal Target As Excel.Range)" bei mehreren Subs verwenden?
Danke schon mal fürs Antworten!
VG Naklov
25.04.2012 12:29:06
Thread-ID: 1260921
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Verwendung von Worksheet_Change in Excel VBA

Schritt-für-Schritt-Anleitung

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Wähle das entsprechende Arbeitsblatt aus, in dem Du den Code verwenden möchtest.

  3. Füge den folgenden Code in das Modul des Arbeitsblatts ein:

    Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "B2" Then
       Select Case Target.Value
           Case "Gennaio"
               Rows.Hidden = False
               Rows("10:52").Hidden = True
           ' Weitere Fälle hier...
           Case Else
               Rows.Hidden = False
       End Select
   End If
End Sub

  4. Achte darauf, dass Du den Code an Deine Bedürfnisse anpasst, insbesondere die Zellen und Bedingungen.

Häufige Fehler und Lösungen

  • Fehler: Änderungen in Zelle A2 werden nicht erkannt.

    • Lösung: Der Worksheet_Change-Ereignis wird nur bei manueller Eingabe ausgelöst. Um Änderungen durch Formeln zu erkennen, verwende Worksheet_Calculate.

  • Fehler: Der Code funktioniert nur für eine Zelle.

    • Lösung: Um mehrere Zellen zu überwachen, kannst Du eine Schleife verwenden oder zusätzliche Bedingungen hinzufügen.

Alternative Methoden

Wenn Du Änderungen in Formeln überwachen möchtest, kannst Du das Worksheet_Calculate-Ereignis verwenden:

Private Sub Worksheet_Calculate()
    ' Hier kannst Du den Code zur Auswertung von Formeln platzieren
End Sub

Beachte, dass Worksheet_Calculate keine spezifische Zieladresse hat, sondern bei jeder Berechnung auf dem Blatt ausgelöst wird.

Praktische Beispiele

  • Beispiel 1: Wenn Du mehrere Tabellenblätter gleichzeitig ansprechen möchtest:

    Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "B2" Then
       Dim ws As Variant
       For Each ws In Array("Tabelle1", "Tabelle2", "Tabelle3")
           With Worksheets(ws)
               ' Hier den Code für die Zeilenversteckung anpassen
           End With
       Next ws
   End If
End Sub

  • Beispiel 2: Verwendung von Private Sub Worksheet_Change(ByVal Target As Excel.Range) zur präzisen Adressierung.

Tipps für Profis

  • Vermeide doppelte Aufrufe: Verwende Rows.Hidden = False anstelle von Rows.EntireRow.Hidden = False, um den Code einfacher zu halten.
  • Debugging: Nutze Debug.Print in Deinem Code, um den Wert von Target in der unmittelbaren Fensteransicht zu verfolgen.
  • Leistungsoptimierung: Vermeide unnötige Berechnungen, indem Du den Application.Calculation-Modus anpasst.

FAQ: Häufige Fragen

1. Wie kann ich den Code so anpassen, dass er für mehrere Zellen funktioniert?
Du kannst mehrere If-Bedingungen oder Select Case-Anweisungen hinzufügen, um unterschiedliche Zellen zu überwachen.

2. Warum funktioniert mein Worksheet_Change-Code, wenn ich mit der Maus klicke, aber nicht bei Änderungen durch Formeln?
Das Worksheet_Change-Ereignis reagiert nur auf manuelle Eingaben. Für Änderungen durch Formeln musst Du Worksheet_Calculate verwenden.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige