Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
824to828
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
824to828
824to828
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Zellen schützen und farbig markieren

Zellen schützen und farbig markieren
29.11.2006 10:00:15
Kai
Hallo,
wie kann ich es bewerkstelligen, dass wenn in einem Zellenbereich eine bestimmte Summe eingegeben wird, Excel a) diesen Bereich dann sperrt und b) den kompletten Bereich farbig markiert? Wird die vorgegebene Summe wieder unterschritten, soll der Breich wieder freigegeben werden.
Beispiel: Für beispielsweise den Bereich G10 bis G20 ist vorgesehen, dass wenn die Werte die Summe 8 ergeben, der Bereich gesperrt wird. Erst wenn die Summe weniger als 8 ergibt, soll eine Eingabe in den anderen Zellen (G10:G20) wieder möglich sein.
Ist dies machbar?
Schon mal danke im voraus.
Gruß
Kai

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellen schützen und farbig markieren
29.11.2006 10:04:52
Jens
Hallo Kai,
kurze Zwischenfrage:
Gibst Du die Werte in G10 bis G20 manuell ein, oder wie kommen die Werte dorthin?
MfG
Jens
AW: Zellen schützen und farbig markieren
29.11.2006 10:08:18
Kai
Hi,
diese werden von Hand eingegeben.
Gruß
Kai
AW: Zellen schützen und farbig markieren
29.11.2006 10:16:42
Jens
Hallo Kai,
Folgendes Problem:
1.Du gibst in Deinem Bereich Werte ein
2.Wenn die Summe = 8 erreicht ist, soll der Bereich geschützt werden
Soweit wäre das kein Problem.
(Ich weiß zwar nicht, wie das in Excel2003 aussieht, aber Zellschutz ist eigentlich nur bei aktivem Blattschutz aktiv.)
Was ist aber, wenn die Summe erreicht ist, und der Bereich geschützt wird?
Wie soll es dann möglich sein, dort wieder andere Werte einzugeben, damit sich die Summe ändern kann? Durch den Schutz wäre das ja nicht möglich, somit könnte sich ja auch nicht die Summe ändern und der Schutz auch nicht wieder aufgehoben werden.
Vielleicht gibt es ja tief im inneren eine XLS-Möglichkeit für dieses Problem, aber das übersteigt leider meine Fähigkeiten.
Deshalb lass ich die Frage noch offen...
Trotzdem viel Erfolg ;)
MfG
Jens
Anzeige
AW: Zellen schützen und farbig markieren
29.11.2006 10:30:21
Kai
Hallo Jens,
und wenn man es so hinbekommen könnte, dass nur die Zellen in diesem Bereich, in denen kein Wert eingetragen ist geschützt werden und die Zellen, in denen ein Wert größer als Null ist nicht geschützt werden?
LG
Kai
Vielleicht kann **IngGi** da helfen? ;-)
29.11.2006 10:37:39
Jens
Hallo Kai,
das wäre sicherlich möglich, aber leider kann ich Dir da aus dem Kopf heraus nicht so genau helfen. Bin grad auf Arbeit ohne Excel.
Solltest Du heute abend keinen Lösungsansatz haben, dann schau ich mal eben von zuhause aus :-)
Gruß
Jens
AW: Vielleicht kann **IngGi** da helfen? ;-)
29.11.2006 14:22:17
IngGi
Hallo Kai,
anderer Vorschlag. Sobald der Maximalwert für einen Bereich überschritten wird, sind nur noch Eingaben möglich, bei denen der bisherige Wert verringert wird. Erst wenn der Maximalwert nicht mehr überschritten ist, sind wieder Erhöhungen in Einzelzellen des Bereiches möglich.
Ich habe dir mal eine Beispielmappe hochgeladen. Im Blatt "Tabelle1" sind zwei Bereiche definiert. A1:A10 und C1:C10. Für diese Bereiche ist eine Gültigkeit hinterlegt, auf Grund derer generell nur die Eingabe von Zahlen zwischen 0 und 1000 möglich ist. Wird im Bereich A1:A10 die Maximalsumme von 500 überschritten, sind in diesem Bereich keine Werterhöhungen, nur noch Wertminderungen möglich. Für den Bereich C1:C10 ist eine Maximalsumme von 100 eingestellt.
Das Makro benötigt ein zweites Tabellenblatt mit demselben Namen und "alt" hintenangestellt. In diesem zweiten Tabellenblatt "merkt" sich Excel während das Makro läuft den alten Wert der veränderten Zelle. Das geschieht dadurch, dass jede Änderung im Originaltabellenblatt auch in dieser Kopie gemacht wird. Allerdings nicht sofort, sondern eben erst am Ende des Makros.
https://www.herber.de/bbs/user/38610.xls
Gruß Ingolf
Anzeige
AW: Vielleicht kann **IngGi** da helfen? ;-)
29.11.2006 14:47:02
Kai
Hallo Ingolf,
diese Lösung ist auch in Ordnung. Bekomme nur gerade beim wiederholten testen die Meldung "Laufzeitfehler 91 - Objektvariable oder With-Blockvariable nicht festgelegt" und es wird die Zeile
If Intersect(Target, Range("A1:A10")) Then
farbig markiert.
Liese sich dein Lösungsvorschlag auch so gestalten, ohne ohne dass ein zweites Tabellenblatt notwendig ist? Denn das wäre die Lösung die ich bräuchte.
Gruß
Kai
AW: Vielleicht kann **IngGi** da helfen? ;-)
29.11.2006 16:28:58
IngGi
Hallo Kai,
die Fehlermeldung sollte mit folgendem, korrigiertem Makro nicht mehr kommen. Ich habe keinen blassen Schimmer, warum diese erscheint, habe den Auslöser aber mit der Korrektur umschifft. Bei mir kam die Fehlermeldung übrigens nur, wenn ich den Inhalt einer einzelnen Zelle über "Entf" gelöscht habe.
Statt ein zweites Tabellenblatt zu verwenden, könnte man die alten Zellinhalte auch in eine Datenfeldvariable schreiben. Ich ändere das Makro nochmal entsprechend ab, komme allerdings erst heute abend oder morgen vormittag dazu. Hier aber erstmal das Makro mit der Änderung bezüglich der Fehlermeldung:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
   If Target = "" Then
      Worksheets(Target.Parent.Name & "alt").Range(Target.Address) = Target
   Else
      If Intersect(Target, Range("A1:A10")) Then
         If Application.WorksheetFunction.Sum(Range("A1:A10")) _
            - Target + Worksheets(Target.Parent.Name & "alt").Range(Target.Address) > 500 _
            And Target > Worksheets(Target.Parent.Name & "alt").Range(Target.Address) Then
            Application.EnableEvents = False
            Target = Worksheets(Target.Parent.Name & "alt").Range(Target.Address)
            Application.EnableEvents = True
            MsgBox "Die Summe des Bereiches ""A1:A10"" übersteigt den Maximalwert " _
               & "von 500. Eingabe verworfen!", vbExclamation
         Else
            Worksheets(Target.Parent.Name & "alt").Range(Target.Address) = Target
         End If
      ElseIf Intersect(Target, Range("C1:C10")) Then
         If Application.WorksheetFunction.Sum(Range("C1:C10")) _
            - Target + Worksheets(Target.Parent.Name & "alt").Range(Target.Address) > 100 _
            And Target > Worksheets(Target.Parent.Name & "alt").Range(Target.Address) Then
            Application.EnableEvents = False
            Target = Worksheets(Target.Parent.Name & "alt").Range(Target.Address)
            Application.EnableEvents = True
            MsgBox "Die Summe des Bereiches ""C1:C10"" übersteigt den Maximalwert " _
               & "von 100. Eingabe verworfen!", vbExclamation
         Else
            Worksheets(Target.Parent.Name & "alt").Range(Target.Address) = Target
         End If
      End If
   End If
ElseIf Application.WorksheetFunction.Sum(Range(Target.Address)) = 0 Then
   Worksheets(Target.Parent.Name & "alt").Range(Target.Address).ClearContents
End If
End Sub

Gruß Ingolf
Anzeige
AW: Vielleicht kann **IngGi** da helfen? ;-)
29.11.2006 23:55:36
IngGi
Hallo Kai,
bei der Fehlermeldung lag ich heute nachmittag auf dem falschen Dampfer. Die Fehlerquelle lag woanders, so dass auch mein Makro von heute nachmittag noch dieselbe Fehlermeldung bringt, sobald du auf den 2. definierten Bereich zugreifst. Ich habe das jetzt korrigiert und das Makro auch so umgeschrieben, das nun kein zweites Tabellenblatt mehr erforderlich ist. Die eingegebenen Daten werden jetzt in ein Datenfeld kopiert, dass beim Öffnen der Arbeitsmappe mit allen verwendeten Zellen des ersten Tabellenblattes (sog. UsedRange) befüllt wird.
Im Zusammenhang mit diesem Datenfeld habe ich beim Löschen eines Zellbereiches zunächst ständig den Laufzeitfehler "Index ausserhalb des gültigen Bereiches" bekommen und musste feststellen, dass die 2. Dimension des Datenfeldes (Spaltendimension) plötzlich von 3 auf 1 reduziert war. Ich kann mir diesen Fehler nicht erklären. Vielleicht stolpert hier ja noch jemand anderes herein, der sich das mal ansieht und mir das erklären kann. Irgendwann hat es dann jedenfalls plötzlich ohne Fehlermeldung geklappt, ohne das ich etwas verändert hätte. Ich hab einfach nur die Datei geschlossen und nochmal neu geladen.
Jetzt hoffe ich einfach mal, dass die Geschichte bei dir reibungslos klappt. Da sich das gesamte VBA-Projekt jetzt auf 3 Module verteilt (muss so sein), habe ich die Beispielmappe nochmal hochgeladen.
https://www.herber.de/bbs/user/38636.xls
Gruss Ingolf
Anzeige
AW: Vielleicht kann **IngGi** da helfen? ;-)
30.11.2006 08:31:42
Kai
Hallo Ingolf,
erst einmal riesen Dank für deine Mühe. Habe deine Datei getestet. Leider erscheint jetzt folgende Meldung "Laufzeitfehler 9: Index außerhalb des gültigen Bereiches" und es wird folgende Zeile als Fehler angezeigt


If Application.WorksheetFunction.Sum(Range("C1:C10")) _
         - Target + arr(Target.Row, Target.Column) > 100 _
         And Target > arr(Target.Row, Target.Column) Then }


Gruß
Kai
Anzeige
AW: Vielleicht kann **IngGi** da helfen? ;-)
30.11.2006 10:42:39
IngGi
Hallo Kai,
ich habe noch ein bißchen herumprobiert und festgestellt, dass die letzte Dimension des Datenfeldes (das ist hier die Spaltendimension) von Excel automatisch auf das erforderliche Maß reduziert wird, ohne dass sich das verhindern läßt. Zumindest weiß ich nicht wie. Das heißt, immer dann, wenn in dem Bereich in Spalte C nichts steht, wird die Größe des Datenfeldes auf eine Spalte reduziert. Sobald man dann etwas in Spalte C hineinschreibt, gibt es die Fehlermeldung.
Der Fehler kann allerdings sehr leicht umgangen werden. Dafür brauchst du in deinem Tabellenblatt nur in der ersten Spalte nach der letzten verwendeten Spalte - in meiner Beispielmappe also Spalte D - in irgendeiner Zelle einen beliebigen Wert einzutragen. Dadurch wird die Reduktion des Datenfeldes hinsichtlich der Spaltenzahl verhindert.
Gruß Ingolf
Anzeige
AW: Vielleicht kann **IngGi** da helfen? ;-)
04.12.2006 10:13:09
Kai
Hi,
anscheinend bin ich nicht in der Lage deine Tabelle korrekt zu bedienen.
Ich habe jetzt wie du gesagt hast, einmal in Spalte D einen Wert eingetragen. Nachdem ich die Tabelle erneut geöffnet hatte habe ich jetzt drei Möglichkeiten gefunden die bei mir eintreffen:
1. Es funktioniert.
2. Es passiert nichts.
3. Ich erhalte wieder die Laufzeitfehlermeldung "Index außerhalb des gültigen Bereiches." wobei diese sich entweder auf den Abschnitt


If Application.WorksheetFunction.Sum(Range("A1:A10")) _
 - Target + arr(Target.Row, Target.Column) > 500 _
 And Target > arr(Target.Row, Target.Column) Then }


oder


If Application.WorksheetFunction.Sum(Range("C1:C10")) _
 - Target + arr(Target.Row, Target.Column) > 100 _
 And Target > arr(Target.Row, Target.Column) Then } }


Noch eine Idee woran dies liegen könnte?
Gruß
Kai
Anzeige

316 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige