Supportnet / Forum / Tabellenkalkulation
Warnung, wenn wert bereits vorhanden bzw. doppeleingabe vermeiden
Frage
ich möchte z.b. in spalte "a" alle lieferscheinnummern eingeben. wenn der wert allerdings schon einmal in dieser spalte eingegben wurde, dann soll eine warnung erscheinen, um eine doppelte eingabe zu vermeiden. bei herbers habe ich einen vba-text gefunden, doch irgendwie funktioniert dieser nicht:
Private Sub Worksheet_Change(ByValTarget As Excel.Range)
Dim GZelle As Range
If Target.Column <> 4 Or IsEmpty(Target) Then Exit Sub
If Application.CountIf(Range("A:A"), Target) - 1 Then
Beep
If MsgBox("Lieferschein schon eingetragen!" & _
vbLf & "Dennoch eintragen?", _
vbCritical + vbYesNo) = vbNo Then
Target.ClearContents
End If
End If
End Sub
nach dem ich siesen text eingegeben habe, schliesse ich den vba und speichere die mappe ab. wenn ich diese wieder öffne, klicke ich auf "makros aktivieren". wenn ich jetzt in spalte "a" identische zahlen eingebe passiert überhaupt nichts. ausserdem wudert es mich, wenn ich das makro über "makro ausführen" anwählen will, keines vorhanden ist. den vba-text sehe ich also nur, wenn ich in den vba gehe.
kann mir BITTE jemand weiterhelfen?! könnte es sein, dass irgendwas im text falsch ist, oder gibt es evtl. noch eine alternative, durch ein makro eine doppeleingabe zu vermeiden?
vielen dank für eure bemühungen!
gruss,
stefan
Antwort 1 von Celia
Hallo Stefan,
da ist einiges "schief" . Also erst einmal mußt du dieses Makro nicht als
Modul sondern direkt in die Tabelle (Tabellename z.B. "Tablelle " >rechter Mausklick > Code anzeigen ) reinkopieren.
Außerdem sind Fehler in dem Makro vergleich das mal hiermit:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim GZelle As Range
If Target.Column <> 4 Or IsEmpty(Target) Then Exit Sub
If Application.CountIf(Range("A:A"), Target) - 1 Then
Beep
If MsgBox("Lieferschein schon eingetragen!" & _
vbLf & "Dennoch eintragen?", _
vbCritical + vbYesNo) = vbNo Then
Target.ClearContents
End If
End If
End Sub
Aber ist das wirklich das was du gesucht hast ? Ich habs ausprobiert.
Da kommt nur wenn in Spalte 4 schon was drin steht die MsgBox.
Nicht aber bei einer Neueingabe in eine Zelle in Spalte 4 mit Inhalt der
in dieser Spalte schon mal vorgekommen ist.
Gruß
Celia
da ist einiges "schief" . Also erst einmal mußt du dieses Makro nicht als
Modul sondern direkt in die Tabelle (Tabellename z.B. "Tablelle " >rechter Mausklick > Code anzeigen ) reinkopieren.
Außerdem sind Fehler in dem Makro vergleich das mal hiermit:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim GZelle As Range
If Target.Column <> 4 Or IsEmpty(Target) Then Exit Sub
If Application.CountIf(Range("A:A"), Target) - 1 Then
Beep
If MsgBox("Lieferschein schon eingetragen!" & _
vbLf & "Dennoch eintragen?", _
vbCritical + vbYesNo) = vbNo Then
Target.ClearContents
End If
End If
End Sub
Aber ist das wirklich das was du gesucht hast ? Ich habs ausprobiert.
Da kommt nur wenn in Spalte 4 schon was drin steht die MsgBox.
Nicht aber bei einer Neueingabe in eine Zelle in Spalte 4 mit Inhalt der
in dieser Spalte schon mal vorgekommen ist.
Gruß
Celia
Antwort 2 von Aliba
Hi Stefan,
da gabs schon mal nen ähnlichen Thread, da gings allerdings in Spalte A steht die Auftragsnummer und B steht die Positionsnummer und in Kombination durften die auch nicht doppelt vorkommen. Der VBA hierzu ist folgender:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Then
Cells(Target.Row, 3).FormulaLocal = "=A" & Target.Row & "&B" & Target.Row
y = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To (y - 1)
If Cells(Target.Row, 3).Value = Cells(x, 3).Value Then
MsgBox "Achtung, schon vorhanden !"
End If
Next x
End If
End Sub
Der ist nicht von mir, sondern von Sicci . Ich kenn mich in VBA nun auch nicht so aus. D. h. ich weiß jetzt auch nicht, was an dieser Anweisung noch geändert wird, damit es sich nur auf eine Spalte bezieht. Aber vielleicht schaut sich ja jemand um, der diesen VBA entsprechend ändern kann. Oder du umgehst es, indem du deine lieferscheinnummern aufteilst. Also 1. Ziffer in Spalte A, rest in Spalte B, Spalte C wird für die Berechnung gebraucht, die kannste ausblenden. Wie oben schon erwähnt, unten auf Tabelle1, oder wie dein Tabellenblatt heißt , rechten Mausklickk, Code anzeigen, alles rauslöschen und dann obigen Text hineinkopieren.
Vielleicht schafft dus ja auch, obige Anweisung so zu ändern, daß es mit einer Spalte funzt.
CU Aliba
da gabs schon mal nen ähnlichen Thread, da gings allerdings in Spalte A steht die Auftragsnummer und B steht die Positionsnummer und in Kombination durften die auch nicht doppelt vorkommen. Der VBA hierzu ist folgender:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Then
Cells(Target.Row, 3).FormulaLocal = "=A" & Target.Row & "&B" & Target.Row
y = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To (y - 1)
If Cells(Target.Row, 3).Value = Cells(x, 3).Value Then
MsgBox "Achtung, schon vorhanden !"
End If
Next x
End If
End Sub
Der ist nicht von mir, sondern von Sicci . Ich kenn mich in VBA nun auch nicht so aus. D. h. ich weiß jetzt auch nicht, was an dieser Anweisung noch geändert wird, damit es sich nur auf eine Spalte bezieht. Aber vielleicht schaut sich ja jemand um, der diesen VBA entsprechend ändern kann. Oder du umgehst es, indem du deine lieferscheinnummern aufteilst. Also 1. Ziffer in Spalte A, rest in Spalte B, Spalte C wird für die Berechnung gebraucht, die kannste ausblenden. Wie oben schon erwähnt, unten auf Tabelle1, oder wie dein Tabellenblatt heißt , rechten Mausklickk, Code anzeigen, alles rauslöschen und dann obigen Text hineinkopieren.
Vielleicht schafft dus ja auch, obige Anweisung so zu ändern, daß es mit einer Spalte funzt.
CU Aliba
Antwort 3 von Aliba
Hi Stefan,
jetzt bin ichs noch mal. Hab mal rumprobiert und es geschafft, die Anweisung entsprechend zu ändern, daß sie mit 1 Spalte funktioniert.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Cells(Target.Row, 2).FormulaLocal = "=A" & Target.Row
y = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To (y - 1)
If Cells(Target.Row, 2).Value = Cells(x, 2).Value Then
MsgBox "Achtung, schon vorhanden !"
End If
Next x
End If
End Sub
Also bei mir funzt das. Aber wie gesagt , die Spalte B muß frei sein.
Viel Glück
Aliba
jetzt bin ichs noch mal. Hab mal rumprobiert und es geschafft, die Anweisung entsprechend zu ändern, daß sie mit 1 Spalte funktioniert.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Cells(Target.Row, 2).FormulaLocal = "=A" & Target.Row
y = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To (y - 1)
If Cells(Target.Row, 2).Value = Cells(x, 2).Value Then
MsgBox "Achtung, schon vorhanden !"
End If
Next x
End If
End Sub
Also bei mir funzt das. Aber wie gesagt , die Spalte B muß frei sein.
Viel Glück
Aliba
Antwort 4 von STEFAN
hi aliba und celia,
vielen dank für die bemühungen! bei mir hat´s auch funktioniert. allerdings erscheint die gleiche zahl, die ich in spalte a eingebe, gleichzeitig in spalte b. hat vielleicht jemand eine ahnung, dass dann nicht gleichzeitig die zahl aus a in b erscheint? ich brauche also nur die spalte a. die spalte b ist völlig uninteressant. optimal wäre es natürlich, wenn ich wie im bsp. von celia die möglichkeit hätte, durch eine msg-box evtl. doch die zahl doppelt eintragen zu können bzw. dann doch ablehnen zu können. doch irgendwie ist im text ein problem, dass er sich auf die spalte d bezieht und sonstige probleme bezügl. einer zelle verursacht.
vielleicht kann mir jemand weiterhelfen, den text ein wenig zu verbessern?
trotzdem schon mal vielen dank!
gruss,
stefan
vielen dank für die bemühungen! bei mir hat´s auch funktioniert. allerdings erscheint die gleiche zahl, die ich in spalte a eingebe, gleichzeitig in spalte b. hat vielleicht jemand eine ahnung, dass dann nicht gleichzeitig die zahl aus a in b erscheint? ich brauche also nur die spalte a. die spalte b ist völlig uninteressant. optimal wäre es natürlich, wenn ich wie im bsp. von celia die möglichkeit hätte, durch eine msg-box evtl. doch die zahl doppelt eintragen zu können bzw. dann doch ablehnen zu können. doch irgendwie ist im text ein problem, dass er sich auf die spalte d bezieht und sonstige probleme bezügl. einer zelle verursacht.
vielleicht kann mir jemand weiterhelfen, den text ein wenig zu verbessern?
trotzdem schon mal vielen dank!
gruss,
stefan
Antwort 5 von Aliba
Hallo Stefan,
guten Morgen. Ich habe jetzt den Text nochmal geändert, daß die Abfrage nach Eintragen Ja/Nein auch erscheinst, und du auswählen kannst.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Cells(Target.Row, 2).FormulaLocal = "=A" & Target.Row
y = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To (y - 1)
If MsgBox("Lieferschein schon eingetragen!" & _
vbLf & "Dennoch eintragen?", _
vbCritical + vbYesNo) = vbNo Then
Target.ClearContents
End If
Next x
End If
End Sub
Die Spalte B braucht diese Anweisung als Hilfsspalte. Du kannst die Spalte aber ausblenden lassen, indem du in das B der Spalte mit der rechten Maustaste klickst, und dann "Ausblenden" anklickst. Dann ist die Spalte optisch verschwunden.
Viel Glück
CU Aliba
guten Morgen. Ich habe jetzt den Text nochmal geändert, daß die Abfrage nach Eintragen Ja/Nein auch erscheinst, und du auswählen kannst.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Cells(Target.Row, 2).FormulaLocal = "=A" & Target.Row
y = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To (y - 1)
If MsgBox("Lieferschein schon eingetragen!" & _
vbLf & "Dennoch eintragen?", _
vbCritical + vbYesNo) = vbNo Then
Target.ClearContents
End If
Next x
End If
End Sub
Die Spalte B braucht diese Anweisung als Hilfsspalte. Du kannst die Spalte aber ausblenden lassen, indem du in das B der Spalte mit der rechten Maustaste klickst, und dann "Ausblenden" anklickst. Dann ist die Spalte optisch verschwunden.
Viel Glück
CU Aliba
Antwort 6 von sicci
..
also so sieht ein mögliher Code aus, wenn keine Hilfsspalte da sein soll:
(Suchspalte = A)
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x%
Dim m
If Target.Column = 1 Then
For x = 1 To Target.Row - 1
If Cells(Target.Row, 1) = Cells(x, 1) Then GoTo weiter:
Next x
Else: Exit Sub
weiter:
Application.EnableEvents = False
m = MsgBox("Achtung! schon vorhanden - trotzdem übernehmen?", vbYesNo)
If m = vbNo Then
Cells(Target.Row, 1).ClearContents
Cells(Target.Row, 1).Select
Application.EnableEvents = True
Exit Sub
Else: Application.EnableEvents = True
Exit Sub
End If
End If
End Sub
-- das EnableEvents=false ist wichtig, damit Excel die Suche nicht x-mal fortsetzt, wenns in der Suchspalte gewollte doppelte Einträge gibt. Das =true muß danach unbedingt wieder gesetzt werden (für Nein und Ja Fall), sonst kann z.B. Excel nicht mehr beendet werden.
--
Gruß
sicci
also so sieht ein mögliher Code aus, wenn keine Hilfsspalte da sein soll:
(Suchspalte = A)
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x%
Dim m
If Target.Column = 1 Then
For x = 1 To Target.Row - 1
If Cells(Target.Row, 1) = Cells(x, 1) Then GoTo weiter:
Next x
Else: Exit Sub
weiter:
Application.EnableEvents = False
m = MsgBox("Achtung! schon vorhanden - trotzdem übernehmen?", vbYesNo)
If m = vbNo Then
Cells(Target.Row, 1).ClearContents
Cells(Target.Row, 1).Select
Application.EnableEvents = True
Exit Sub
Else: Application.EnableEvents = True
Exit Sub
End If
End If
End Sub
-- das EnableEvents=false ist wichtig, damit Excel die Suche nicht x-mal fortsetzt, wenns in der Suchspalte gewollte doppelte Einträge gibt. Das =true muß danach unbedingt wieder gesetzt werden (für Nein und Ja Fall), sonst kann z.B. Excel nicht mehr beendet werden.
--
Gruß
sicci
Antwort 7 von sTefaN
erst einmal vielen dank für die ausführliche hilfe!
sicci, dein text bezieht sich nur auf spalte a. jetzt habe ich alle zahlen von 1 in 2 geändert, in der hoffnung, dass dich die 1 auf spalte a und die 2 auf die spalte b bezieht. hat allerdings nicht funktioniert. kann ich denn im text nicht irgendeinen bereich festlegen für z.b. spalte c "c:c" oder bei mehreren spalten z.b. "c:f", sodass sich aber die warnung mit der doppeleingabe nicht auf den kompletten bereich c:f bezieht, sondern nur jeweils in den entsprechenden spalten. das heisst allerdings auch, dass es evtl. z.b. nur die spalten c,d,f betrifft und e nicht damit versehen wird. da sich die benötigte spaltenanzahl in einer kleineren größenordnung bewegt, würde ich es vorziehen, im makro z.b. c,d,f oder c,d,e,f anzugeben. kommt drauf an, was halt benötigt wird. wenn dann z.b. in c eine 2 steht und in f eine 2 eingegeben wird, dann soll natürlich keine warnung angezeigt werde. wenn ich jetzt also in c noch eine 2 reinschreibe, dann soll die warnung erscheinen. der text soll sich also auf einen bestimmten bereich aber jede spalte für sich beziehen.
ausserdem hab ich mal wieder ein problem in sachen zellenschutz. weisst du noch das bsp. mit dem schutz für alle tabellen innerhalb einer mappe?! diesmal soll eine zelle gesperrt werden, wenn etwas drinsteht. das heisst, wenn ich in eine zelle etwas reinschreibe und dann durch tab oder enter in eine nächst zelle wechsle, dann soll die zelle, in der ich etwas reingeschrieben habe, automatisch gesperrt werden. wenn ich also die besagte zelle überschreiben oder den inhalt löschen will, dann soll wieder eine msg-box erscheinen mit einer kennwortaufforderung. das kennwort soll wieder im makro vorgegeben sein, z.b. "schutz". wenn ich jetzt das richtige kennwort eingebe, dann ist die zelle wieder freigegeben, bis ich wieder etwas reinschreibe. wenn ich dann die beschriebene zelle mittels tab oder enter wieder verlasse, dann soll sie automatisch wieder gesperrt werden. auch hier möchte ich einen bestimmten bereich festlegen. das heisst alle spalten, von z.b. "b:m" ,oder das ich nur div. spalten auswähle, wie z.b a,f,l,o. die sperre soll sich also nur auf bestimmte spalten beziehen.
muss ich jetzt ein solches makro über alt+f11 (modul)oder über tabelle1+re. maustast +code anzeigen einfügen ?
zu diesen zwei problemen habe ich bei herbers nichts tolles gefunden. deshalb muss ich dich wohl "schon wieder" fragen. wäre nett, wenn du mir weiterhelfen könntest!
vielen dank für deine bemühungen!
gruss,
stefan
sicci, dein text bezieht sich nur auf spalte a. jetzt habe ich alle zahlen von 1 in 2 geändert, in der hoffnung, dass dich die 1 auf spalte a und die 2 auf die spalte b bezieht. hat allerdings nicht funktioniert. kann ich denn im text nicht irgendeinen bereich festlegen für z.b. spalte c "c:c" oder bei mehreren spalten z.b. "c:f", sodass sich aber die warnung mit der doppeleingabe nicht auf den kompletten bereich c:f bezieht, sondern nur jeweils in den entsprechenden spalten. das heisst allerdings auch, dass es evtl. z.b. nur die spalten c,d,f betrifft und e nicht damit versehen wird. da sich die benötigte spaltenanzahl in einer kleineren größenordnung bewegt, würde ich es vorziehen, im makro z.b. c,d,f oder c,d,e,f anzugeben. kommt drauf an, was halt benötigt wird. wenn dann z.b. in c eine 2 steht und in f eine 2 eingegeben wird, dann soll natürlich keine warnung angezeigt werde. wenn ich jetzt also in c noch eine 2 reinschreibe, dann soll die warnung erscheinen. der text soll sich also auf einen bestimmten bereich aber jede spalte für sich beziehen.
ausserdem hab ich mal wieder ein problem in sachen zellenschutz. weisst du noch das bsp. mit dem schutz für alle tabellen innerhalb einer mappe?! diesmal soll eine zelle gesperrt werden, wenn etwas drinsteht. das heisst, wenn ich in eine zelle etwas reinschreibe und dann durch tab oder enter in eine nächst zelle wechsle, dann soll die zelle, in der ich etwas reingeschrieben habe, automatisch gesperrt werden. wenn ich also die besagte zelle überschreiben oder den inhalt löschen will, dann soll wieder eine msg-box erscheinen mit einer kennwortaufforderung. das kennwort soll wieder im makro vorgegeben sein, z.b. "schutz". wenn ich jetzt das richtige kennwort eingebe, dann ist die zelle wieder freigegeben, bis ich wieder etwas reinschreibe. wenn ich dann die beschriebene zelle mittels tab oder enter wieder verlasse, dann soll sie automatisch wieder gesperrt werden. auch hier möchte ich einen bestimmten bereich festlegen. das heisst alle spalten, von z.b. "b:m" ,oder das ich nur div. spalten auswähle, wie z.b a,f,l,o. die sperre soll sich also nur auf bestimmte spalten beziehen.
muss ich jetzt ein solches makro über alt+f11 (modul)oder über tabelle1+re. maustast +code anzeigen einfügen ?
zu diesen zwei problemen habe ich bei herbers nichts tolles gefunden. deshalb muss ich dich wohl "schon wieder" fragen. wäre nett, wenn du mir weiterhelfen könntest!
vielen dank für deine bemühungen!
gruss,
stefan
Antwort 8 von sicci
Hallo Stefan
also heut noch erstmal zum ersten Problem: die 1 bedeutet die Spalte A, 2 für B etc. Vielleicht hast Du auch die 1, im Makro die Variable x, die die Zeilen (von 1 - letzte vor der aktiven) zählt, mitgeändert und es funktionierte deshalb nicht.
Hier ein Vorschlag, in dem die Spalte einfach in eine Variabel (s) gesteckt wird und sich je nach Zelle, die gerade geändert wird, ändert - hab mal Spalte A-G genommen. Auf die Art muß man dasselbe Makro nicht mehrmals schreiben.
Vorab aber noch zu Deiner nicht unwichtigen Frage: wohin wird der Code geschrieben? Außer Objekten (wie Tabellenblatt=Worksheet, Zelle=Range, Arbeitsmappe=Workbook etc.) gibts im Excel ja noch das eigentlich Schöne: es passiert was beim Bearbeiten einer Datei.
Alles, was passiert, wird "Ereignis" genannt. Ereignisse passieren also in der Arbeitsmappe oder im Tabellenblatt. Jeder Code(Makro), der ausgeführt werden soll, wenn sowas passiert, muß dem Blatt oder der Mappe zugeordnet werden.
Daß etwas passiert, ist ja dem Programm selbst zu verdanken und so gibt das Programm auch die Möglichkeit zu sagen: wenn dieser oder jene Programmprozedur abläuft, dann soll noch was zusätzliches ablaufen, nämlich mein Makro.
Die Ereignisse sind also fest vorgegeben und wenn Du im Code des Tabellenblatts auf den Pfeil in der oberen rechten Box klickst, siehst Du die vorhandenen Ereignisse:
Selection_Change für: Markierung wird geändert
Change für: Wert wird geändert
Calculate für: Blatt wird berechnet
etc.
Alle Makros die Du zu den Ereignissen eingibst, laufen ab, wenn das Ereignis eintritt. Sie brauchen also nicht gesondert aufgerufen zu werden.
Nicht ereignisgebundende Makros schreibt man in Module.
.. Glaube das langt für heute als kleine Einstiegserklärung .. ist gar nicht so einfach in Worte zu fassen ..
Hier nun der Code fürs Tabellenblatt im Change-Ereignis:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x%, s%
Dim m
If Target.Column = 1 Then
s = 1
End If
If Target.Column = 2 Then
s = 2
End If
If Target.Column = 3 Then
s = 3
End If
If Target.Column = 4 Then
s = 4
End If
If Target.Column = 5 Then
s = 5
End If
If Target.Column = 6 Then
s = 6
End If
If Target.Column = s Then
For x = 1 To Target.Row - 1
If Cells(Target.Row, s) = Cells(x, s) Then GoTo weiter:
Next x
Else: Exit Sub
weiter:
Application.EnableEvents = False
m = MsgBox("Achtung! schon vorhanden - trotzdem übernehmen?", vbYesNo)
If m = vbNo Then
Cells(Target.Row, s).ClearContents
Cells(Target.Row, s).Select
Application.EnableEvents = True
Exit Sub
Else: Application.EnableEvents = True
Exit Sub
End If
End If
End Sub
Zu Deinem zweiten Problem (Zellschutz) meld ich mich nochmal, wahrscheinlich erst Sa, da ich morgen abend erstmal das WE einläute.
Und jetzt hol ich mir noch ne Mütze Schlaf :)
Gruß
sicci
PS: die vielen if-thens könnt man bestimmt mit select case eleganter lösen, aber bis jetzt hab ich mich erfolgreich davor gedrückt es anzuwenden (muß ich noch bißchen üben ;-)
Nochn Tip, wenn die Ereignisse scheinbar nicht mehr stattfinden (bzw. Dein ereignisgebundenes Makro einfach icht abläuft): manchmal hängt Excel dann im Application.EnableEvents=False, das EreignisProzedurabläufe ausschaltet. Einfach in ein Modul (einfügen/Modul) folgenden Code schreiben:
sub Anschalten()
Application.EnableEvents=True
end sub
... und ausführen ....
also heut noch erstmal zum ersten Problem: die 1 bedeutet die Spalte A, 2 für B etc. Vielleicht hast Du auch die 1, im Makro die Variable x, die die Zeilen (von 1 - letzte vor der aktiven) zählt, mitgeändert und es funktionierte deshalb nicht.
Hier ein Vorschlag, in dem die Spalte einfach in eine Variabel (s) gesteckt wird und sich je nach Zelle, die gerade geändert wird, ändert - hab mal Spalte A-G genommen. Auf die Art muß man dasselbe Makro nicht mehrmals schreiben.
Vorab aber noch zu Deiner nicht unwichtigen Frage: wohin wird der Code geschrieben? Außer Objekten (wie Tabellenblatt=Worksheet, Zelle=Range, Arbeitsmappe=Workbook etc.) gibts im Excel ja noch das eigentlich Schöne: es passiert was beim Bearbeiten einer Datei.
Alles, was passiert, wird "Ereignis" genannt. Ereignisse passieren also in der Arbeitsmappe oder im Tabellenblatt. Jeder Code(Makro), der ausgeführt werden soll, wenn sowas passiert, muß dem Blatt oder der Mappe zugeordnet werden.
Daß etwas passiert, ist ja dem Programm selbst zu verdanken und so gibt das Programm auch die Möglichkeit zu sagen: wenn dieser oder jene Programmprozedur abläuft, dann soll noch was zusätzliches ablaufen, nämlich mein Makro.
Die Ereignisse sind also fest vorgegeben und wenn Du im Code des Tabellenblatts auf den Pfeil in der oberen rechten Box klickst, siehst Du die vorhandenen Ereignisse:
Selection_Change für: Markierung wird geändert
Change für: Wert wird geändert
Calculate für: Blatt wird berechnet
etc.
Alle Makros die Du zu den Ereignissen eingibst, laufen ab, wenn das Ereignis eintritt. Sie brauchen also nicht gesondert aufgerufen zu werden.
Nicht ereignisgebundende Makros schreibt man in Module.
.. Glaube das langt für heute als kleine Einstiegserklärung .. ist gar nicht so einfach in Worte zu fassen ..
Hier nun der Code fürs Tabellenblatt im Change-Ereignis:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x%, s%
Dim m
If Target.Column = 1 Then
s = 1
End If
If Target.Column = 2 Then
s = 2
End If
If Target.Column = 3 Then
s = 3
End If
If Target.Column = 4 Then
s = 4
End If
If Target.Column = 5 Then
s = 5
End If
If Target.Column = 6 Then
s = 6
End If
If Target.Column = s Then
For x = 1 To Target.Row - 1
If Cells(Target.Row, s) = Cells(x, s) Then GoTo weiter:
Next x
Else: Exit Sub
weiter:
Application.EnableEvents = False
m = MsgBox("Achtung! schon vorhanden - trotzdem übernehmen?", vbYesNo)
If m = vbNo Then
Cells(Target.Row, s).ClearContents
Cells(Target.Row, s).Select
Application.EnableEvents = True
Exit Sub
Else: Application.EnableEvents = True
Exit Sub
End If
End If
End Sub
Zu Deinem zweiten Problem (Zellschutz) meld ich mich nochmal, wahrscheinlich erst Sa, da ich morgen abend erstmal das WE einläute.
Und jetzt hol ich mir noch ne Mütze Schlaf :)
Gruß
sicci
PS: die vielen if-thens könnt man bestimmt mit select case eleganter lösen, aber bis jetzt hab ich mich erfolgreich davor gedrückt es anzuwenden (muß ich noch bißchen üben ;-)
Nochn Tip, wenn die Ereignisse scheinbar nicht mehr stattfinden (bzw. Dein ereignisgebundenes Makro einfach icht abläuft): manchmal hängt Excel dann im Application.EnableEvents=False, das EreignisProzedurabläufe ausschaltet. Einfach in ein Modul (einfügen/Modul) folgenden Code schreiben:
sub Anschalten()
Application.EnableEvents=True
end sub
... und ausführen ....
Antwort 9 von sicci
Hallo Stefan
doch noch heut zu Deinem Zellschutz-Problem. Zu schützende Zelle im Beispielcode: A1.
Wenn Du mehrere Zellen mit demselben Paßwort schützen willst, dann steck Target, wie im gestrigen Beispiel beschrieben, wieder in eine Variable.
Diesmal wird der Code ins Selection_Change Ereignis geschrieben, weil ja schon beim Selektieren der Zelle die Abfrage erfolgen soll.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim x As String
If Cells(1, 1).Value = Empty Then
Application.EnableEvents = False
Cells(Target.Row, Target.Column).Select
Application.EnableEvents = True
Exit Sub
End If
If Target = Cells(1, 1) Then
x = InputBox("bitte Kennwort eingeben")
If x = "Schutz" Then
Application.EnableEvents = False
Cells(1, 1).Select
Application.EnableEvents = True
Else: Cells(2, 1).Select
MsgBox " .. nur der Autor darf Zelle A1 bearbeiten !! "
End If
End If
End Sub
-----------
Um das Ereignis Markieren/Selektieren von Zellen anzusprechen kann man die Zelle fest angeben (zB: Cells(1,1)) oder ganz allgemein ( Cells(Target.Row, Target.Column), was sowas heißt wie markierteZeile, makrierteSpalte)
Gruß
sicci
doch noch heut zu Deinem Zellschutz-Problem. Zu schützende Zelle im Beispielcode: A1.
Wenn Du mehrere Zellen mit demselben Paßwort schützen willst, dann steck Target, wie im gestrigen Beispiel beschrieben, wieder in eine Variable.
Diesmal wird der Code ins Selection_Change Ereignis geschrieben, weil ja schon beim Selektieren der Zelle die Abfrage erfolgen soll.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim x As String
If Cells(1, 1).Value = Empty Then
Application.EnableEvents = False
Cells(Target.Row, Target.Column).Select
Application.EnableEvents = True
Exit Sub
End If
If Target = Cells(1, 1) Then
x = InputBox("bitte Kennwort eingeben")
If x = "Schutz" Then
Application.EnableEvents = False
Cells(1, 1).Select
Application.EnableEvents = True
Else: Cells(2, 1).Select
MsgBox " .. nur der Autor darf Zelle A1 bearbeiten !! "
End If
End If
End Sub
-----------
Um das Ereignis Markieren/Selektieren von Zellen anzusprechen kann man die Zelle fest angeben (zB: Cells(1,1)) oder ganz allgemein ( Cells(Target.Row, Target.Column), was sowas heißt wie markierteZeile, makrierteSpalte)
Gruß
sicci
Antwort 10 von stefaN
hallo sicci,
der text für den schutz der zelle a1 funktioniert einwandfrei. jetzt habe ich versucht, wieder einen bereich zu bestimmen wie im bsp. mit der doppelten eingabe. es sollen also nur bestimmte spalten mit dem makro versehen werden, wenn man eine mit inhalt versehene zelle ändern möchte. ich habe also versucht deinen text abzuändern, um die spalte a und c (also 1 und 3) mit dem text zu versehen. ziel ist es also, sobald versucht wird den inhalt einer zelle in spalte a und c zu verändern bzw. zu selektieren, die aufforderung zur passworteingabe erfolgt. der abgeänderte text sieht dann so aus:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim x%, s%
Dim m
If Target.Column = 1 Then
s = 1
End If
If Target.Column = 3 Then
End If
If Target.Column = s Then
x = InputBox("bitte Kennwort eingeben")
If x = "Schutz" Then
Application.EnableEvents = False
Column = s . Select
Application.EnableEvents = True
Else: Column = s.Select
MsgBox " .. nur der Autor darf Zelle A1 bearbeiten !! "
End If
End If
End Sub
leider muss ich da wieder irgendein eingabefehler gemacht haben. ich hab über eine stunde versucht das problem zu lösen, doch anscheinend bin ich doch noch nicht so fit in vba.
ich komm da irgendwie mit cells, select, enableevants und x=1 nicht so klar. was muss ich denn aus deinem text weglöschen und anschliessend ergänzen, um spalte a und c usw. mit dem makro zu versehen?
danke
gruss
stefan
der text für den schutz der zelle a1 funktioniert einwandfrei. jetzt habe ich versucht, wieder einen bereich zu bestimmen wie im bsp. mit der doppelten eingabe. es sollen also nur bestimmte spalten mit dem makro versehen werden, wenn man eine mit inhalt versehene zelle ändern möchte. ich habe also versucht deinen text abzuändern, um die spalte a und c (also 1 und 3) mit dem text zu versehen. ziel ist es also, sobald versucht wird den inhalt einer zelle in spalte a und c zu verändern bzw. zu selektieren, die aufforderung zur passworteingabe erfolgt. der abgeänderte text sieht dann so aus:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim x%, s%
Dim m
If Target.Column = 1 Then
s = 1
End If
If Target.Column = 3 Then
End If
If Target.Column = s Then
x = InputBox("bitte Kennwort eingeben")
If x = "Schutz" Then
Application.EnableEvents = False
Column = s . Select
Application.EnableEvents = True
Else: Column = s.Select
MsgBox " .. nur der Autor darf Zelle A1 bearbeiten !! "
End If
End If
End Sub
leider muss ich da wieder irgendein eingabefehler gemacht haben. ich hab über eine stunde versucht das problem zu lösen, doch anscheinend bin ich doch noch nicht so fit in vba.
ich komm da irgendwie mit cells, select, enableevants und x=1 nicht so klar. was muss ich denn aus deinem text weglöschen und anschliessend ergänzen, um spalte a und c usw. mit dem makro zu versehen?
danke
gruss
stefan
Antwort 11 von sTeFan
hi sicci,
ich bins wieder. mit dem längeren problem, welches ich dir in der email geschrieben habe, bin ich jetzt etwas schlauer geworden. es handelt sich also um eine inputbox, die sich ständig z.b. rechts neben der tabelle befindet. durch eingabe der te-nummern (also mehrere) soll diese inputbox also eine suchfunktion erfüllen, sodass nur die zu suchenden te-nummern angezeigt werden (ähnlich wie filterfunktion). hoffentlich ist dir die sache jrtzt klarer geworden. ausserdem bin ich bei dem schutz über mehrere spalten, wenn inhalt in zelle, noch nicht weitergekommen )siehe mein letztes kommentar. des weiteren funktioniert die normale filterfunktion nicht, wenn ich ein blattschutz aktiviere. den kurzen makrotext, den ich bei herbers gefunden habe hat nicht funktioniert. beim schutz soll also trotzdem noch die filterfunktion funktionieren.
also dann, bis bald.
p.s. schönes wochenende!
gruss,
stefan
ich bins wieder. mit dem längeren problem, welches ich dir in der email geschrieben habe, bin ich jetzt etwas schlauer geworden. es handelt sich also um eine inputbox, die sich ständig z.b. rechts neben der tabelle befindet. durch eingabe der te-nummern (also mehrere) soll diese inputbox also eine suchfunktion erfüllen, sodass nur die zu suchenden te-nummern angezeigt werden (ähnlich wie filterfunktion). hoffentlich ist dir die sache jrtzt klarer geworden. ausserdem bin ich bei dem schutz über mehrere spalten, wenn inhalt in zelle, noch nicht weitergekommen )siehe mein letztes kommentar. des weiteren funktioniert die normale filterfunktion nicht, wenn ich ein blattschutz aktiviere. den kurzen makrotext, den ich bei herbers gefunden habe hat nicht funktioniert. beim schutz soll also trotzdem noch die filterfunktion funktionieren.
also dann, bis bald.
p.s. schönes wochenende!
gruss,
stefan
Antwort 12 von wolfram
hi stefan!
ich weiß nicht, ob du unbedingt ein macro willst, aber die aufgabe läßt sich auch mit folgender formel lösen:
(die werte stehen in a2:a22; die formel stehl in b2 und kann nach unten kopiert werden:
=WENN(ISTNV(VERGLEICH(A2;A3:$A$22;0));"ok";"doppelt")
gruß
wolfram
ich weiß nicht, ob du unbedingt ein macro willst, aber die aufgabe läßt sich auch mit folgender formel lösen:
(die werte stehen in a2:a22; die formel stehl in b2 und kann nach unten kopiert werden:
=WENN(ISTNV(VERGLEICH(A2;A3:$A$22;0));"ok";"doppelt")
gruß
wolfram