Worksheet_Change Verwirrung
Thomas Anft
ich habe mir folgendes Makro zusammengebastelt nach Studium der NG:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set VerdLoesg = Range("A18:A37") ' Bereich benennen in dem Excel _
auf Aenderungen reagiert
If Not Intersect(Target, VerdLoesg) Is Nothing Then ' Schleifenstart
Eingzelle = Selection.Value ' Aktuellen Zellinhalt auslesen
EingZelleAddr = Selection.Address ' Aktuelle Zelladresse auslesen
Sheets("Listen").Range("D2").FormulaLocal = Format(Now(), _
"ddmmyyyy") 'Schreibt nach D2 das aktuelle Tagesdatum
Sheets("Blatt").Range(EingZelleAddr).Activate ' Wählt Startadresse _
im Blatt aus
: EingZelSuc ' Unterroutine aufrufen
: ZellInhaltzuText ' Unterroutine aufrufen
End If ' Schleifenende
End Sub
Es soll mir bei Eingaben in die Zellen A18 bis A37 Einträge in andere
Zellen vornehmen mit dem Makroaufruf EingZelSuc. Im Anschluß daran liest
der Makroaufruf ZellInhaltzuText den Zellinhalt aus und schreibt den
Wert zurück mit . value.
Alle drei Makros funktionieren für sich aufgerufen einwandfrei. Nur wenn
ich sie wie oben verknüpft starte bekomme ich einen Stapelüberlauf.
Wenn ich die zwei Aufrufe aus der Schleife rausnehme habe ich zwar den
Überlauf nicht aber das Makro läuft einige Dutzend mal durch.
Wo habe ich den Fehler eingebaut? Kann mir jemand helfen? Oder gibt es
eine andere Möglichkeit bei Zelleingaben ein Makro zu starten?
Cu
Thomas
Hajo
in einem Change ereignis kannst Du kein anderes Registe wählen. Leider hast
Du die beiden anderen Makros nicht zur Verfügung gestell darum kan es auich
nur eine Hlabe Lösung sein.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Eingzelle As String
Dim EingZelleAddr As String
If Not Intersect(Target, Range("A18:A37")) Is Nothing Then '
Schleifenstart
Application.EnableEvents = False
Eingzelle = Target.Value ' Aktuellen Zellinhalt auslesen
EingZelleAddr = Target.Address ' Aktuelle Zelladresse auslesen
Sheets("Listen").Range("D2").FormulaLocal = Format(Now(),
"ddmmyyyy") 'Schreibt nach D2 das aktuelle Tagesdatum
With Sheets("Blatt").Range(EingZelleAddr)
: EingZelSuc ' Unterroutine aufrufen
: ZellInhaltzuText ' Unterroutine aufrufen
End With
Application.EnableEvents = True
End If ' Schleifenende
End Sub
Sub te()
' faslls es zum Programabbruch kommt dieses Makro starten
Application.EnableEvents = True
End Sub
Gruß Hajo
"Thomas Anft" <Tha...@AOL.COM> schrieb im Newsbeitrag
news:3D170A33...@AOL.COM...
Thomas Anft
>
> Hallo Thomas
>
> in einem Change ereignis kannst Du kein anderes Registe wählen. Leider hast
> Du die beiden anderen Makros nicht zur Verfügung gestell darum kan es auich
> nur eine Hlabe Lösung sein.
danke für die schnelle Antwort. Hier erstmal die zwei Subroutinen:
1.)
Sub ZellInhaltzuText() ' Der Wert der Zelle wird hineingeschrieben
Dim OrgAdresse, FormelZuWert As Variant ' Variablen definieren
OrgAdresse = Selection.Address ' Adresse sichern
FormelZuWert = Selection.Value ' Wert auslesen in Variable
Range(OrgAdresse).FormulaLocal = FormelZuWert 'Hier steht die Formel
_
die ersetzt wird
End Sub
2.)
Sub EingZelSuc() ' Die Formel soll eingegeben werden
Dim gZelle As Variant ' Variablen definieren
Dim sBegriff, KonzVerd, KonzVerd1, KonzVerd2 As Variant ' Variablen
sBegriff = Eingzelle ' Den Zellinhalt wird uebergeben
If sBegriff = "" Then Exit Sub ' Schleifenstart. Wenn die _
Zelle leer ist endet das Makro hier
Set gZelle = Range("F18:F37").Find(sBegriff, _
Lookat:=xlWhole) ' In Bereich suchen nach Bezeichner
' Läuft nur wenn in Suchbereich keine Formel steht
If gZelle <> " " Then Exit Sub ' Schleifenstart. hier
Range(gZelle.Address).Offset(0, -2).Select
KonzVerd = Selection.Address
Range(EingZelleAddr).Offset(0, 1).Select
KonzVerd1 = Selection.Address
Range(EingZelleAddr).Offset(0, 2).Select
KonzVerd2 = Selection.Address
Range(EingZelleAddr).Offset(0, 3).Select
ActiveCell.FormulaLocal = "=RUNDEN(" & KonzVerd & "/" _
& KonzVerd2 & "*" & KonzVerd1 & ";(SigStellen-1)-GANZZAHL(LOG _
(ABS(" & KonzVerd & "/" & KonzVerd2 & "*" & KonzVerd1 & "))))"
Range(EingZelleAddr).Select
End Sub
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
...
> End With
> Application.EnableEvents = True
>
> Sub te()
> ' faslls es zum Programabbruch kommt dieses Makro starten
> Application.EnableEvents = True
> End Sub
>
> Gruß Hajo
>
Soweit ich verstanden habe darf ich im Change Ereignis nicht auf andere
Register verweisen. Dann hast Du ein End With und ein
Application.EnableEvents eingefügt. Werde es mal versuchen mit diesen
Aenderungen. Und die Routinen mit dem Aufruf auf das andere Blatt in
dasselbe via Worksheets_Open verschieben.
Habe ich es richtig verstanden?
Cu
Thomas
Hajo
du darf in einem Change Ereignis nicht mit Select oder Activate ein anderes
Register wählen. Der rictige Durchblick ist mir mit Deinen beiden Makros
auch noch nicht gekommen.
Das scheint mir leider zu kompliziert. z.B. erscheint in Sub EingZelSuc()
Eingzelle die hier nicht definiert ist und vom Change Ereignis wird es nicht
übergeben da es hier nur mit Dim Definiert ist.
Grundsätzlich sollte in VBA auf select verzichtet werden.
Vielleicht kommst Du besser, wenn Du beschreibst was passieren soll bei der
Eingabe im Bereich A18:A37 (im welchem Register ist dieser Bereich)
Gruß Hajo
Philipp von Wartburg
Ich möchte mich kurz einklinken und auf zwei weitere Punkte
hinweisen.
Ich empfehle die Prüfung nach der Anzahl Zellen in Target.
Ändern sich nämlich mehrere Zellen und nicht nur eine Zelle,
dann kann 'Value' nicht mehr ermittelt werden, d.h. die Code-
Zeile
Eingzelle = Target.Value
führt zum Laufzeitfehler 13 (Typen unverträglich).
Abfragen lässt sich die Anzahl Zellen in Target z.B. mittels
If Target.Cells.Count > 1 Then
Die Frage ist halt, ob die Unterroutinen in diesem Fall trotzdem
ausgeführt werden sollen/können/dürfen.
Das Problem "Target enthält mehrere Zellen" wirkt sich auch
auf diese Code-Zeile aus:
If Not Intersect(Target, Range("A18:A37")) Is Nothing Then
Ändern sich z.B. die Zellen A11:A25, dann ist die If-Bedingung
erfüllt, da die Schnittmenge ermittelt werden konnte (A18:A25).
Diese Code-Zeile gefällt mir ehrlich gesagt auch nicht:
Sheets("Listen").Range("D2").FormulaLocal = Format(Now(),
"ddmmyyyy")
Bei jeder Zelländerung im Bereich A18:A37 wird jedes Mal das
aktuelle Datum in Listen!D2 eingetragen. Vielleicht geht auch
"=HEUTE" als Zellformel. Allerdings wird so das Datum auch
dann aktualisiert, wenn keine Änderung in A18:37 stattfand,
was vermutlich eben nicht passieren soll.
HTH
Gruss
Philipp
Hajo schrieb in Nachricht ...
Thomas Anft
>
> Hallo Thomas
>
Hallo Hajo
> du darf in einem Change Ereignis nicht mit Select oder Activate ein anderes
> Register wählen. Der rictige Durchblick ist mir mit Deinen beiden Makros
Das habe ich verstanden und werde es ändern.
Gibt es eigentlich eine andere Möglichkeit bei Veränderung von Zellen
ein Makro zu starten?
> auch noch nicht gekommen.
> Das scheint mir leider zu kompliziert. z.B. erscheint in Sub EingZelSuc()
> Eingzelle die hier nicht definiert ist und vom Change Ereignis wird es nicht
> übergeben da es hier nur mit Dim Definiert ist.
Die Variablen hatte ich vor dem Change Ereignis als Variant definiert.
Option Explicit
Dim Eingzelle, EingZelleAddr, PeterBez As Variant
Dim VerdLoesg, ProbBezVal As Range
Dim Target As Excel.Range
>
> Grundsätzlich sollte in VBA auf select verzichtet werden.
In den aufgerufenen Untermakros würde es dann funktionieren? Welcher
Befehl liest mir denn die Zelladdressen in eine Variable ein?
>
> Vielleicht kommst Du besser, wenn Du beschreibst was passieren soll bei der
> Eingabe im Bereich A18:A37 (im welchem Register ist dieser Bereich)
Im Register Blatt, da dieses Blatt aktiv ist beim Start hatte ich es
nicht mit sheets("Blatt") extra ausgewählt.
Allgemein wollte ich folgendes. Ich habe eine Reihe von Werten, diese
Werte werden fortgeführt über eine Nummerierung in Spalte A18:A37. Wenn
ich also eine neue Bezeichnung (Die Bezeichnung steht schon fest) in
Bsp.: A22 eingebe sollte mein Makro in der Liste F18:F37 nachsehen wo
diese Bezeichnung steht und in Abhängigkeit davon in der Spalte zwei
links daneben einen Wert auslesen mit dem ich Rechnen will.
>
> Gruß Hajo
Danke Thomas
Thomas Anft
>
> Hallo zusammen
>
> Ich möchte mich kurz einklinken und auf zwei weitere Punkte
> hinweisen.
>
> Ich empfehle die Prüfung nach der Anzahl Zellen in Target.
> Ändern sich nämlich mehrere Zellen und nicht nur eine Zelle,
> dann kann 'Value' nicht mehr ermittelt werden, d.h. die Code-
> Zeile
> Eingzelle = Target.Value
> führt zum Laufzeitfehler 13 (Typen unverträglich).
> Abfragen lässt sich die Anzahl Zellen in Target z.B. mittels
> If Target.Cells.Count > 1 Then
> Die Frage ist halt, ob die Unterroutinen in diesem Fall trotzdem
> ausgeführt werden sollen/können/dürfen.
> Das Problem "Target enthält mehrere Zellen" wirkt sich auch
> auf diese Code-Zeile aus:
> If Not Intersect(Target, Range("A18:A37")) Is Nothing Then
> Ändern sich z.B. die Zellen A11:A25, dann ist die If-Bedingung
> erfüllt, da die Schnittmenge ermittelt werden konnte (A18:A25).
Gibt es hier die Möglichkeit ein Makro zu starten wenn sich eine Zelle
ändert ohne ein Worksheet_Change Ereignis?
>
> Diese Code-Zeile gefällt mir ehrlich gesagt auch nicht:
> Sheets("Listen").Range("D2").FormulaLocal = Format(Now(),
> "ddmmyyyy")
> Bei jeder Zelländerung im Bereich A18:A37 wird jedes Mal das
> aktuelle Datum in Listen!D2 eingetragen. Vielleicht geht auch
> "=HEUTE" als Zellformel. Allerdings wird so das Datum auch
Ja, daß will ich in dem Listen Register mit einem Auto_open machen und
es so aus dem Register Blatt rausnehmen. Ist Auto_open dafür die
richtige Wahl? Das aktuelle Tagesdatum soll nur einmal beim Öffnen des
Blattes eingetragen werden als Wert und nicht als Funktion da es als
Teil des Bezeichners verwendet werden soll. Mit
VERKETTEN(Listen!A3;"_";Listen!D2) mache ich es im Moment.
> HTH
> Gruss
> Philipp
Danke
Thomas
Hajo
> Gibt es eigentlich eine andere Möglichkeit bei Veränderung von Zellen
> ein Makro zu starten?
> Die Variablen hatte ich vor dem Change Ereignis als Variant definiert.
> Option Explicit
> Dim Eingzelle, EingZelleAddr, PeterBez As Variant
> Dim VerdLoesg, ProbBezVal As Range
> Dim Target As Excel.Range
Die Variablen sind mit Dim Definiert und gelten darum nur in den Makros der
Tabelle (Blatt) nicht im Modul Dazu müßten Sie mit Public definiert werden.
Solche Definitionen würde ich immer in ein Modul machen.
> In den aufgerufenen Untermakros würde es dann funktionieren? Welcher
> Befehl liest mir denn die Zelladdressen in eine Variable ein?
in der Tabelle
Zelle=Target.Address , wenn nur eine Zelle markiert ist siehe Beitrag von
Philipp vobn Wartburg
> > Vielleicht kommst Du besser, wenn Du beschreibst was passieren soll bei
der
> > Eingabe im Bereich A18:A37 (im welchem Register ist dieser Bereich)
> Allgemein wollte ich folgendes. Ich habe eine Reihe von Werten, diese
> Werte werden fortgeführt über eine Nummerierung in Spalte A18:A37. Wenn
> ich also eine neue Bezeichnung (Die Bezeichnung steht schon fest) in
> Bsp.: A22 eingebe sollte mein Makro in der Liste F18:F37 nachsehen wo
> diese Bezeichnung steht und in Abhängigkeit davon in der Spalte zwei
> links daneben einen Wert auslesen mit dem ich Rechnen will.
Das liest sich doch aber nach einer Sverweisformel nach links die im
Register eingetragen werden kann. Da bin ich aber nicht der Fachmann. Schaue
mal hier
http://www.excelformeln.de/uberuns.html
Mit nach rechts wäre es eine einfacher Sverweis.
Gruß Hajo
Philipp von Wartburg
Es gibt eine Möglichkeit nebst Worksheet_Change. Über die
OnEntry-Eigenschaft kann definiert werden, welches Makro
bei einer Zell-Eingabe gestartet werden soll. "OnEntry" ist
jedoch nicht mehr dokumentiert und nur noch aus Gründen
der Kompatibilität zu früheren XL-Versionen enthalten; sollte
daher nicht mehr unbedingt verwendet werden.
Eingesetzt wird es so:
Diese Zeile steht z.B. in Auto_Open oder Workbook_Open:
Worksheets("Tabelle1").OnEntry = "MeinMakro"
In einem Modul steht das Makro "MeinMakro":
Public Sub MeinMakro
MsgBox "Zelle geändert: " + Application.Caller.Address
End Sub
Gruss
Philipp
Thomas Anft schrieb in Nachricht <3D180230...@AOL.COM>...