Supportnet / Forum / Tabellenkalkulation

Hallo alle! Ich habe ein schwieriges Problem zu lösen, konnte es bisher aber nicht schaffen: Ich möchte in einer gerade verlassenen Zelle nachträglich automatisch etwas ändern können. Beispiel: In Zelle B7 Habe ich "Paul" eingegeben, danach klicke ich Zelle E12 an. In diesem Moment soll Zelle B7 z.B. automatisch Fettschrift erhalten oder etwas Ähnliches. "Private Sub Worksheet_SelectionChange" in VBA als Tabellenblattereignis mit dem Befehl "Static Zelle As Range" bezieht sich ja auf die aktuell angewählte Zelle und leider nicht auf die eben erst verlassene! Das Problem besteht darin, in dem SelectionChange-Makro die letzte Zelle wieder adressieren zu können und außerdem für den nächsten Zugriff die Adresse der aktuellen Zelle zu speichern. Bin sehr gespannt, ob da jemand helfen kann. Danke jedenfalls schon jetzt für alle Bemühungen. Gruß peko

Hi peko,

ja, wie der Name schon sagt:
das SelectionChange -Ereignis tritt immer in Kraft , wenn du die Auswahl änderst, d.h. eine neue Zelle oder Bereich auswählst.
Das Change-Ereignis bezieht sich direkt auf die vor dem verlassen geänderte Zelle, weil die Änderungen erst mit dem Verlassen der Zelle wirksam werden.

Von der Sache her unter der Voraussetzung, daß die alte Zelle auch geändert wird und nicht nur angeklickt:
Alte Zelle:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Address
´etc

Neue Zelle:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Address
´etc

Keine Ahnung, was du machen willst, ist aber sicher sinnvoll, mit Public-Variablen zu arbeiten.
Achtung, man kann sehr schnell mit der Reihenfolge der Ereignisse durcheinanderkommen.


Gruß Primut

Hi Primut,

danke für deine Mühe, hat mir aber nicht wirklich geholfen.

Vielleicht weiß sonst jemand Rat?

Gruß peko

Hi Peko,

du mußt einfach mal konkret (Details!) werden, was du machen willst. Ansonsten kannst du auch keine speziellen Lösungen erwarten, deswegen nur meine allgemeine Erklärung!

.

Nur in ausgewählten verlassenen Zellen oder in allen?
Was soll geändert werden?
Ist es abhängig davon, wohin die Zelle verlassen wurde, d.h. welches die neue Zelle ist?
Soll die verlassene Zelle nur geändert werden, wenn sie selbst geändert wurde oder auch wenn sie nur ausgewählt wurde ohne den Zellinhalt zu ändern?
Gilt das nur für einzelne Zellen oder auch für Bereiche?

Anbei nochmal einen Beispiel -Code, der die gerade geänderte Zelle Fett formatiert:

Private Sub Worksheet_Change(ByVal Target As Range)
 Target.Font.Bold = True
End Sub

Gruß Primut

Hallo Primut,

du gibst dir ja wirklich Mühe, und ich danke dir dafür. Dein Mini-Makro habe ich ausprobiert, klappt natürlich.

Um mein Anliegen zu präzisieren: Ich habe eine funktionierende Routine geschrieben, die im Bereich Z2S2 bis Z41S51 alle führenden und alle abschließenden Leerzeichen entfernt. Hier ist sie:

Sub LeerzeichenEntfernen()
Dim spalte, zeile, länge As Integer, text As String
With Sheets ("Tabelle1")
For spalte = 2 To 51
For zeile = 2 To 41
text = .Cells(zeile, spalte)
länge = Len(text)
Do Until Left(text, 1) <> " "
text = Mid(text, 2, länge - 1)
länge = Len(text)
Loop
If text = "" Then GoTo leer
Do Until Mid(text, länge, 1) <> " "
text = Left(text, länge - 1)
länge = Len(text)
Loop
leer:
.Cells(zeile, spalte) = text
Next zeile
Next spalte
End With
End Sub

Ich lasse zur Zeit diese Routine ablaufen, um unbeabsichtigte Leerzeichen am Anfang oder Ende zu entfernen, bevor zum Beispiel Sortierungen in dem genannten Bereich stattfinden oder per Formeln auf einzelne dieser Zellen zugegriffen wird. Der Haken: Es stört einfach, dass die Routine einige Zeit braucht.

Von daher ist es mein Anliegen, eine Worksheet_Change-Routine zu haben, die sofort nach Eingabe in eine Zelle und Verlassen der Zelle (egal wohin) die dort eventuell vorhandenen führenden oder abschließenden Leezeichen automatisch entfernt.

Wenn du mir hier helfen könntest, wäre das einfach nur klasse!!!

Herzliche Grüße
peko

Hallo Primut,

ich habe noch weiter versucht, mein Problem zu lösen. Anscheinend ist es mir gelungen. Folgende Routine leistet das Gewünschte:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim spalte, zeile, laenge As Integer, text As String, bereich As Long
bereich = Selection.Count
If bereich > 1 Then GoTo ende
text = Target
laenge = Len(text)
Do Until Left(text, 1) <> " "
text = Mid(text, 2, laenge - 1)
laenge = Len(text)
Loop
If text = "" Then GoTo leer
Do Until Mid(text, laenge, 1) <> " "
text = Left(text, laenge - 1)
laenge = Len(text)
Loop
leer:
Target = text
ende:
End Sub

Der Vollständigkeit halber muss ich sagen, dass die Routine versagt., wenn ich vorher einen Bereich markiere und dann über ENTER-Taste mehrere Eingaben vornehme.

Lieben Gruß und danke für deine Bemühungen.
peko

Hi Primut,

klappt eigentlich super, aber Probleme bei automatischem (per Makro) Schreiben von Formeln!

Auch beim Sortieren!

Gruß peko

Hi peko,

oh, das hört sich ja tatsächlich etwas komplexer an.
Du willst also immer nur im gleichen Bereich B2:AZ41 die Leerzeichem entfernen?
Sortieren innerhalb des Bereiches?
Formel per Makro aber außerhalb des Bereiches schreiben, oder ?

Ich werd mal schauen.

Gruß Primut

Hi Peko,

ich muß schon sagen, war wirklich etwas Tricky!, d.h. ich habs mal etwas eleganter umgeschrieben, daß praktisch die Bereichsänderung auch funktioniert. Im Ernstfall mußt du deine Probleme aus Antwort 6 nochmal genau erläutern.

Erstmal der Code, dann die Erläuterungen:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim  i As Integer
Dim Zelle As Range, BA As Range
Dim BereichAuswahl As Range
Dim x As String, Ad As String, St As String
On Error Resume Next
Set BereichAuswahl = Range("B2:AZ41")

x = Target.Address
Lenx = Len(x)
For i = 1 To Lenx
 St = Mid(x, i, 1)
 If St = "$" Then
  Else
  Ad = Ad + St
 End If
Next i
Set BA = Range(Ad)
If Intersect(Target, BereichAuswahl) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Zelle In BA
Wert = Zelle.Value
Zelle.Value = Trim(Wert)
Next
Application.EnableEvents = True
End Sub

So..., Das Problem bestand tatsächlich darin, werden mehrere Zellen gleichzeitig geändert, dann bleibt bei nebeneinanderliegenden Zellen der Target.Value leer. Werden die Zellen einzeln markiert und dann geändert (Wert eingefügt), tritt dieser Effekt erstaunlicherweise nicht auf. Ein zweites Problem bestand darin, daß Target.Address als fixierte Adresse mit $ angegeben wird, diese aber nicht für das Range-Objekt verwendet werden kann. Deswegen die $ Eliminierung in der

For i = 1 To Lenx 

Schleife.
Mit

Intersect

wird der zu überprüfende Bereich abgefragt und eingegrenzt, läßt sich bei Bedarf auch oben ändern. Somit bekommst du keine Probleme mit Zellen, wo du anderes machen willst.
Ein Weiteres Problem besteht darin, daß es sich ja um ein Ereignis-Makro handelt. Wenn du aber die Leerzeichen löschst so tritt wieder das Change-ereignis in Kraft, d.h. das Makro ruft sich selbst aus. Das ist ungünstig und wird mit der

 Application.EnableEvents = False

Anweisung ausgeschaltet. Diese Sache muß zum Werte in die Zelle übertragen ausgeschaltet werden, muß aber unbedingt wieder zum Makroende angeschaltet werden.
D.h. wenn du das Makro mittendrin unterbrichst, so bleibt diese Sache ausgeschaltet, und anschließend funktioniert nichts mehr.
Zum Leerzeichenlöschen werden ganz zum Schluß einfach alle Zellen, die geändert worden sind direkt angesprochen. Geht mit der Trim() Funktion recht einfach.

Beste Grüße

Primut

Hi Primut,

du hast dich ja richtig in die Lösung meines Problems verbissen. Finde ich toll!

Ich habe jetzt leider keine Zeit, dein Listing auszuprobieren; werde ich heute Abend tun.

Vorab aber eine Frage. Eine If-Schleife verstehe ich nicht sofort:

If St = "$" Then
Else
Ad = Ad + St
End If

Warum folgt nach "Then" nichts?

Gruß peko

Hi Peko,

klar, wäre mal über ein Feedback interessiert, ob´s deinen Vorstellungen entsprechend läuft.

Mit der If-Schleife ist ganz einfach: wenn im Then Zweig nix steht, dann macht er dort auch nix, er soll also nur eine Aktion ausführen, wenn eine Bedingung efüllt ist, sonst nix.
Klar, ich hätte auch die Abfrage "$"<>St nehmen können und dann die Anweisung in den Then zweig und dafür else weglassen, wie auch immer...

Gruß Primut

hi ihr :))

If St <>"$" Then Ad = Ad + St

und eine bestehende markierung liesse sich so abfragen

If Selection.Cells.Count < 2 Then


End If

gruss nighty

Hallo Primut,

erst mal ganz herzlichen Dank!!!

Gestern bin ich doch nicht mehr dazu gekommen, deine Routine zu testen, habe das aber inzwischen getan. Alles funktioniert einwandfrei. Ich bin begeistert.

Nachts um fünf noch so eine Leistung, das finde ich einfach bemerkenswert! Noch einmal herzlichen Dank für deine Mühe.

Der gute alte nighty auch wieder dabei. Auch dir danke für deine Ergänzung. War ebenfalls sehr nützlich.

Liebe Grüße
peko

Hi peko,

Thanks fürs Feedback, freut mich ja, geholfen haben zu können.
Klar hat Nighty natürlich Recht, das Makro läßt sich also auch kürzer Schreiben:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Zelle As Range, Wert As Variant
Dim BereichAuswahl As Range
On Error Resume Next
Set BereichAuswahl = Range("B2:AZ41")
If Intersect(Target, BereichAuswahl) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Zelle In Selection
Wert = Zelle.Value
Zelle.Value = Trim(Wert)
Next
Application.EnableEvents = True
End Sub

Besten Gruß

Primut

Hallo Primut,

danke dir nochmals. Die Ergänzung von nighty hatte ich schon eingearbeitet.

Viele Grüße
peko

Hallo Primut und nighty,

und eventuell noch andere, die das Problem interessant genug finden, um damit Zeit zu verbringen!

Funktioniert ja alles inzwischen, aber leider doch nicht bestens.

Uhrzeiten, die gemäß Zellformatierung im Format hh:mm angezeigt werden sollten, werden nur als Nachkommawert eines ganzen Tages angezeigt (so wie sie ja auch gespeichert werden).

Also: Uhrzeit 18:00 wird als 0,75 angezeigt.

Das Datumsformat TT.MM.JJJJ wird nicht umgesetzt, Daten erscheinen trotzdem im Format TT.MM.JJ

Vielleicht hat da auch noch jemand eine Idee, wie EXCEL hier durch VBA "besiegt" werden kann.

Ich habe es noch nicht hinbekommen, viel Spaß und Erfolg beim Tüfteln!

Gruß peko

Hey peko,

mal als Hinweis: Eine haargenaue Erklärung des Problems ist meist schon seine halbe Lösung!!!
Woher soll ich denn wissen, welche Formate du alle verwenden willst???
Ich ging davon aus, du willst bei Texteingaben einfach die Leerzeichen innerhalb eines festen Bereiches entfernen.
Im übrigen werden beispielsweise Uhrzeiten, die mit vorhergehenden Leerzeichen eingegeben werden, von Excel eh´ nicht als Uhrzeiten erkannt!


Gruß Primut

Hi PEko,

???,
kleiner Tip: Du wirst Excel nie besiegen können, aber du kannst versuchen, dich positiv mit den Excel - Eigenheiten zu arrangieren.
Wenn du Daten oder Uhrzeiten mit Leerzeichen am Anfang eingibst, so erkennt Excel nicht wirklich das Datum oder die Uhrzeit sondern nur einen Datumähnlichen String.
Die Trim -Funktion entfernt zwar die Leerzeichen, aber der Sting bleibt, d.h. du kannst nicht wirklich als Datum damit weiterrechnen und also ist auch keine Formatierung als Datum möglich.
Sorry, ´ne zusätzliche Formatabfrage und Anpassung für jede Zelle ist mir jetzt zu aufwendig.

Trotzdem beste Grüße
Primut

Hallo Primut,

habe das Problem jetzt so gelöst, dass die gesamte Tabelle erst nach kompletter Eingabe durch die Leerzeichen-Routine nachbearbeitet wird.

Zellen mit Uhrzeiten oder Datumsangaben lasse ich dabei überspringen.

Danke für deine Mühe und liebe Grüße!
peko

• wieder Makro Code
• Automatische Zellfärbung/Bedingte Formatierung
• Berechnungsergebnis in andere Zelle
• Verweis wird ignoriert