Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: VBA Change Event --> Fehler Stapelspeicher und Objektfehler

VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 20:31:25
MarC
Hallo zusammen,

ich bin gerade total am verzweifeln und weiß einfach nicht weiter.

In die Zelle B7 soll eine Seriennummer eingegeben und überprüft werden ob diese in der Datenbank vorhanden ist. Wenn diese nicht vorliegt soll die Zelle rot werden wenn sie gefunden wird soll diese grün und Werte in andere Zellen übertragen werden. Leider Gottes erhalte ich immer zwei Fehlermeldungen:

1. Laufzeitfehler 28 - Nicht genügend Stapelspeicher
Wenn ich diesen Fehler quittiere erhalte ich beim nächsten mal diese Fehlermeldung:

2. Die Methode Cells für das Objekt Worksheet ist fehlgeschlagen

Kann mir bitte jemand sagen was ich falsch gemacht habe? Ist das Change Event die falsche Vorgehensweise für meine Anwendung? Ich habe Change Event gewählt, weil die Seriennummer eingescannt wird und automatisch ein Enter ausgeführt wird. Ich habe mir gedachte so müsste niemand einen Button betätigen oder Enter drücken.

Grüße
MarC

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyX As Range
Dim RowNo1 As Long
Dim LastRow As Long

Set KeyX = Range("B7")

LastRow = Sheets("Tabelle2").Cells(Rows.Count, 2).End(xlUp).Row
For RowNo1 = 6 To LastRow
If Mid(Sheets("Tabelle1").Cells(7, 2), 3, 6) = Sheets("Tabelle2").Cells(RowNo1, 2) Then
Sheets("Tabelle1").Cells(7, 2).Interior.Color = RGB(0, 176, 80)
Sheets("Tabelle1").Cells(9, 2) = Mid(Sheets("Tabelle1").Cells(7, 2), 19, 5)
Sheets("Tabelle1").Cells(11, 6) = Sheets("Tabelle2").Cells(RowNo1, 5)
Else
Sheets("Tabelle1").Cells(7, 2).Interior.Color = RGB(255, 0, 0)
Sheets("Tabelle1").Cells(7, 3).Interior.Color = RGB(255, 0, 0)
End If
Next RowNo1
End Sub
Anzeige

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

Betreff
Datum
Anwender
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 20:54:02
AlterDresdner
Hallo Marc,
ohne Deinen Code genau gelesen zu haben:
Er bezieht sich vermutlich auf das Change-Event von Tabelle1. Damit ruft sich der Code permanent selbst auf, bis halt der Stapelspeicher voll ist.
Setze am ANfang Deines Codes die ANweisung Application.EnableEvents = False
und am Ende Application.EnableEvents = True. Dann sollte es besser laufen.
Den anderen Fehler erkennt man wohl besser aus einer geposteten Testdatei...
Gruß der AlteDresdner
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 21:04:23
ralf_b
hier ein anderer Ansatz, UNGETESTET!!
zusätzlich was der Kollege schon geschrieben hat, benötigst du keine Schleife, die du beim Fund nicht mal verläßt.
Mir scheint außerdem das deine Zellreferenzen etwas durcheinander sind.
Man sollte schon immer die gleichen Zellen färben und setzen im if und else Zweig.
deine zu prüfende Zelle ist B7 und diese ist im Änderungsfall dann hinter der Variable Target versteckt.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyX As Range
Dim LastRow As Long
Dim val


If Target.Address(0, 0) = "B7" Then

If Len(Target.Value) 6 Then Exit Sub
val = Mid(Target, 3, 6)
With Sheets("Tabelle2")
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
Set KeyX = .Cells(6, 2).Resize(LastRow - 5, 1).Find(What:=val, LookIn:=xlValues, LookAt:=xlPart)
End With

Application.EnableEvents = False
If Not KeyX Is Nothing Then
Target.Interior.Color = RGB(0, 176, 80)
Cells(9, 2) = Mid(Target, 19, 5)
Cells(11, 6) = KeyX.Offset(, 3).Value
Else
Target.Resize(1, 2).Interior.Color = RGB(255, 0, 0)
End If
Application.EnableEvents = True

End If
End Sub
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 21:33:29
MarC
Danke euch beiden aber so ganz komme ich noch nicht zu recht bzw. ich verstehe deinen Code nur zum Teil :-/

Ich habe die Schleife gewählt, weil in der Seriennummer ab der dritten Stelle bis zur 8 Stelle eine Materialnummer drin steckt und nach dieser soll im Tabellenblatt2 gesucht werden. Wenn diese gefunden wurde soll die Zelle grün werden und weitere Daten aus dem Tabellenblatt 2 in das Tabellenblatt 1 kopiert werden. Diese verweise habe ich jetzt nicht in meinen Post geschrieben, weil ich dachte dass das nicht relevant ist.
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 22:17:49
MarC
Hallo Ralf,

kannst du mir bitte sagen was der Befehl macht? Hier wird doch nur die Länge des Inhalts der definierten Zelle "B7" überprüft oder? Wenn der Inhalt kleiner als 6 ist so das Makro beendet werden? Stimmt das oder liege ich da falsch?

If Len(Target.Value)  6 Then Exit Sub


Gruß
MarC
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
22.05.2024 15:00:02
MarC
Hallo Ralf,

ich habe deine unten aufgeführte Zeile angepasst, weil ich glaube das xlPart nicht das richtige für meine Anwendung ist. Der Suchtext muss gesamt übereinstimmen also xLWhole. Jetzt scheint es zu funktionieren :-) Vielen Dank nochmal für deine, Onurs, AlterDresdners Hilfe.

Set KeyX = .Cells(6, 2).Resize(LastRow - 5, 1).Find(What:=val, LookIn:=xlValues, LookAt:=xlPart)


Geänderte Zeile
Set KeyX = .Cells(6, 2).Resize(LastRow - 5, 1).Find(What:=val, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
Anzeige
Gerne!
22.05.2024 16:18:25
ralf_b
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 23:27:38
ralf_b
weil du hier val = Mid(Target, 3, 6) das 6 te Zeichen angibst dachte ich mir: "wenn weniger drin sind, macht die weitere Verarbeitung keinen Sinn."
Diesmal mit Datei
22.05.2024 10:38:30
MarC
Hallo Ralf,

ich habe heute deinen Code nochmal versucht sauber umzusetzen und die Datei gleich mal hier hochgeladen. Der Code läuft ohne eine Fehlermeldung durch aber das Ergebnis stimmt nicht und ich kann noch nicht nachvollziehen warum.

Die Idee war ja das ich in Zelle "B7" eine Seriennummer eingebe oder halt einscanne. Diese ist 40 Zeichen lang. In dieser Seriennummer steckt eine Sachnummer die ab dem 3 Zeichen beginnt und 6 Zeichen lang ist. Nach dieser Sachnummer soll im Tabellenblatt Data Base gesucht in der Spalte B ab Zeile 6 gesucht werden. Wenn diese nicht gefunden wird soll die Zelle "B7" rot gefärbt werden. Wenn die Zelle "B7" leer ist soll diese hellgelb werden.

Wenn ich jetzt bei "B7" z.B. ewe eingebe wird die Zelle grün und die anderen Zellen die bei einem positiven Ergebnis gefüllt werden sollen werden werden beschrieben. Aber das sollte nicht sein. Woran könnte das denn liegen? Mid..........3, 6 habe ich ja richtig gewählt.

Grüße
MarC

https://www.herber.de/bbs/user/169636.xlsm
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 21:32:31
Onur
So ähnlich funktioniert auch die Atombombe. Wenn ein Neutron ein Uranatom trifft, wird es gespalten und es werden zwei neue Neutronen freigesetzt, die dann wiederum je ein Uranatom treffen, und es werden wiederum je 2 Neutronen ............
Wenn du das Ganze nicht kontrollierst wie beim Kernkraftwerk, gibt es eine Explosion.
Deine durch das Change-Event durchgeführten Änderungen lösen wiederum das Change-Event aus und es werden wieder Zellen verändert, die dann .....
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 21:35:50
MarC
Das war mir nicht klar Onur. Ich habe den Code in einer kleinen Datei getestet und da ist kein Fehler aufgetreten. Danach habe ich diesen in die original Datei kopiert wo mehrere Werte von Tabellenblatt2 kopierten werden sollen und dann kamen die Fehler. Anscheinend hatte ich vorher nur Glück das kein Fehler aufgetreten ist.
Anzeige
AW: VBA Change Event --> Fehler Stapelspeicher und Objektfehler
21.05.2024 21:43:17
Onur
Wie bereits von den Kollegen gesagt: Wenn du auf dem Blatt, dessen Change-Event du überwachst, was ändern willst, musst du vorher Application.EnableEvents auf False setzen und danach wieder zurück auf True.
Und wichtig: Du solltest immer das Makro auf den Bereich begrenzen, den du überwachen willst, sonst springt es bei JEDER Änderung auf diesem Blatt an. Dann erübrigt sich sogar meistens das mit "Events ausschalten". Wenn du z.B. in C1 ein "ok" schreiben willst, wenn in A1 eine 1 geschrieben wird.
Z.B. so:
If Taget.Address >"$A$1" Then Exit Sub
Anzeige

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige