Office: LFZ 9

moses-ms · 13. Oktober 2015

Hallo liebe Gemeinde,
ich schreibe seit langem mal wieder ein VBA Script. Es soll für uns intern ein abgeschafftes DispoTool ersetzen, soviel nur am Rande. Da das Script natürlich all unsere Wünsche erfüllen soll (auch meine :-)) versuche ich alles umzusetzen. Folgendes Probleme habe ich, ich füge mittels VBA Code direkt Code in die Tabelle ein, da alle eingaben in Großbuchstaben geändert werden sollen und mittels Doppelklick ein Kommentarfenster geöffnet werden soll.
Folgenden Code verwende ich dafür:
Code:

Sub BigLetter()
Dim VBC As Object
Dim LineNr As Integer
Dim WKS_BigLetter

'WKS_BigLetter = ActiveWorkbook.ActiveSheet.Name
WKS_BigLetter = "Dispo_2015"

With ThisWorkbook.VBProject.VBComponents(Worksheets(WKS_BigLetter).CodeName).CodeModule
LineNr = .CreateEventProc("Change", "Worksheet")
.InsertLines LineNr + 1, "If Target.Cells.Count > 1 Then Exit Sub"
.InsertLines LineNr + 2, "If Not Target.Value = """" Then"
.InsertLines LineNr + 3, "Target.Value = UCase(Left(Target.Value, 3)) & Mid(LCase(Target.Value), 4, 999)"
.InsertLines LineNr + 4, "End If"
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Der Code ist nicht das eigentliche Problem :-/ sondern das Einfügen in den Tabellencode. Führe ich das Makro aus erscheint der LFZ 9. Wechsel ich dann in den Debugmodus wird die Zeile "ThisWorkbook.VBProject.VBComponents(Worksheets(WKS_BigLetter).CodeName).CodeModu le" markiert. Dann breche ich das Script mit "Zurücksetzen" ab und lasse den VBA Editor geöffnet. Führe ich das Script DANN erneut aus, funktioniert alles ohne Probleme.*confused.gif*

Wo ist mein Fehler der durch das Öffnen des VBA Editors behoben wird?
Dim WKS_BigLetter as String ohne Erfolg, auch das füllen von WKS_BigLetter mit einem festem Wert brachte keinen Erfolg.

:)

EarlFred · 15. Oktober 2015

Hallo,

das hohe Ziel

Da das Script natürlich all unsere Wünsche erfüllen soll [...]
alle eingaben in Großbuchstaben geändert werden sollen [...]
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

wird schon in der ersten Codezeile verfehlt:
Code:

If Target.Cells.Count > 1 Then Exit Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Was, wenn mal mehr als eine Zelle (z. B. per Copy&Paste) geändert wird?
Zudem sollten die Ereignisse deaktiviert werden, solange in dem Change-Ereignis Änderungen verursacht werden.

Aber zur eigentlichen Frage: Ich kann das Problem nicht nachvollziehen - der Code läuft bei mir an sich.
Aber ich stelle mir immer die Frage, warum man zum einen Änderungen des VBA-Projektes zulassen muss (Sicherheitslücke!) und zum anderen, warum es überhaupt für notwendig erachtet wird, Code zu Laufzeit zu ändern.
Wenn Du Deine Gedanken und Dein Vorhaben schilderst, findet sich vielleicht ein ganz anderer Weg.

Grüße
EarlFred

Beverly · 15. Oktober 2015

Hi,

ich konnte dein Problem nachvollziehen, obwohl mir die Ursache unklar ist. Nach Schließen von Excel und Neustart trat der Fehler nicht mehr auf.

Weshalb benutzt du nicht stattdessen ein Vorlage-Tabellebnlatt, bei dem der Code bereits enthalten ist und welches einfach nur kopiert werden muss? Außerdem kann man den Code als solchen auch für alle Tabellenblätter zugängig machen, indem man nicht das Change-Ereignis des Tabellenblattes sondern das SheetChange-Ereignis unter DieseArbeitsmappe benutzt.

Luschi · 15. Oktober 2015

Hallo moses-ms,

ich mach das immer so: Code:

Sub BigLetter()
Dim ws As Worksheet
Dim VBC As Object
Dim LineNr As Integer
Dim WKS_BigLetter As String

WKS_BigLetter = "Dispo_2015"
Set ws = ThisWorkbook.Worksheets(WKS_BigLetter)

Set VBC = Application.VBE.ActiveVBProject.VBComponents(ws.CodeName)

With VBC.CodeModule
LineNr = .CreateEventProc("Change", "Worksheet")
.InsertLines LineNr + 1, "If Target.Cells.Count > 1 Then Exit Sub"
.InsertLines LineNr + 2, "If Not Target.Value = """" Then"
.InsertLines LineNr + 3, "Target.Value = UCase(Left(Target.Value, 3)) & _
Mid(LCase(Target.Value), 4, 999)"
.InsertLines LineNr + 4, "End If"
End With

Set VBC = Nothing
Set ws = Nothing

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Gruß von Luschi
aus klein-Paris

moses-ms · 16. Oktober 2015

Danke erst einmal für die Antworten bzw. Lösungsvorschläge, anbei meine Antworten dazu:

EarlFred:
Ich Frage über eine InPutBox das Jahr ab und lege darauhin ein neues WorkSheet an. Füge den aktuellen Stand der MA ein. Lege dann einen Zeitstrahl mit dem Tagesdatum und darüber mit der KW an. Da sich dabei doch das eine oder andere ändert, lege ich das Blatt neu an. Dein Hinweis zu Copy/Paste ist OK aber es treten keine Probleme dabei auf. Wenn dann müssten die Daten "extern" eingegeben werden und dann mit Copy/Paste übertragen werden. Das wäre aber definitiv doppelte Arbeit.
Der Code ist auch nicht das Problem, nur bei der Anlage erscheint der Fehler und dann auch nur wenn der VBE geschlossen ist. Als Workaround habe ich einfach ein paar Jahre (WorkSheets) angelegt. Grund des Codes ist das die Eingabe in den Zellen meisten in Kleinbuchstaben erfolgt aber schöner in Großbuchstaben aussieht. Und ja ich weiß das ist eigentlich kein Grund.

Beverly:
Das kann ich leider nicht bestätigen. Bei mir tritt der Fehler auch nach Neustart von Excel auf. *bawling
Der Code in SheetChange-Ereignis unter DieseArbeitsmappe läuft zwar aber das Einfügen erzeugt das gleiche Problem *bawling

Luschi:
Auch Dein Code erzeugt den gleichen Fehler

Als Hinweis möchte ich noch sagen, dass der Blattschutz aktiviert ist und ein Teil des Fensters fixiert ist. Aber wenn alles deaktiviert wird wird der Fehler auch erzeugt.

Aber bei der weiteren Arbeit habe ich noch einen Fehler festgestellt. Nachdem ich den Code "manuell" eingefügt habe, stellte ich fest das eine Hinweismeldung von MS wohl falsch ist. Wenn ein ExcelSheet freigegeben wird, können keine Makros bearbeitet werden soweit so gut. Da ich aber die Bearbeitung nur für bestimmte Bereiche freigegeben habe und über Makro z. B. das Wochenende im Zeitstrahl ein und ausblenden will muss ich den Blattschutz aufheben, das funktioniert leider nicht wenn die Datei freigegeben ist. Die Freigabe ist aber sinnvoll, da darüber automatisch jegliche Änderung geloggt werden und das war/ist ein "must have" im Tool.
Also habe ich folgenden Code im Bereich "SheetChange-Ereignis unter DieseArbeitsmappe" eingefügt:
Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ErsteFreieZeile As Long
If Target.Count > 1 Then Exit Sub
If Sh.Name = "Protokoll" Then Exit Sub
If Intersect(Target, Sh.Range("C18:ND58")) Is Nothing Then Exit Sub
With Sheets("Protokoll")
ErsteFreieZeile = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(ErsteFreieZeile, 1) = Sh.Name
.Cells(ErsteFreieZeile, 2) = Target.Address(0, 0)
.Cells(ErsteFreieZeile, 3) = Target.Value
.Cells(ErsteFreieZeile, 4) = Date
.Cells(ErsteFreieZeile, 5) = Time
.Cells(ErsteFreieZeile, 6) = Environ("username")
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Und hier ist das größere Problem, das Loggen und das Ändern in Großbuchstaben funktionieren zusammen nicht. Hier wird immer der Fehler "Laufzeitfehler '-2147417848(80010108)': Die Methode 'Range' für das Objekt '_Worksheet' ist fehlgeschlagen". Ich kann mir auch denken warum, da die Änderung des Zellinhaltes über VBA im Hintergrund erfolgt kann das nicht "sauber" protokolliert werden. Sobald ich nur ein "Modul" aktiviere treten keine Probleme auf.

Ist es nicht möglich beide Code zu "verheiraten"? Ich habe es nicht geschafft.

EarlFred · 16. Oktober 2015

Hallo TE,

Code:

Dein Hinweis zu Copy/Paste ist OK aber es treten keine Probleme dabei auf.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

es mag keinen Fehler erzeugen. Das "Problem" ist aber, dass das gewollte, nämlich "Herstellung von Großschreibung" schlicht nicht ausgeführt wird.

Der Hinweis auf C&P ist nur ein Beispiel, daher der Zusatz auf "z. B.". Aber mach, wie Du denkst.

Der Hinweis, Ereignisse auszuschalten, wenn Du per Code Wert änderst, ist offenbar ungelesen an Dir vorbeigegangen. Der Hinweis hatte schon einen Sinn.

Ich erkenne nach wie vor keine Grund, Code zur Laufzeit zu erzeugen. Das erachte ich immer als die schlechteste Variante.

Code:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ErsteFreieZeile As Long
Dim c As Range

On Error GoTo errExit

If Sh.Name = "Protokoll" Then Exit Sub

'''''''''''''''''''''''''''''''''If Target.Count > 1 Then Exit Sub

If Intersect(Target, Sh.Range("C18:Z58")) Is Nothing Then Exit Sub

With Sheets("Protokoll")
Application.EnableEvents = False

For Each c In Intersect(Target, Sh.Range("C18:ND58"))
ErsteFreieZeile = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(ErsteFreieZeile, 1) = Sh.Name
.Cells(ErsteFreieZeile, 2) = c.Address(0, 0)
.Cells(ErsteFreieZeile, 3) = c.Value
.Cells(ErsteFreieZeile, 4) = Date
.Cells(ErsteFreieZeile, 5) = Time
.Cells(ErsteFreieZeile, 6) = Environ("username")
Next c
End With

errExit:
Application.EnableEvents = True
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Grüße
EarlFred

Beverly · 12. Januar 2021

Hi,

von moses-ms

Beverly:
....
Der Code in SheetChange-Ereignis unter DieseArbeitsmappe läuft zwar aber das Einfügen erzeugt das gleiche Problem
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Weshalb musst du den Code denn überhaupt erst mit VBA schreiben? Wenn er bereits von Beginn an unter DieseArbeitsmappe steht und es nur darum geht neue Tabellen zu erstellen, dann wirkt er doch auch in den neu erstellten Tabellen.

moses-ms · 21. Oktober 2015

Hallo,

endlich habe ich wieder Zeit mich um mein "Problem" zu kümmern und "JA" warum erst schreiben wenn der Code schon im SheetChange Ereignis stehen kann. DANKE für den Hinweis und lieber EarlFred Deinen Hinweis habe ich wirklich überlesen

EarlFred · 21. Oktober 2015

Hallo TE,

Hat einer eine Idee, wie ich das ändern in Großbuchstaben mit Log hinbekomme
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

wenn der Code aus Beitrag #6, ergänzt um c.Value = ucase(c.value) nicht das tut, was er soll: Nein.

Grüße
EarlFred

moses-ms · 21. Oktober 2015

Moin,

so in der Art hatte ich es schon versucht, leider ohne Erfolg.

Danke noch einmal für die Hinweise!