ich habe ein Problem, das ich auch nach ungelogen 6h Internetrecherche und Rumprobieren nicht lösen konnte. Im Gegenteil: Je länger ich probierte, desto vertrackter wurde das Problem. Ich würde mich sehr freuen, wenn mir ein Profi bei der Lösung helfen könnte. Ich habe versucht, das ganze möglichst ausführlich zu beschreiben.
Ausgangslage:
Ich möchte, dass, wenn ich aus einer per Gültigkeitsprüfung (data validation) erstellten Auswahlliste einen Wert auswähle, dieser Wert in die Zelle 10 Zeilen untendrunter kopiert wird.
Da dies nur bei bestimmten Feldern der Fall sein soll, habe ich einen bereich namens range_change definiert.
Folgenden Code schrieb ich:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Set isect = Application.Intersect(Range("range_change"), Range(Target.Address))
If Not isect Is Nothing Then
MsgBox "Change-Handler aktiviert und Aenderung in ausgesuchtem Bereich"
Target.Offset(10, 0).FormulaR1C1 = Target.FormulaR1C1
MsgBox "Wert uebertragen"
End If
End Sub
Das Problem Nummer 1:
Dieser Code klappte bei mir nur halb. Wenn ich per DropDownMenü (data validation auswahlliste) einen Wert änderte, so sprang zwar der ChangeHandler an (die MsgBox erschien nämlich), es wurde aber kein Wert übertragen. Wenn ich allerdings den Wert per Hand eintrug oder den Zellinhalt löschte, so klappte alles wie gewünscht.
Der Code klappte auch vollständig, wenn ich in der Testzeile, die noch zum "range_change"-Bereich gehörte, aber für die keine Gültigkeitsauswahllisten definiert waren, Werte änderte.
Nach kurzer Recherche stieß ich darauf, dass in Excel 97 die Änderung eines Wertes durch eine Gültigkeitsauswahlliste nicht den Change-Handler aktivierte.
Dies konnte bei mir nicht der Fall sein, da ich a) excel 2007 benutze und b) die MsgBox ja erscheint.
Ich suchte weiter, und stieß unter anderem auf zwei Threads, in denen so ziemlich mein Problem beschrieben wurde:
http://www.pcreview.co.uk/forums/thread-971198.php
vor allem aber:
http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/ea7241ad-42dd-40f3-8eb4-a15c323338f9/
In letzterem Thread beschrieb jemand, wie eine Formel, die der meinen sehr gleicht, in einer bestimmten Arbeitsmappe klappte, in der anderen aber nicht.
Ich testete und...
Problem 2:
Mein Code klappt in einer bestimmten Arbeitsmappe nicht, in anderen aber schon. In den "funktionierenden" Arbeitsmappen klappt die Übertragung des Wertes auch bei Auswahl aus einer durch die Gültigkeitsprüfung festgelegten Liste.
Nachdem ich das festgestellt hatte, begann ich, die "fehlerhafte" Arbeitsmappe zu durchsuchen. Nach langem Suchen konnte ich den "Schuldigen" eindeutig ausmachen.
Es war eine benutzerdefinierte Funktion (in einem Modul) mit folgendem Code:
Public Function GET_MONTH_NR(cell_for_month)
Formula = cell_for_month.Formula
Dim Month_2 As String
Month_2 = Right(Formula, 2)
Dim cut_first As String
cut_first = Left(Month_2, 1)
Dim Month As Integer
If cut_first = "_" Then
Month = Right(Formula, 1)
Else
Month = Month_2
End If
GET_MONTH_NR = Month
End Function
Als ich diese UDF löschte, klappte mein Übertragungscode .
Und jetzt steh ich vor folgendem...
Problem 3 (das aktuelle)
Da der Fehler mit meinem Code 1 nur auftaucht, wenn in der Arbeitsmappe die UDF (Code2) steht, muss der Fehler in der Zusammenarbeit mit dieser UDF liegen. Und zwar auch nur, wenn der Berechnungsmodus auf "automatisch" steht.
Wenn ich auf manuell umstelle, klappt mein Code, auch sogar wenn der UDF-code (get_month_nr) da ist.
Ich will aber, dass mein Übertragungscode auch klappt, wenn der Berechnungsmodus auf automatisch gestellt ist.
Ich habe dann versucht, zu gucken, ob ich den Code der UDF get_month_nr so abändern kann, dass es keine Probleme mehr bei der Ausführung des Change-Handlers gibt, auch nicht im Automatikberechnungsmodus. Und siehe da:
bei
Public Function GET_MONTH_NR(cell_for_month as Range) as Integer
dim test As String
test = "hallo welt"
End Function
klappt alles wie gewünscht. Nur dass dann eben meine Get_Month_Nr_Funktion nicht mehr funktioniert. :-(
Es kann sein, dass meine UDF unsauber ist, was die Variablentypen anbelangt. Deswegen habe ich sie testweise total vereinfacht zu
Public Function GET_MONTH_NR(cell_for_month as Range) as Integer
dim test As String
test = cell_for_month.formula
End Function
Aber nichtmal das klappt. Folgender Code aber wie gesagt schon
Public Function GET_MONTH_NR(cell_for_month as Range) as Integer
dim test As String
test = "hallo welt"
End Function
Zusammenfassung:
Mein Change-Handler-Makro klappt:
- immer (!) wenn ich im Manuellen Berechnungsmodus bin
- im automatischen Berechnungsmodus, wenn:
- ich ihn nicht aus einer Zelle mit Dropdownliste aufrufe, sondern aus der Testzeile untendrunter
- ich ihn aus der Dropdownliste aufrufe, aber vorher die UDF entweder gelöscht habe oder die "cell_for_month"-Zeilen rausgenommen habe
Ich habe wirklich gar keinen Plan mehr, woran das liegen könnte und wie ich meine UDF oder sonstige Einstellungen abändern kann, damit mein Change-Handler-Makro klappt, auch bei automatischem Berechnungsmodus und auch wenn ich einen Wert aus einer Gültigkeitsprüfungaushwahlliste wähle.
Die gleiche Frage habe ich vor mehr als einer Woche schon in einem anderen Forum gestellt, und auch Antworten bekommen, die mein Problem jedoch nicht lösen konnten (http://www.office-loesung.de/ftopic304538_0_0_asc.php).
Deswegen würde ich mich sehr freuen, wenn mir jemand helfen könnte.
Die Problemdatei findet ihr unter https://www.herber.de/bbs/user/61198.xls.
Vielen Dank im Voraus!
Viele Grüße
Christian