| |
Visual-Basic EinsteigerVBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 03.04.23 15:06 |
| Hallo liebe Foren-User,
ich habe eine Excel-Datei, die als digitaler Urlaubsantrag fungiert.
Einige Funktionen laufen über VBA-Code und das funktioniert auch recht gut.
Der Urlaubsantrag funktioniert über 3 Hauptschritte, wobei nach jedem Schritt die Datei gespeichert werden muss.
Die/Der Mitarbeiter gibt während seiner Eingaben einen eigenen Code ein, die/der Abt.Leiter ebenso und am Ende auch die Pers.Abt.
Zur Info:
- Das Tabellenblatt ist per Blattschutz mit Kennwort geschützt!!
- Der Bereich, in dem Eingaben gemacht werden können, ist A9 - Q20
- Es handelt sich also um 12 Zeilen, in denen Urlaube eingetragen werden können
Nun möchte ich den vorhandenen Code gerne erweitern, sodass in dem Moment, wo die Pers.Abt. ihren Code eingegeben hat, die folgenden Dinge passieren:
- die Datei soll gespeichert werden, ohne dass nochmal explizit auf "Speichern" geklickt muss
- eine Messagebox soll erscheinen
Zum besseren Verständnis, hier mal die komplette Abfolge der einzelnen Schritte:
1) Mitarbeiter gibt seinen Code ein und speichert die Excel-Datei auf herkömmliche Weise (die Zeile bleibt editierbar)
2) AL gibt seinen Code ein und speichert die Excel-Datei auf herkömmliche Weise (die Zeile bleibt editierbar)
3) Pers.Chefin gibt ihren Code ein. Dadurch...
3a) erscheint in Spalte P der Eintrag "genehmigt" und in Spalte Q das akt. Tagesdatum
3b) Gleichzeitig wird mit dem Eintrag des Tagesdatums diese Zeile gesperrt
3c) Und der Zellbereich "A-E" und "G-K" wird grau formatiert
3d) zusätzlich soll der Antrag jetzt gespeichert werden, ohne dass die Pers.Abt. nochmal explizit auf "Speichern" klicken muss.
3e) und eine Messagebox soll erscheinen >> "Urlaub gespeichert - Zeile nicht mehr editierbar!"
Während des kompletten Ablaufs bleibt das Tabellenblatt immer geschützt!!
Die Aufhebung des Schutzes ist nur dazu da, um notfalls Änderungen auch nachträglich vornehmen zu können (nach Absprache m.d. Pers.Abt.)
Hier mal ein Screenshot des bisherigen VBA-Codes:
Grüße,
imebro
Beitrag wurde zuletzt am 03.04.23 um 15:08:05 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 05.04.23 14:39 |
| hmmm... hat niemand von Euch eine Idee hierzu?
Grüße,
imebro | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 12.04.23 12:10 |
| Hi, es ist nicht immer einfach, eine Problembeschreibung nachzuvollziehen. Aber ist Dir hier recht gut gelungen, auch wenn (mir) nicht alles ganz klar ist.
(In Excel kenne ich mich eher weniger aus, bin vor Jahren auf "VB6" umgestiegen.)
Die Kommandos lauten - und das ist sicher nicht neu -
1. ThisWorkbook.Save
2. Msgbox "Aufgabe erledigt.", 64
Da Du den Ablauf Deines Makros am besten kennst, könntest Du diese Kommandos doch dort einfügen, wo die gesamte Prüfung erfolgreich durchlaufen wurde. Oder nicht? | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 13.04.23 08:46 |
| Hallo und danke für die Antwort.
Habe es jetzt nochmal versucht mit Deinen Tipps.
Aber es gibt 2 Probleme und eine Frage:
1) Die Message-Box erscheint zwei Mal
2) Die Message-Box erscheint erst, nachdem man MANUELL in der Excel-Datei auf "speichern" geklickt hat.
Es soll ja so sein, dass AUTOMATISCH gespeichert wird, wenn die Zeile durch den Code gesperrt und grau eingefärbt wurde.
Und DANACH soll erst die Message-Box erscheinen.
Frage:
Was bedeutet das ",64" bei Deiner Angabe (MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!",64)?
Hier mal der aktuelle Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim objCell As Range
Dim loZ As Long
With Worksheets("Eingaben")
For Each objCell In .Range("Q9:Q20")
If IsDate(objCell.Value) Then
loZ = objCell.Row
objCell.EntireRow.Locked = True
objCell.Interior.Color = RGB(221, 221, 221)
.Range(.Cells(loZ, 1), .Cells(loZ, 5)).Interior.Color = RGB( _
221, 221, 221)
.Range(.Cells(loZ, 7), .Cells(loZ, 11)).Interior.Color = RGB( _
221, 221, 221)
ThisWorkbook.Save
MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!"
End If
Next
End With
End Sub Hast Du oder sonst Jemand noch eine Idee?
Grüße,
imebro
Beitrag wurde zuletzt am 13.04.23 um 08:50:28 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 13.04.23 11:01 |
| Ok, ja, es ist nicht immer alles so nachvollziehbar, was die einzelnen User schreiben. Ich selbst bin auch immer unsicher, ob meine Worte klar sind. Aber ggf. kann man ja nachfragen.
Gut, eine Frage kann ich gleich beantworten: die "64" bedeutet "vbInformation" und verleiht der MsgBox ein Ausrufezeichen und wenn in der Systemsteuerung des Rechnern dem Ereignis auch noch ein Klang zugeordnet ist, wird dieser abgespielt.
Die blanke Anweisung 'MsgBox "Fall erledigt."' veranlasst lediglich einen "Blubb".
So, fraglich sind - wenn ich das richtig verstanden habe - nur die Aktionen des "PersChefs", alles zuvor ist in Ordnung.
Also Fragen:
In welche Zelle und was gibt "PersChef" ein? (Du hast "Code" geschrieben, das ist verwirrend. "Code" wird programmiert, eine Zelleingabe besteht zumeist aber aus Zahlen, Worten, irgendwelchen Zeichenfolgen [mal abgesehen von Zellformeln]).
Ich versuche, das noch einmal "nachzubauen" [hatte ich gestern schon gemacht, leider nicht gespeichert], soweit das lt. Deiner Beschreibung nachvollziehbar ist. Den vollen Ablauf des Codes... na mal sehen, was Du an Infos noch nachschiebst.
Viele Grüße. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 13.04.23 12:29 |
| Hallo und danke für Deine Mühe und Erklärung.
Hier mal die nähere Erläuterung, was genau jetzt noch in der eigentlichen Excel-Datei passieren soll:
Eingaben sind in den Zeilen 9 - 20 möglich.
Der Pers.Chef trägt nun z.B. in der ersten Zeile (das wäre also Zeile 9) in Zelle K9 seinen Code ein.
Dadurch wird dann in Zelle P9 automatisch "genehmigt" eingetragen.
Ebenso wird dann in Zelle Q9 automatisch das Tagesdatum eingetragen.
Und genau dann - wenn also das Tagesdatum in Zelle Q9 eingetragen wurde - soll...
1) die Excel-Datei AUTOMATISCH gespeichert werden
2) und danach die Messagebox erscheinen, die man dann mit OK bestätigt.
------------
Nur zur Info:
über den VBA-Code wird nach dem Eintrag des Tagesdatums in Zelle Q9 dann...
1) der Bereich A-E und G-K grau markiert
2) die komplette Zeile gesperrt
Ich hoffe, Du kommst damit klar.
Im Grunde ist das der genaue Ablauf.
Danke und Grüße,
imebro
Beitrag wurde zuletzt am 13.04.23 um 12:31:32 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 15.04.23 08:42 |
| Gut, bevor mein mit Schichtarbeit gefülltes Wochenende beginnt, noch eine Antwort für Dich. Was Du mit "Code" meinst hast Du leider nicht erklärt, aber sei´s drum.
Unabhängig von irgendwelchen Eingaben in irgendwelche Zellen mit den nachfolgenden Ereignissen (Code-Aufrufen) habe ich mich voll und ganz auf die Zeile 20 Deines Arbeitsblattes konzentriert.
In Zelle "O20" wird das Code-Wort "ist ok" eingegeben. Es tritt das Ereignis "Worksheet_Change(ByVal Target As Range)" auf, welches per If-Abfragen prüft und ggf. die nachfolgend gewünschten Aktionen ausführt (wie gesagt, ich habe nur die Zeile 20 betrachtet und den Schutz des Arbeitsblattes beachtet, aber halt ohne Passwort).
Ich hoffe, dass Du damit in von Dir angepasster Form etwas anfangen kannst. VG Hollie
(Wie macht man das nur, einen VB-Code hier so einzufügen, wie Du das in Deiner letzten Nachricht gemacht hast?)
Code siehe Nachricht von 08:53 Uhr
Beitrag wurde zuletzt am 15.04.23 um 08:57:48 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 15.04.23 08:48 |
| (War nur ein Versuch, VB-Code in "schicker" (korrekter) Form zu senden, war aber ein Klatsch ins Wasser )
Beitrag wurde zuletzt am 15.04.23 um 08:51:36 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 15.04.23 08:53 |
| mom, hab noch eine Idee, evtl. so ?
'tritt auf, wenn die Zelle verlassen wird:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ABL As Object '("ABL" = Arbeitsblatt)
Set ABL = ThisWorkbook.Worksheets("Tabelle1")
If Target.Row = 20 Then
If Target.Column = 15 Then
'Zelle "O20" wurde geändert
If Target.Text = "ist ok" Then '(unterstelltes "Codewort" des PersChefs)
ABL.Cells(20, 16) = "genehmigt"
ABL.Cells(20, 17) = Date
ABL.Range("A20:Q20").Locked = True
ThisWorkbook.Save
MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!", 64
End If
End If
End If
End Sub Jepp, so klappt es, man muss nur richtig hinschauen
PS: die "If-Abfragen" können natürlich in einer Zeile, verknüpft mit "and", aufgeführt werden.
Beitrag wurde zuletzt am 15.04.23 um 09:03:19 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 18.04.23 12:19 |
| Hallo und danke für die Mühe...
Leider klappt es nicht.
Ich bekomme es nicht hin, den von mir eingestellten Code so mit Deinem zu verbinden / anzupassen, dass es wie folgt funktioniert:
Nachdem der Pers.Chef seinen Code in Spalte "K" eingegeben hat und dadurch automatisch der Eintrag "genehmigt" in Spalte "P" sowie das Datum in Spalte "Q" erschienen ist, soll...
1) die komplette Zeile mit grauem Hintergrund erscheinen (bzw. die Zellen A-E und G-K, da der Rest schon grau ist)
2) danach die komplette Zeile gesperrt werden (Lock)
3) dann soll die gesamte Datei gespeichert werden
4) und dann soll die Message-Box erscheinen ("Dieser Urlaub ist jetzt nicht mehr veränderbar!")
Vielleicht kannst Du (oder sonst Jemand) ja meinen Code mal so abändern und mit Deinem ergänzen, damit ich den Code dann so nutzen kann für meine Datei.
Hier nochmal mein aktueller Code von "Diese Arbeitsmappe":
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim objCell As Range
Dim loZ As Long
With Worksheets("Eingaben")
For Each objCell In .Range("Q9:Q20")
If IsDate(objCell.Value) Then
loZ = objCell.Row
objCell.EntireRow.Locked = True
objCell.Interior.Color = RGB(221, 221, 221)
.Range(.Cells(loZ, 1), .Cells(loZ, 5)).Interior.Color = RGB( _
221, 221, 221)
.Range(.Cells(loZ, 7), .Cells(loZ, 11)).Interior.Color = RGB( _
221, 221, 221)
ThisWorkbook.Save
MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!"
End If
Next
End With
End Sub Danke und Grüße von
imebro
Beitrag wurde zuletzt am 18.04.23 um 12:21:17 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 18.04.23 18:39 |
| Hallo "imebro",
ich weiß nicht, ob Du meine Antwort richtig gelesen hast. Egal.
Mit diesen "Range"-Begriffen arbeite ich ggf. recht ungern, hab den
Code jetzt noch mal abgeändert (vl. für Dich verständlicher).
Folgendes:
(1) lösche in "Private Sub Workbook_BeforeSave" die zwei Zeilen
- ThisWorkbook.Save
- MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!"
Belasse es hier also so, wie es scheinbar zuvor war.
(2) Das von Dir verwendete Wort "Code" hast Du leider nicht erklärt.
Ich UNTERSTELLE, dass es sich um ein geheimes Wort des "PersCh" handelt,
damit nicht jeder X-Beliebige den Urlaub genehmigen kann.
Das von mir hier verwendete "Code"-Wort lautet "GEHEIM".
Füge jetzt diese Programmzeilen in den Code der Tabelle "Eingaben" ein. Ich unterstelle, dass
es hier die "Private Sub Worksheet_Change(ByVal Target As Range)" noch nicht gibt (prüfe das bitte zuvor!).
'tritt auf, wenn die Zelle mit geändertem Inhalt verlassen wird.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$20" Then
If Range("$O$20").FormulaR1C1 = "GEHEIM" Then
Range("$O$20").FormulaR1C1 = "in Ordnung"
Range("$P$20").FormulaR1C1 = "genehmigt"
Range("$Q$20").FormulaR1C1 = Date
Range("A20:Q20").Locked = True 'Sperren der Zellen in Zeile 20
ActiveSheet.Protect(Password:="XXXXXXX", UserInterfaceOnly:=True) = True
ThisWorkbook.Save ' ruft "Workbook_BeforeSave" auf und graut die _
angegebenen Zellen aus
MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!", 64
End If
End If
End Sub Das Ereignis "Worksheet_Change" tritt auf, wenn der User die Zelle (Range)
mit einem geänderten Inhalt verlässt (Verlassen per Mausklick oder "Enter"-Taste).
Es passiert nun folgendes
- wurde die Zelle "O20" mit einer Änderung verlassen?
- Ja, Zelle "O20" war betroffen (If Target.Address = "$O$20" Then)
- hat der "PersCh" sein Codewort "GEHEIM" eingegeben? (If Range("$O$20").FormulaR1C1 = "GEHEIM" Then)
jetzt kommt eine Spezialität, damit nicht jeder das Codewort lesen kann:
- Ja, in Zelle "O20" steht "GEHEIM" --> ändere Zelle "O20" und trage "in Ordnung" ein
Range("$O$20").FormulaR1C1 = "in Ordnung"
- Das ruft jetzt "Worksheet_Change" erneut auf, ändert aber nichts, weil keine der
nachfolgenden If-Bedingungen (außer Range "O20") erfüllt ist.
- "Worksheet_Change" durchläuft nun nach "Range("$O$20").FormulaR1C1 = "in Ordnung"
die Kommandos
- Range("$P$20").FormulaR1C1 = "genehmigt"
- Range("$Q$20").FormulaR1C1 = Date
- ActiveSheet.Protect(Password:="XXXXXXX", UserInterfaceOnly:=True) = True
---> das ruft an dieser Stelle "Workbook_BeforeSave" auf und graut Deine Zellen aus
- MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!"
FERTIG !!!
Ich hoffe, dass ich mich nun besser (verständlicher) ausdrücken konnte.
VG. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 19.04.23 10:43 |
| Hallo Hollie,
danke für Deine weitere Mühe.
Mit dem Code hattest Du Recht. Der Pers.Chef trägt in das Feld "K9" seinen Mitarbeiter-Code ein. Damit wird der Urlaub dann genehmigt.
Noch ein paar Anmerkungen, zu Deinem Code:
1) es können Urlaube eingetragen werden in den Zeilen 9 bis 20 (nicht nur in Zeile 20)
2) Dein Code-Teil, damit man das Codewort des Pers.Chefs nicht lesen kann, kann entfernt werden, da ich das bereits ohne VBA
umgesetzt hatte.
3) auch der Eintrag "genehmigt" kann entfernt werden, da ich das schon über "Bedingte Formatierung" eingestellt hatte.
Was bei meinem Test eben nicht funktioniert hat (ich hatte Deinen Code von Zeile 20 auf Zeile 9 abgeändert):
1) in Zeile 9 wurde in der Zelle "Q9" nicht das Tagesdatum eingesetzt (das hatte bisher funktioniert).
2) Der Zeilenbereich "A9 - E9" und "G9 - K9" wurde nicht ausgegraut (das hatte bisher funktioniert).
3) Die Zeile 9 wurde nicht gesperrt
4) Die Datei wurde nicht gespeichert
5) Es erschien keine Message-Box
Hier mal 2 Screenshots:
1) Die VBA-Codes der Arbeitsmappe und von "Eingaben":
2) So sieht das Ganze optisch aus, nachdem ich den Code des Pers.Chefs in die Zelle "K9" eingegeben hatte:
Hier mal beide Code-Bereiche, wie sie VORHER aussahen:
>> da hat alles funktioniert, außer der automatischen Speicherung, nachdem das Tagesdatum eingesetzt wurde und der Messagebox.
Danke & Grüße von
imebro
Beitrag wurde zuletzt am 19.04.23 um 10:55:52 editiert. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 19.04.23 14:28 |
| Hallo "imebro",
hm, vermutlich willst Du einen fertigen Code für Deine Excel-Mappe. Ich dachte, Du kannst Dir das, was ich bisher geliefert habe, an Deine Bedürfnisse anpassen.
Nun gut, das eine Bildchen hat mir noch Hinweise geliefert.
Davon ausgehend, dass Deine "bedingten Formatierungen" und die vorhandenen "Methoden" in den Makros bereits alles ausführen, probiere es nun mit diesem Code, den Du in das Tabellenblatt "Eingaben" einfügst (lösche hier aber bitte vorhandene "Private Sub Worksheet_Change", das darf nicht doppelt drin sein):
'tritt auf, wenn die Zelle mit geändertem Inhalt verlassen oder mit "Entf"
' geändert wird.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ZL% 'Steht für die Tabellenzeile, in der eine Änderung erfolgte.
Dim SP% 'Steht für die Tabellenspalte, in der eine Änderung erfogte.
ZL = Target.Row
SP = Target.Column
'Es soll nur war passieren, wenn in Spalte "Q" (numerisch=17) das Datum
' gesetzt wird:
If SP = 17 Then
'Hier könnte man auch schreiben:
'if ZL=9 or ZL=10 or ZL = 11 or .... or ZL=20 then...
'Aber das geht mit "Select Case" wesentlich kürzer. Also:
Select Case ZL
Case 9 To 20 'mache nur was, wenn eine der Zeilen 9...20 betroffen ist!
If ActiveSheet.Cells(ZL, 17) = Date Then
ActiveSheet.Protect(Password:="XXXXXXX", UserInterfaceOnly:=True) = _
True
ThisWorkbook.Save
MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!", 64
End If
End Select
End If
End Sub Hoffe, dass es nun passt. VG. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 19.04.23 15:14 |
| Hallo Hollie,
und wieder mal danke für Deine Mühe.
Ist mir schon echt peinlich - aber hat leider wieder nicht funktioniert.
Genau wie vorher...
Ich habe wieder die Zeile 9 ausgefüllt.
Es wird jedoch in Zelle Q9 wieder kein Datum eingesetzt, nachdem der Code des Pers.Chefs in Zelle K9 eingetragen wurde.
Ebenso wird...
- der Zeilenbereich "A9 - E9" und "G9 - K9" nicht ausgegraut,
- die Zeile nicht gesperrt
- die Datei nicht autom. gespeichert
- die Messagebox nicht angezeigt.
Hier nochmal ein Screenshot der beiden aktuellen Codes (Arbeitsblatt & Eingaben):
Danke & Grüße von
imebro | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 19.04.23 21:43 |
| Naja, es wird jetzt wirklich langsam anstrengend.
Ich will mal so sagen: lt. Deinen Erstangaben (ich zitiere)
1) Mitarbeiter gibt seinen Code ein und speichert die Excel-Datei auf herkömmliche Weise (die Zeile bleibt editierbar)
2) AL gibt seinen Code ein und speichert die Excel-Datei auf herkömmliche Weise (die Zeile bleibt editierbar)
3) Pers.Chefin gibt ihren Code ein. Dadurch...
3a) erscheint in Spalte P der Eintrag "genehmigt" und in Spalte Q das akt. Tagesdatum
3b) Gleichzeitig wird mit dem Eintrag des Tagesdatums diese Zeile gesperrt
3c) Und der Zellbereich "A-E" und "G-K" wird grau formatiert
bin ich nun nicht dafür verantwortlich, dass kein Datum in Spalte "Q" gesetzt wird. Schau einfach mal, ob Dein bisheriger
Code und die "bedingten Formatierungen" noch die alten sind.
Vom Prinzip her (nachdem ich durch die Bilder in Deiner Nachricht von heute, 10:43 Uhr das Ganze etwas besser verstanden habe)
gibt es nur die eine Ergänzung im Code-Teil von "Eingaben", das Ereignis "Private Sub Worksheet_Change(ByVal Target As Range)".
Mehr nicht.
Ich hab das gleich nochmal vereinfacht (das fiel mir vorhin beim Abendessen ein, weshalb ich überhaupt Deine letzte Info hier
heute Abend noch sah). Inhaltlich oder funktionell hat sich dabei überhaupt nichts geändert, es ist nur etwas kürzer/einfacher.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 Then '=Spalte "Q"
Select Case Target.Row
Case 9 To 20 'Zeilen 9...20
If ActiveSheet.Cells(Target.Row, 17) = Date Then
ActiveSheet.Protect(Password:="XXXXXXX", UserInterfaceOnly:=True) = _
True
ThisWorkbook.Save
MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!", 64
End If
End Select
End If
End Sub Und zusammen mit den Code-Schnipseln, die Du in Deinen Beiträgen geliefert hast, funktioniert das in meiner Test.xlsm tadellos.
Die Zellen der Zeile werden ausgegraut, gesperrt, es wird gespeichert und die MsgBox krönt das ganze.
Also nochmals meine Empfehlung: bring Deine Datei auf den alten Stand, bis es nach altem Muster funktioniert und dann
ergänze im Tabellenblatt "Eingaben" den gelieferten Code.
So, jetzt mach ich aber wirklich Feierabend für heute, vG.
PS: eine Frage doch noch, wie genau "zauberst" Du das Datum in die Zeilenspalte "K"? | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 20.04.23 10:41 |
| Hallo Hollie,
wow, danke... wir sind fast am Ziel und sorry, dass ich Dich hier so nerve
Wie ich das Datum in die Zelle "Q" (nicht "K") zaubere, siehst Du im Code weiter unten. Es geht über "Worksheet Calculate()".
Habe es eben erneut versucht und es lief wie folgt:
- nachdem der Pers.Chef in Spalte "O" sein Kennwort eingetragen hat, erscheint in Spalte "P" = "genehmigt" und in Spalte "Q" das Tagesdatum
- weiterhin wird die komplette Zeile ausgegraut (A-E und G-K)
- die Message-Box erscheint und man kann sie mit einem Klick auf "OK" schließen
ABER...
- die Zeile wird nicht gesperrt
- die Datei wird nicht automatisch gespeichert
INFO:
Nach einer manuellen Speicherung ist die Zelle dann gesperrt.
Hier nun nochmal alle aktuellen Codes im Überblick:
1) Code "Dieses Arbeitsblatt":
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim objCell As Range
Dim loZ As Long
With Worksheets("Eingaben")
For Each objCell In .Range("Q9:Q20")
If IsDate(objCell.Value) Then
loZ = objCell.Row
objCell.EntireRow.Locked = True
objCell.Interior.Color = RGB(221, 221, 221)
.Range(.Cells(loZ, 1), .Cells(loZ, 5)).Interior.Color = RGB( _
221, 221, 221)
.Range(.Cells(loZ, 7), .Cells(loZ, 11)).Interior.Color = RGB( _
221, 221, 221)
End If
Next
End With
End Sub
Private Sub Workbook_Open()
Call Worksheets("Eingaben").Protect(Password:="xxxxxxxx", _
UserInterfaceOnly:=True)
End Sub 2) Code "Eingaben":
Private Sub Worksheet_Calculate()
Dim objCell As Range
For Each objCell In Range("O9:O20")
With objCell
If .Text = "OK" Then _
If Not IsDate(.Offset(0, 2).Value) Then .Offset(0, 2).Value = _
Date
End With
Next
End Sub
'tritt auf, wenn die Zelle mit geändertem Inhalt verlassen oder mit "Entf"
' geändert wird.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 Then '=Spalte "Q"
Select Case Target.Row
Case 9 To 20 'Zeilen 9...20
If ActiveSheet.Cells(Target.Row, 17) = Date Then
ActiveSheet.Protect(Password:="xxxxxxxx", UserInterfaceOnly:=True) = _
True
ThisWorkbook.Save
MsgBox "Dieser Urlaub ist jetzt nicht mehr veränderbar!", 64
End If
End Select
End If
End Sub Vielleicht ist es ja nur noch eine Kleinigkeit
Danke & Grüße von
imebro | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: Hollie | Datum: 20.04.23 12:53 |
| Hallo "imebro",
"PersChef" trägt seine Code in Spalte "O" ein?
Am 13.04. schreibst Du Spalte "K". Gut, wahrscheinlich eine Fehlangabe wg. Eile.
Eines noch vorweg, das habe ich eben erst gemerkt. Das Sperren der Zellen pro Zeile. Bei mir
funktioniert Deine Zeile "objCell.EntireRow.Locked = True" in "Workbook_BeforeSave" nicht. Ich habe
deshalb "Worksheet_Change" ergänzt (siehe Codeausschnitt).
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 Then '=Spalte "Q"
Select Case Target.Row
Case 9 To 20 'Zeilen 9...20
If ActiveSheet.Cells(Target.Row, 17) = Date Then
ActiveSheet.Range(Cells(Target.Row, 1), Target.Cells(Row, 17)).Locked _
= True 'neu
ActiveSheet.Protect(Password:="XXXXXXX", UserInterfaceOnly:=True) = _
True
ThisWorkbook.Save
MsgBox "Zeile " & Target.Row & ":" & vbCrLf & "Dieser Urlaub ist" & _
"jetzt nicht mehr veränderbar!", 64
End If
End Select
End If
End Sub
'Die Zeile "objCell.EntireRow.Locked = True" in "Workbook_BeforeSave"
' streichen/deaktivieren. Es ist wirklich schwer, etwas nachzuvollziehen, wenn man nicht alle Details eines Projektes kennt.
In meiner Testdatei hab ich das folgendermaßen nachvollzogen:
als "Code" für die Eingabespalte "K" verwende ich "ok" (ich muss ja den "Code" vom PersCh nicht verschleiern)
die Zellen "O9"..."O20" sind "bedingt formatiert" mit "=Wenn(K9="ok;"ok","")" usw. Zeile fortlfd. angepasst natürlich;
die Zellen "P9"..."P20" sind "bedingt formatiert" mit "=Wenn(O9="ok";"genehmigt","") usw. Zeile fortlfd. angepasst natürlich;
Im Ablauf passiert nun folgendes:
- PersCh gibt Code in "K9" ein (bei mir "ok")
- Zelle "O9" reagiert wegen "=Wenn(K9="ok;"ok","")" und beschreibt sich mit "ok"
- Zelle "P9" reagiert wegen "=Wenn(O9="ok";"genehmigt";"") und beschreibt sich selbst mit "genehmigt"
- jetzt tritt "Worksheet_Calculate" in Aktion und sucht alle 12 Zeilen der Spalte "O" nach einem "ok" ab
(denn hier steht ja "For Each objCell In Range("O9:O20")") und
wird in Zeile 9 (also Zelle "O9") fündig ("If .Text = "ok" Then").
Weil nun hier "If Not IsDate" wahr ist (die Zelle "Q9" ist ja noch leer), wird das Datum eingetragen
(If Not IsDate(.Offset(0, 2).Value) Then .Offset(0, 2) = Date).
(PS: dieses Eintragen ruft "Worksheet_Calculate" erneut auf, ist aber nicht von Belang, da das ergebnislos bleibt.)
- Jetzt tritt (automatisch) das Ereignis "Worksheet_Change" ein und reagiert auf den erfolgten Datums-Eintrag in Zelle "Q9".
"Worksheet_Change" ermittelt Zeile 9 und Spalte 17 ("Q") und fragt mit
"If ActiveSheet.Cells(Target.Row, 17) = Date Then", ob hier ein Datum drinsteht. Ergebnis positiv, ja, es ist so.
- Da positiv geprüft wurde, kommt es nun zu
(1.) "ActiveSheet.Range(Cells(Target.Row, 1), Target.Cells(Row, 17)).Locked = True" (neu: das Zellen-Sperren)
(2.) "ActiveSheet.Protect(Password:="XXXXXXX", UserInterfaceOnly:=True) = True" (Tabellenblatt schützen)
(3.) "ThisWorkbook.Save" (Arbeitsmappe speichern)
- "ThisWorkbook.Save" ruft "Workbook_BeforeSave" auf und hier werden alle Zellen "Q9"..."Q20" auf einen
Eintrag "Datum" geprüft ("For Each objCell In .Range("Q9:Q20")").
Jede Zeile, die in Spalte "Q" einen Datumseintrag hat ("If IsDate(objCell.Value) Then"),
wird nun duch die Anweisung "objCell.Interior.Color = RGB(221..." ausgegraut
(das passiert übrigens auch für Zeilen, die schon mal durch das Genehmigungsverfahren gelaufen sind).
- Nun kehrt die Programmabfolge SPEICHERND nach "Worksheet_Change" zurück, denn es gilt noch die MsgBox-Anweisung zu erledigen.
- Ist das geschafft, läuft "Worksheet_Calculate" weiter ab, denn es wurden ja noch nicht alle Zeilen auf Datumseintrag
geprüft. Das wäre für Deine Anwendung eigentlich nicht weiter notwendig,
da aber überall "For Each objCell In Range("O9:O20")" (also "für alle Zellen "O9" bis "O20"") angewiesen ist,
muss also auch alles (hier ohne weitere Ergebnisse) abgearbeitet werden.
Was ich nun noch mehr für Dich tun könnte, das weiß ich nicht. Bei mir läuft es so ab, wie Du es gewünscht hast
(natürlich alles nur auf "PersCh" bezogen). VG.
PS: sollte es immer noch nicht laufen, dann besser per Mail, denn wir müssen diesen Beitrag ja nicht noch weiter aufblasen. | |
Re: VBA-Code ergänzen durch autom. Speicherung und Messagebox | | | Autor: imebro | Datum: 20.04.23 14:29 |
| Hallo Hollie,
DANKESEHR!!! Klappt alles!
Ich hatte mich übrigens selbst überlistet. Natürlich war es die Spalte "K" und nicht "O". Das Ergebnis der Eingabe in "K" sieht man dann halt in der Spalte "O". Daher alles bestens...
Es ist schon verrückt.
Im Grunde funktionierte ja auch mit meiner Ursprungsdatei alles schon... eben bis auf das AUTOMATISCHE Speichern ganz am Ende und die Messagebox. Aber es ist wichtig, es so zu haben, wie es jetzt ist, denn es tauchten immer wieder Fehler auf. Und nun werden diese vermieden.
Im Grunde habe ich jetzt wirklich einen perfekten "digitalen Urlaubsantrag", der noch mehr Funktionen hat, als ich bisher geschrieben hatte. Vielleicht interessiert es Dich ja...
Damit man die Einträge in den Zellen nicht sehen kann, habe ich diese Zellen mit "Benutzerdefiniert" und ";;;" formatiert.
Die Mitarbeiter-Codes sind alle in einer zusätzlichen Excel-Datei hinterlegt. Das Programm sucht also, ob es
1) diesen MA überhaupt gibt
2) ob der Code auch stimmt
Ansonsten gibt es eine Fehlermeldung (direkt im Feld... also der Zelle).
Ebenso kann in einem Feld noch Sonderurlaub eingetragen werden, der dann automatisch nicht als Urlaub gezählt wird.
Und... es sind alle Feiertage in der Datei hinterlegt sowie die Wochenenden. Und die Feiertage aktualisieren sich automatisch, indem man in einer Zelle in der Hauptansicht das Jahr einträgt.
Wenn man also Urlaub einträgt über ein Wochenende hinaus und wenn dann auch noch ein Feiertag innerhalb des Urlaubes ist, dann wird dieser und das Wochenende automatisch nicht als Urlaubstag angerechnet.
Tja, das wär´s.
Vielen Dank nochmal für Deine ausdauernde Hilfe
Schöne Grüße von
imebro
Beitrag wurde zuletzt am 20.04.23 um 14:30:55 editiert. | |
| Sie sind nicht angemeldet! Um auf diesen Beitrag zu antworten oder neue Beiträge schreiben zu können, müssen Sie sich zunächst anmelden.
Einloggen | Neu registrieren |
|
|
sevISDN 1.0
Überwachung aller eingehender Anrufe!
Die DLL erkennt alle über die CAPI-Schnittstelle eingehenden Anrufe und teilt Ihnen sogar mit, aus welchem Ortsbereich der Anruf stammt. Weitere Highlights: Online-Rufident, Erkennung der Anrufbehandlung u.v.m. Weitere InfosTipp des Monats sevGraph (VB/VBA)
Grafische Auswertungen
Präsentieren Sie Ihre Daten mit wenig Aufwand in grafischer Form. sevGraph unterstützt hierbei Balken-, Linien- und Stapel-Diagramme (Stacked Bars), sowie 2D- und 3D-Tortendiagramme und arbeitet vollständig datenbankunabhängig! Weitere Infos
|