Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 08:26:15
Rückgabewert mehrere Suchkriterien Datumsbereich
23.04.2024 08:20:37
VBA: Blatt als pdf per Mail. Blatt mit anderen pdfs vereinen
23.04.2024 08:04:28
4-fach verschachtelte WENN-Funktion
22.04.2024 22:36:13
Marko automatisch ausführen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
456to460
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
456to460
456to460
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Problem mit SpecialCells-Methode ?!

Problem mit SpecialCells-Methode ?!
19.07.2004 13:31:07
Ben
Hallo Leute,
ich habe folgendes Problem:
In einem Arbeitsblatt stehen mehrere Funktionen. Die möchte ich vor Veränderung schützen. Ich mache dies mit folgendem Makro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim formeln as Range	' Variable für den zu schützenden Bereich
Set formeln = Cells.SpecialCells(xlCellTypeFormulas)
' Deaktiviert alle Ereignisse für das Application-Objekt:
Application.EnableEvents = False
If Not Application.Intersect(Target, formeln) Is Nothing Then
Application.Undo
End If
Application.EnableEvents = True
End Sub

Doch dieses Makro verhindert nicht, daß vorhandene Funktionen geändert werden - es verhindert stattdessen, daß neue Funktionen in leere Zellen eingegeben werden können.
Das verstehe ich nicht!
Wenn ich nun die If-Bedingung im obigen VBA-Code ändere zu:
If Application.Intersect(Target, formeln) Is Nothing Then
(also das "Not" weglasse), dann können zwar die vorhandenen Funktionen nicht geändert werden - aber es können nun überhaupt keine Zellen mehr geändert werden (leere Zellen können auch nicht beschrieben werden).
Das verstehe ich erst recht nicht!
Für Hinweise, wie ich mit der SpecialCells-Methode bestimmte Zellen (z. B. solche mit Funktionen) vor Veränderung schützen kann, wäre ich Euch sehr dankbar!
Herzliche Grüße
Ben

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Problem mit SpecialCells-Methode ?!
19.07.2004 14:07:42
peh
HI,
die Logik an sich erscheint falsch.
Wenn du in einer Zelle einen Wert eingibst, löschst du damit die Formel.
Damit kann die Zelle nicht mehr zu den Formelzellen gehören und fällt damit
auch nicht ins intersect.
Und andersrum ist es genauso ;-), da dann die Zelle zum intersect gehört und dort verbietest du es ja ;-)
Du arbeitst bei diesen Sachen mit dem aktuellen Wert und nicht dem Vorgängerwert der Zelle.
Gruß Peter
AW: Problem mit SpecialCells-Methode ?!
Luschi
Hallo Ben,
der Befehl "Set formeln = Cells.SpecialCells(xlCellTypeFormulas)"
kommt zu spät. Wenn Du mit "MsgBox formeln.Address" Dir mal den Zellbereich
anschaust, der Formeln enthält, dann wirst Du feststellen, daß die Zelle,
in der gerade die Formel gelöscht/bzw. gegen Text ausgetaucht wurde, nicht
mehr im "formeln"-Bereich drin ist.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: Problem mit SpecialCells-Methode ?!
19.07.2004 14:35:54
Uwe
Hallo Ben,
hier mein Vorschlag, wie es gehen sollte:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WertAktuell
On Error GoTo Ende
Application.EnableEvents = False
WertAktuell = Target
Application.Undo
If Not Target.HasFormula Then Target = WertAktuell
Ende:
Application.EnableEvents = True
End Sub

Gruß Uwe
AW: Problem mit SpecialCells-Methode ?!
19.07.2004 14:56:00
Ben
Hallo Leute,
herzlichen Dank für Eure prompten Antworten! Habe wieder mal was gelernt.
Uwe's Lösung ist sehr clever - hat aber einen kleinen Schönheitheitsfehler:
Wenn ich nun in eine leere Zelle eine Funktion eingebe (z. B. "=5*4"), dann erscheint in dieser Zelle nicht diese Funktion, sondern der Wert dieser Funktion, also 20 (und nicht "=5*4"). Ansonster aber sehr tricky! Danke!
Für Euch weiterhin alles Gute, herzliche Grüße
Ben.
Anzeige
AW: Problem mit SpecialCells-Methode ?!
19.07.2004 15:09:49
Uwe
Hallo Ben,
so sollte es auch mit neuen Formeln gehen:
WertAktuell = Target.Formula
Gruß Uwe
Nur mal so ;-)
19.07.2004 21:06:49
peh
Hi ihr beiden,
ohne es getestet zu haben, würde mich dann doch mal interessieren,
was so alles passiert wenn mehrere Zellen gleichzeitig geändert werden,
z.B. durch Paste.
Was wo welche formulas, welche Undos, welche ... werden nun
gemurkst?
Vor allem dürfte interessant sein, wie sich
Wert = target.formula dort verhält ;-)
Formelzellen sperren,Blattschutz, fertig.
Gruß Peter
AW: Nur mal so ;-)
19.07.2004 23:59:45
Uwe
Hallo Peter,
ich wäre richtig enttäuscht gewesen, wenn von Dir kein Einwand gekommen wäre. ;-)
Schon beim Abschicken des Codes sah ich Dein Kopfschütteln. :-)
Hier die komplette Variante, welche (hoffentlich) alle Eventualitäten abdeckt.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WertAktuell()
Dim rngArea As Range
Dim rngAZ As Range
Dim rngZelle As Range
Dim lngZ As Long
Set rngAZ = ActiveCell
On Error GoTo Ende
Application.EnableEvents = False
ReDim WertAktuell(1 To Target.Cells.Count)
For Each rngArea In Target.Areas
For Each rngZelle In rngArea.Cells
lngZ = lngZ + 1
WertAktuell(lngZ) = rngZelle.Formula
Next rngZelle
Next rngArea
lngZ = 0
Application.Undo
For Each rngArea In Target.Areas
For Each rngZelle In rngArea.Cells
lngZ = lngZ + 1
If Not rngZelle.HasFormula Then rngZelle = WertAktuell(lngZ)
Next rngZelle
Next rngArea
rngAZ.Activate
Ende:
Application.EnableEvents = True
End Sub

Gruß Uwe
Anzeige
Was ich gar nicht verstehe ... !
20.07.2004 11:22:12
Ben
Hallo Uwe,
bei Deiner letzten Version wie bei Deiner vorläufigen Version verstehe ich eins nicht:
Du fragst in der Prozedur ja eigentlich nur ab, ob der neu eingetragene Wert eine Funktion ist. Man würde also erwarten: Wenn ich eine vorhandene Funktion mit einer neuen Funktion überschreibe, dann läßt Deine Prozedur das zu (was ja eigentlich nicht sein soll). Aber Deine Prozedur verhindert auch diesen Fall (was gut ist) - nur, ich verstehe nicht, wieso: Wie erkennt Deine Prozedur, daß eine Funktion geändert wurde, obwohl doch nur abgefragt wird, ob der neue Werte eine Funktion ist:
If Not rngZelle.HasFormula Then rngZelle = WertAktuell(lngZ)
Anzeige
Versuch einer Beschreibung
20.07.2004 13:17:45
Uwe
Hallo Ben!
Ich versuche mal, das Ganze Zu erklären.
Das erste Problem ist(siehe Hinweis von Peter),
daß der Benutzer ja auch mehrere Zellen markieren kann,
um sie dann z.B. in einem Rutsch zu leeren(ENTF-Taste).
Damit aber nicht genug: Es ist ja auch möglich, mit der
STRG-Taste mehre nichtangrenzende Zellen(-Bereiche) zu
markieren. Um das alles abzufangen, dienen die Schleifen.
ReDim WertAktuell(1 To Target.Cells.Count)
Hier werden erst einmal alle markierten Zellen gezählt
und für jede Zelle eine eigene Variable vorbereitet.
For Each rngArea In Target.Areas
Alle möglichen Teilbereiche werden durchlaufen.
For Each rngZelle In rngArea.Cells

Alle Zellen im gerade abgefragten Teilbereich werden durchlaufen.
lngZ = lngZ + 1
WertAktuell(lngZ) = rngZelle.Formula

Der tatsächliche Inhalt der gerade abgefragten Zelle
wird in der entsprechenden Variable abgespeichert.
Zu beachten ist hierbei, daß die momentanen Inhalte,
also nach der Änderung der Zellinhalte, gespeichert werden.
Application.Undo
Die Excelfunktion 'Rückgängig' wird ausgeführt.
Jetzt haben die Zellen wieder den Wert vor der letzten
Änderung. Nun werden noch einmal alle Bereiche und Zellen
durchlaufen.
If Not rngZelle.HasFormula Then rngZelle = WertAktuell(lngZ)
Die wichtigste Zeile. Wenn die Zelle keine Formel enthält,
wird ihr der vorher in der Variable gespeicherte Inhalt
wieder übergeben, also die Rückgängig-Aktion wieder
rückgängig gemacht. Enthält die Zelle eine Formel, passiert
garnichts, sie behält also ihren Inhalt, den sie vor
der Änderung hatte.
Ich hoffe, daß mit diesen Erklärungen der Code verständlicher wurde.
Viele Grüße
Uwe
Anzeige
AW: Alles klar!
20.07.2004 14:30:16
Ben
Ja, OK, prima, jetzt kapiere ich:
Es wird also nicht geprüft, oder der neue Eintrag eine Funktion ist, sondern die Änderung wird rückgängig gemacht (application.undo), dann wird der originale Wert vor der Änderung geprüft, und falls er keine Funktion war, wird der geänderte Wert sozusagen neu eingetragen.
Da kann ich mich nur wiederholen: sehr clever, sehr tricky!
Gut, herzlichen Dank - da hab' ich wieder was, womit ich vor meinen Kumpels angeben kann ;-)
Für Dich weiterhin alles Gute und herzliche Grüße
Ben.
AW: Problem mit SpecialCells-Methode ?!
20.07.2004 11:11:21
Ben
Hallo Uwe,
herzlichen Dank für Deine Verbesserung der Prozedur! Diesen Code verstehe ich zwar nicht, aber ich werd's schon noch lernen.
Einen Schutz der Funktionen mittels Blattschutz möchte ich vermeiden, weil man den Blattschutz mit einem Makro entfernen kann (ohne vorherige Kenntnis des Kennwortes) - so mach' ich das jedenfalls immer. Darum will ich die Funktionen über ein Makro schützen: ist halt ein bisserl sicherer ...
Für Dich und peh weiterhin alles Gute, herzliche Grüße
Ben.
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige