Microsoft Excel

Herbers Excel/VBA-Archiv

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

Zellüberwachung mit Makro | Herbers Excel-Forum


Betrifft: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 02.02.2012 12:56:20

Hallo Forum,

ich habe folgendes, funktionierendes Makro in einer Arbeitsmappe:

Sub Wert_8_ausblenden()
    
    If Sheets("Daten").Range("F14") = "Nein" Then
    Sheets("Hilfstabelle").Select
    Columns("K:K").Select
    Range("K2").Activate
    Selection.EntireColumn.Hidden = True
    Else
    Sheets("Hilfstabelle").Columns("K:K").EntireColumn.Hidden = False
    End If
    
    If Sheets("Daten").Range("F14").Value = "Nein" Then
    Sheets("Daten").Range("D14") = "0"
    End If
    
    If Sheets("Daten").Range("F14").Value = "Nein" Then
    Sheets("Daten").Range("C14") = "0"
    End If
    
    If Sheets("Daten").Range("F14") = "Nein" Then
    Sheets("Diagramm").Select
    Columns("K:K").Select
    Range("K2").Activate
    Selection.EntireColumn.Hidden = True
    Else
    Sheets("Diagramm").Columns("K:K").EntireColumn.Hidden = False
    End If
    
    If Sheets("Daten").Range("F14") = "Nein" Then
    Sheets("Diagramm").Select
    Rows("36:36").Select
    Selection.EntireRow.Hidden = True
    Else
    Sheets("Diagramm").Rows("36:36").EntireRow.Hidden = False
    End If
    
    Tabelle4.Range("C14:D14").NumberFormat = Tabelle4.Range("WertFormat").NumberFormat
    
    Sheets("Diagramm").Select
    Range("A1").Select
    
    Sheets("Hilfstabelle").Select
    Range("A1").Select
    
    Sheets("Daten").Select
    Range("F14").Select
        
End Sub
Derzeit habe ich aus Testgründen das Makro im Modul stehen. Ziel ist es nun, das Makro im Hintergrund automatisch ausführen zu lassen. D. h. im Blatt "Daten" soll die Zelle F14 auf den Inhalt bzw. eine Änderung zu überwachen und automatisch auszuführen.
Nun meine Frage: Muss da etwas umgeschrieben werden, bzw. wo muss ich den Code hinkopieren, damit es automatisch ausgeführt wird?

Wer kann mir helfen?

Vielen Dank im Voraus für eine Rückmeldung.

Viele Grüße

Ralf

  

Betrifft: AW: Zellüberwachung mit Makro von: Rudi Maintaire
Geschrieben am: 02.02.2012 13:08:09

Hallo,
im Klassenmodul von 'Daten':

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 And Target.Address(0, 0) = "F14" Then
    Sheets("Hilfstabelle").Columns(11).Hidden = Target = "Nein"
    Sheets("Diagramm").Columns(11).Hidden = Target = "Nein"
    Sheets("Diagramm").Rows(36).Hidden = Target = "Nein"
    
    If Target = "Nein" Then
      Sheets("Daten").Range("D14") = "0"
      Range("C14") = "0"
    End If
    
    Tabelle4.Range("C14:D14").NumberFormat = Tabelle4.Range("WertFormat").NumberFormat
    Target.Select
  End If
End Sub

Gruß
Rudi


  

Betrifft: AW: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 02.02.2012 13:13:24

Hallo Rudi,

es gibt bei Deiner Vorgehensweise einen Laufzeitfehler 1004 in dieser zeile:

Tabelle4.Range("C14:D14").NumberFormat = Tabelle4.Range("WertFormat").NumberFormat

Ich frage mich warum der Code nur noch so kurz ist. Mein Code ist mit dem makrorekorder generiert, d. h. sicherlich nicht optimal. Ich denke schon, dass sich der Code optimieren, verkürzen lässt, aber gleich so viel?

Viele Grüße

Ralf


  

Betrifft: AW: Zellüberwachung mit Makro von: Rudi Maintaire
Geschrieben am: 02.02.2012 13:25:09

Hallo,
gibt es Tabelle4? Gibt es dort den Namen "Wertformat"?

...aber gleich so viel?
sicher!

Gruß
Rudi


  

Betrifft: AW: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 02.02.2012 13:32:03

Hallo Rudi,

ok, der Laufzeitfehler ist weg, es musste Tabelle2.Range.... heißen.
Es wird aber im Blatt Diagramm die Spalte K und die Zeile 36 nicht ausgeblendet. Es wird scheinbar kein Code ausgeführt.

???

Viele Grüße

Ralf


  

Betrifft: AW: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 02.02.2012 13:36:13

Hallo Rudi,

passt.
Ich habe den Fehler gefunden.
Vielen Dank für Deine Hilfe.

Viele Grüße

Ralf


  

Betrifft: AW: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 02.02.2012 13:50:55

Hallo Rudi,

jetzt bin dabei den Code zu duplizieren und auf die anderen Spalten/Zeilen zu übertragen.
Der Code sieht jetzt so aus:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Wert 8 ausblenden
    If Target.Count = 1 And Target.Address(0, 0) = "F14" Then
     Sheets("Hilfstabelle").Columns(11).Hidden = Target = "Nein"
     Sheets("Diagramm").Columns(11).Hidden = Target = "Nein"
     Sheets("Diagramm").Rows(36).Hidden = Target = "Nein"
     
     If Target = "Nein" Then
       Sheets("Daten").Range("D14") = "0"
       Range("C14") = "0"
     End If
     
     Tabelle2.Range("C14:D14").NumberFormat = Tabelle2.Range("WertFormat").NumberFormat
     Target.Select
    End If

    'Wert 7 ausblenden
    If Target.Count = 1 And Target.Address(0, 0) = "F13" Then
     Sheets("Hilfstabelle").Columns(10).Hidden = Target = "Nein"
     Sheets("Diagramm").Columns(10).Hidden = Target = "Nein"
     Sheets("Diagramm").Rows(35).Hidden = Target = "Nein"
     
     Sheets("Hilfstabelle").Columns(11).Hidden = Target = "Nein"
     Sheets("Diagramm").Columns(11).Hidden = Target = "Nein"
     Sheets("Diagramm").Rows(36).Hidden = Target = "Nein"
     
     If Target = "Nein" Then
       Sheets("Daten").Range("D13") = "0"
       Range("C13") = "0"
       
       Sheets("Daten").Range("D14") = "0"
       Range("C14") = "0"
     End If
     
     Tabelle2.Range("C13:D14").NumberFormat = Tabelle2.Range("WertFormat").NumberFormat
     Target.Select
    End If

    'Wert 6 ausblenden
    If Target.Count = 1 And Target.Address(0, 0) = "F12" Then
     Sheets("Hilfstabelle").Columns(9).Hidden = Target = "Nein"
     Sheets("Diagramm").Columns(9).Hidden = Target = "Nein"
     Sheets("Diagramm").Rows(34).Hidden = Target = "Nein"
     
     Sheets("Hilfstabelle").Columns(10).Hidden = Target = "Nein"
     Sheets("Diagramm").Columns(10).Hidden = Target = "Nein"
     Sheets("Diagramm").Rows(35).Hidden = Target = "Nein"
     
     Sheets("Hilfstabelle").Columns(11).Hidden = Target = "Nein"
     Sheets("Diagramm").Columns(11).Hidden = Target = "Nein"
     Sheets("Diagramm").Rows(36).Hidden = Target = "Nein"
     
     If Target = "Nein" Then
       Sheets("Daten").Range("D12") = "0"
       Range("C12") = "0"
       
       Sheets("Daten").Range("D13") = "0"
       Range("C13") = "0"
       
       Sheets("Daten").Range("D14") = "0"
       Range("C14") = "0"
     End If
     
     Tabelle2.Range("C12:D14").NumberFormat = Tabelle2.Range("WertFormat").NumberFormat
     Target.Select
    End If
    
End Sub
Die Werte 7 bzw. 6 lassen sich aber leider nicht nach dem Muster des Wertes 8 aus- bzw. einblenden.
Kannst Du Dir den Code bitte nochmal anschauen, ob ich irgendetwas vergessen habe.

Vielen Dank im Voraus.

Viele Grüße

Ralf


  

Betrifft: AW: Zellüberwachung mit Makro von: Rudi Maintaire
Geschrieben am: 02.02.2012 14:21:49

Hallo,
meinst du so?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lngRow As Long
    'Wert 8 ausblenden
    If Target.Count = 1 And Target.Column = 6 Then
      lngRow = Target.Row
     Sheets("Hilfstabelle").Columns(lngRow - 3).Hidden = Target = "Nein"
     Sheets("Diagramm").Columns(lngRow - 3).Hidden = Target = "Nein"
     Sheets("Diagramm").Rows(lngRow + 24).Hidden = Target = "Nein"
     
     If Target = "Nein" Then
      Range(Cells(lngRow, 3), Cells(lngRow, 4)) = 0
     End If
     
     With Tabelle2
      .Range(.Cells(lngRow, 3), .Cells(lngRow, 4)).NumberFormat = .Range("WertFormat"). _
NumberFormat
      End With
     Target.Select
    End If
End Sub

Gruß
Rudi


  

Betrifft: AW: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 02.02.2012 14:34:28

Hallo Rudi,

der Hammer.
Ich habe mir den Code für die einzelnen Werte entsprechend runterkopiert. der Code wurde natürlich immer länger.
Du kürzt diesen wieder auf ein Minimum zusammen. Super.
In dem Zusammenhang ist mir jetzt noch eine Idee gekommen:
Beispiel: Wenn die Zeile 8 auf "Nein" gestellt wird, soll das Makro für Zeile 9 bis 14 automatisch auch Nein gesetzt werden.
Wird eine Zeile auf nein gestellt, sollen alle darunter liegenden Zeilen bis Zeile 14 auch auf Nein gestellt werden, und die entsprechenden Zeilen/Spalten in den anderen Tabellenblättern ausgeblendet werden.
Da Dein Code nun dermaßen kurz ist, kann ich da natürlich keine Bedingungen mehr erkennen.
Ich hoffe Du kannst mir dabei nochmal helfen.

Viele Grüße

Ralf


  

Betrifft: AW: Zellüberwachung mit Makro von: Rudi Maintaire
Geschrieben am: 02.02.2012 15:09:43

Hallo,

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngRow As Long, i As Long, blnNein
  'Wert 8 ausblenden
  If Target.Count = 1 And Target.Column = 6 Then
    lngRow = Target.Row
    Select Case lngRow
      Case 8 To 14
        blnNein = LCase(Target) = "nein"
        Sheets("Hilfstabelle").Columns(lngRow - 3).Hidden = blnNein
        Sheets("Diagramm").Columns(lngRow - 3).Hidden = blnNein
        Sheets("Diagramm").Rows(lngRow + 24).Hidden = blnNein
        
        If blnNein Then
          Range(Cells(lngRow, 3), Cells(lngRow, 4)) = 0
        End If
        
        With Tabelle2
          .Range(.Cells(lngRow, 3), .Cells(lngRow, 4)).NumberFormat = _
            .Range("WertFormat").NumberFormat
        End With
        
        If lngRow < 14 And blnNein Then Cells(lngRow + 1, 6) = "Nein"
    End Select
  End If
End Sub

Gruß
Rudi


  

Betrifft: AW: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 02.02.2012 15:23:45

Hallo Rudi,

ich weiß gar nicht was ich sagen soll.
ICH BIN BEGEISTERT!!!

Vielen Dank für die kompetente Hilfe.

Viele Grüße

Ralf


  

Betrifft: AW: Zellüberwachung mit Makro von: Ralf
Geschrieben am: 03.02.2012 12:00:00

Hallo Rudi,

Du hast mir gestern mit einem super guten Makro weitergeholfen:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngRow As Long, i As Long, blnNein
  
  If Target.Count = 1 And Target.Column = 6 Then
    lngRow = Target.Row
    Select Case lngRow
      Case 8 To 14
        blnNein = LCase(Target) = "nein"
        Sheets("Hilfstabelle").Columns(lngRow - 3).Hidden = blnNein
        Sheets("Diagramm").Columns(lngRow - 3).Hidden = blnNein
        Sheets("Diagramm").Rows(lngRow + 24).Hidden = blnNein
        
        If blnNein Then
          Range(Cells(lngRow, 3), Cells(lngRow, 4)) = 0
        End If
        
        With Tabelle2
          .Range(.Cells(lngRow, 3), .Cells(lngRow, 4)).NumberFormat = _
            .Range("WertFormat").NumberFormat
        End With
        
        If lngRow < 14 And blnNein Then Cells(lngRow + 1, 6) = "Nein"
    End Select
  End If
End Sub

Jetzt ist es dummerweise so, dass die Datenbeschriftungen im Diagramm nach Ausblenden der Spalte+Zeile nicht mehr passen. Diese müssten nun so angepasst werden:
Bei Ausblenden von Spalte "K" im Blatt Diagramm (Zelle "F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(11).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(10).DataLabel.Text = "=Daten!$E$15"

Bei Ausblenden von Spalte "J bis K" im Blatt Diagramm (Zellen "F13:F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(10).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(9).DataLabel.Text = "=Daten!$E$15"

Bei Ausblenden von Spalte "I bis K" im Blatt Diagramm (Zellen "F12:F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(9).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(8).DataLabel.Text = "=Daten!$E$15"

Bei Ausblenden von Spalte "H bis K" im Blatt Diagramm (Zellen "F11:F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(8).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(7).DataLabel.Text = "=Daten!$E$15"

Bei Ausblenden von Spalte "G bis K" im Blatt Diagramm (Zellen "F10:F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(7).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(6).DataLabel.Text = "=Daten!$E$15"

Bei Ausblenden von Spalte "F bis K" im Blatt Diagramm (Zellen "F9:F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(6).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(5).DataLabel.Text = "=Daten!$E$15"

Bei Ausblenden von Spalte "E bis K" im Blatt Diagramm (Zellen "F8:F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(5).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(4).DataLabel.Text = "=Daten!$E$15"

Bei Ausblenden von Spalte "D bis K" im Blatt Diagramm (Zellen "F7:F14" im Blatt Daten wird auf "Nein" gestellt) sollen die Datenbeschriftungen so angepasst werden:

Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(4).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(3).DataLabel.Text = "=Daten!$E$15"

Steht der Zellbereich F7:F14 auf "Ja" (Ausgangskonfiguration) sollen die Datenbeschriftungen so formatiert werden:
Sheets("Diagramm").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Points(12).DataLabel.Text = "=Daten!$D$6"
ActiveChart.SeriesCollection(2).Points(11).DataLabel.Text = "=Daten!$E$15"
ActiveChart.SeriesCollection(2).Points(10).DataLabel.Text = "=Daten!$E$14"
ActiveChart.SeriesCollection(2).Points(9).DataLabel.Text = "=Daten!$E$13"
ActiveChart.SeriesCollection(2).Points(8).DataLabel.Text = "=Daten!$E$12"
ActiveChart.SeriesCollection(2).Points(7).DataLabel.Text = "=Daten!$E$11"
ActiveChart.SeriesCollection(2).Points(6).DataLabel.Text = "=Daten!$E$10"
ActiveChart.SeriesCollection(2).Points(5).DataLabel.Text = "=Daten!$E$9"
ActiveChart.SeriesCollection(2).Points(4).DataLabel.Text = "=Daten!$E$8"
ActiveChart.SeriesCollection(2).Points(3).DataLabel.Text = "=Daten!$E$7"
ActiveChart.SeriesCollection(2).Points(2).DataLabel.Text = "=Daten!$C$6"
ActiveChart.SeriesCollection(2).Points(1).DataLabel.Text = "=Daten!$C$3"

Desweiteren soll für Datenbeschriftungen diese Formatierung gelten:
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Ich habe probiert es selber mit if Bedingungen hinzubekommen, leider ohne Erfolg.

Kannst Du mir bitte nochmal helfen?

Vielen Dank im Voraus.

Viele Grüße

Ralf