Office: (Office 2016) Laufzeitfehler 13 & Blattschutz (VBA Excel 2016)

Helfe beim Thema Laufzeitfehler 13 & Blattschutz (VBA Excel 2016) in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo zusammen, ich bin leider was das Thema VBA angeht noch ein ganz blutiger Anfänger und suche deshalb eure Hilfe. :( Ich habe eine Tabelle... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Royo_Drizz, 24. Juli 2020.

  1. Royo_Drizz Neuer User

    Laufzeitfehler 13 & Blattschutz (VBA Excel 2016)


    Hallo zusammen,

    ich bin leider was das Thema VBA angeht noch ein ganz blutiger Anfänger und suche deshalb eure Hilfe. Laufzeitfehler 13 & Blattschutz (VBA Excel 2016) :(

    Ich habe eine Tabelle gebaut in der ein paar Formel stehen. Über VBA habe ich nun noch folgendes hinzugefügt:

    wenn in Spalte A eine Eingabe erfolgt wird in Spalte C Datum & Uhrzeit ausgegeben und in Spalte D der User (Code folgt unten)

    Nun habe ich allerdings das Problem wenn mehrere (bis zu 200) verschiedene Daten zeitgleich via Copy&Paste eingefügt werden,
    zwar diese Daten in Spalte angezeigt werden aber in Spalte C & D nichts ausgegeben wird.
    Statt dessen erhalte ich die Meldung: "Laufzeitfehler 13 Typ unverträglich" Laufzeitfehler 13 & Blattschutz (VBA Excel 2016) :confused:

    Des Weitern möchte ich (da die Datei von mehreren Kollegen bearbeitet werden soll)
    das die Spalten in denen Datum & User ausgeben werden sollen schreibgeschützt sind.

    Ich hoffe ihr könnt mir hier weiterhelfen.

    Hier noch der Code den ich derzeit im VBA stehen habe:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A1048576")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then
    Target.Offset(0, 3).ClearContents
    Target.Offset(0, 2).ClearContents
    Target.Offset(0, 1).ClearContents
    Else:
    Target.Offset(0, 2).Value = CDate(Format(Now, "dd.mm.yy hh:mm"))
    Target.Offset(0, 3).Value = Environ("Username")
    End If
    End Sub


    Danke vorab für euer Feedback und eure Hilfe.

    Liebe Grüße Gerd
     
    Royo_Drizz, 24. Juli 2020
    #1
  2. fette Elfe Erfahrener User
    Moin Gerd

    wenn Du mit Copy/Paste mehrere Datensätze gleichzeitig einfügen möchtest, brauchst Du eine Schleife durch alle Zellen Deiner Auswahl.
    Für den Blattschutz must Du natürlich die Zellen formatieren >> "Zellen formatieren - Schutz". Den Rest macht dann das Makro.

    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim oZelle As Object
    
    On Error GoTo Errorhandler              ' wichtig damit Ereignisse auch bei Fehler wieder eingeschaltet werden
        Application.EnableEvents = False    ' Ereignisse ausschalten
        Me.Protect Password:="TestPW", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
                                            ' das setzen des Passwortes (Password:="TestPW",) kann auf Wunsch weggelassen werden
                                            ' das Passwort ("TestPW") kann selbstverständlich nach Belieben angepasst werden
                                            ' "UserInterfaceOnly:=True" ist wichtig, damit nur Benutzer vom Blattschutz betroffen sind
                                            ' und Makros trotzdem noch frei arbeiten können
    
        For Each oZelle In Target           ' Schleife durch jede Zelle der Auswahl
            If oZelle.Column = 1 Then       ' Prüfung ob aktuelle Zelle in Spalte "A" ist
                If oZelle = "" Then
                    oZelle.Offset(0, 1).ClearContents
                    oZelle.Offset(0, 2).ClearContents
                    oZelle.Offset(0, 3).ClearContents
                Else:
                    oZelle.Offset(0, 2).Value = CDate(Format(Now, "dd.mm.yy hh:mm"))
                    oZelle.Offset(0, 3).Value = Environ("Username")
                End If
            End If
        Next
        
    Errorhandler:
        Application.EnableEvents = True     ' Ereignisse wieder einschalten => sehr wichtig
    
    End Sub
     
    fette Elfe, 24. Juli 2020
    #2
  3. Beverly
    Beverly Erfahrener User
    Hi Gerd,

    Achims Code ist nicht ganz korrekt:

    1. Application.EnableEvents benötigt man hier nicht, da keine Zelle mehrfach angesprochen wird
    2. der Blattschutz wird zu Beginn des Codes gesetzt - das ist Quatsch, denn er muss zu Beginn aufgehoben und am Ende wieder gesetzt werden
    3. wenn man Zellen anspricht, sollte/kann man sie als Range definieren und nicht als Object. Object ist der Variablentyp für jedes Objekt und das belegt unnötig Speicherkapazität, weil es umfassender definiert ist. Außerdem muss Excel intern erst "suchen", um was für ein Objekt es sich handelt - und das braucht Zeit. Object verwendet man für solche Objekte, für die es keinen definierten Variablentyp gibt

    Außerdem kann man den Code noch etwas vereinfachen. Meiner würde so aussehen:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngZelle As Range
        If Intersect(Target.Cells(1), Columns(1)) Is Nothing Then Exit Sub
        ' Blattschutz aufheben (eventuell mit Passwort)
        ActiveSheet.Unprotect ' anstelle ActiveSheet kann auch Me verwendet werden
        ' Schleife über alle markierten/eingefügten Zellen
        For Each rngZelle In Target
            ' laufende Zelle liegt in Spalte A
            If Not Intersect(rngZelle, Columns(1)) Is Nothing Then
                ' laufende Zelle wird gelöscht
                If rngZelle = "" Then
                    ' Zellen B:D gemeinsam leeren
                    Range(rngZelle.Offset(0, 1), rngZelle.Offset(0, 3)).ClearContents
                Else
                    ' Spalte C Datum+Uhrzeit einfügen
                    rngZelle.Offset(0, 2).Value = CDate(Format(Now, "dd.mm.yy hh:mm"))
                    ' Spalte D Benutzername einfügen
                    rngZelle.Offset(0, 3).Value = Environ("Username")
                End If
            End If
        Next rngZelle
        ' Blattschutz setzen (eventuell mit Passwort und speziellen Einstellungen)
        ActiveSheet.Protect
    End Sub
    
    
    Bis später,
    Karin


    , außerdem kann man den Code vereinfachen
     
    Beverly, 25. Juli 2020
    #3
  4. fette Elfe Erfahrener User

    Laufzeitfehler 13 & Blattschutz (VBA Excel 2016)

    Moin Karin :)

    Da würde ich Dir nicht widersprechen, bin leider "eingerostet" und besser gehts immer. ;)


    Ich habe die Schleife zuerst durchlaufen lassen ohne die Events abzuschalten. Das hat nicht funktioniert. Im Einzelschrittmodus habe ich dann gesehen, dass jede Zellwertänderung durch das Makro das Change-Ereignis auslöst (was ja eigentlich auch klar ist), und deshalb 1. unnötig viele Durchläufe stattfinden, und 2. sich das ganze irgendwo verhaspelt.
    Deshalb halte ich das Abschalten der Ereignisse für sinnvoll, lasse mich aber gerne eines besseren belehren.


    Da hast Du vollkommen recht. Vor allem für den Fall das ein Blattschutz bereits händisch gesetzt wurde kann das sogar zu Problemen führen. Zum Beispiel jemand setzt händisch einen Blattschutz und möchte verhindern das bestimmte Zellen/Spalten von Usern ausgewählt werden. Wenn man dann per Copy/Paste etwas einfügt und diese gesperrten Zellen/Spalten in der Range liegen (auch wenn das nicht vorkommen soll), dann läuft das Makro vor die Wand.


    Jepp, da wären wir wieder beim Thema "eingerostet".
     
    fette Elfe, 25. Juli 2020
    #4
  5. Beverly
    Beverly Erfahrener User
    Das ist schon richtig, dass das Change-Ereignis immer wieder ausgelöst wird, aber im konkreten Fall (einfach mal testen) springt der Code nur zur Codezeile

    Code:
    If Intersect(Target.Cells(1), Columns(1)) Is Nothing Then Exit Sub
    und sofort - da die Sub verlassen wird - wieder zurück zur zuvor ausgeführten Codezeile (bzw. zur nächsten), sodass im eigentlichen Sinn kein erneuter Schleifendurchlauf stattfindet. Das Ausschalten der Eingabereaktion ist dagegen zwingend erforderlich, wenn dieselbe Zelle mehrfach angesprochen wird und man eine erneute Änderung und somit Endlosschleife verhindern will/muss.

    Bis später,
    Karin
     
    Beverly, 25. Juli 2020
    #5
  6. Royo_Drizz Neuer User
    Hallo Karin & Achim

    vorab sry für die verspätete Antwort.
    War leider ;) im Urlaub.Laufzeitfehler 13 & Blattschutz (VBA Excel 2016) :cool:

    Danke für eure schnelle Hilfe.
    Dank eurer Hinweise und Anmerkungen klappt bei meiner Datei nun alles. :D

    1000 Dank dafür.

    Für die Leser die es interessiert mein Code schon nun so aus:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngZelle As Range
    If Intersect(Target.Cells(1), Columns(1)) Is Nothing Then Exit Sub
    For Each rngZelle In Target
    If Not Intersect(rngZelle, Columns(1)) Is Nothing Then
    If rngZelle = "" Then
    Range(rngZelle.Offset(0, 1), rngZelle.Offset(0, 3)).ClearContents
    Else
    rngZelle.Offset(0, 2).Value = CDate(Format(Now, "dd.mm.yy hh:mm"))
    End If
    End If
    Next rngZelle
    End Sub


    Wie gesagt nochmal 1000 Dank für eure Hilfe.

    Liebe Grüße Gerd
     
    Royo_Drizz, 11. August 2020
    #6
Thema:

Laufzeitfehler 13 & Blattschutz (VBA Excel 2016)

Die Seite wird geladen...
  1. Laufzeitfehler 13 & Blattschutz (VBA Excel 2016) - Similar Threads - Laufzeitfehler Blattschutz VBA

  2. Laufzeitfehler '13': Typen unverträglich

    in Microsoft Excel Hilfe
    Laufzeitfehler '13': Typen unverträglich: Hallo, bräuchte bitte noch einmal Eure Hilfe. Möchte mit einem Makro Zeilen oder Spalten ausblenden. wenn ich Rows("15:22").Select eingebe funktioniert es. Code: Sub Makro3()...
  3. WebScraping-Laufzeitfehler 91: Objekvariable nicht festgelegt

    in Microsoft Excel Hilfe
    WebScraping-Laufzeitfehler 91: Objekvariable nicht festgelegt: Hi zusammen, ich erhalte die obergenannte Fehlermeldung bei meinem Code, welcher mir die Tabelle aus dem Link: https://data.snb.ch/en/topics/uvo#!/...T5,HYP,KON,UKR) ziehen soll. Ich hoffe mir...
  4. Laufzeitfehler '-2147221040(800401d0)' Die Methode 'Copy'...

    in Microsoft Excel Hilfe
    Laufzeitfehler '-2147221040(800401d0)' Die Methode 'Copy'...: Hallo Leute, ich habe schon länger ein Problem in einem sehr großen VBA-Makro beim kopieren von Diagrammen. In der Zeile "objDia.Copy" kommt sporadisch die Fehlermeldung: Laufzeitfehler...
  5. Laufzeitfehler '9' Index außerhalb des gültigen Bereichs

    in Microsoft Excel Hilfe
    Laufzeitfehler '9' Index außerhalb des gültigen Bereichs: Ich habe ein Makro aufgezeichnet und es läuft auch so wie es soll. Das Problem ist nur dass ich das Makro in einer Arbeitsmappe aufgezeichnet habe die sich XXX.csv nennt. Das darin befindliche...
  6. Laufzeitfehler 91 Objektvarable oder WithBlock Variable nicht festgelegt

    in Microsoft Access Hilfe
    Laufzeitfehler 91 Objektvarable oder WithBlock Variable nicht festgelegt: Hallo ich bin neu hier und habe für mein Problem keine Lösung in den vorhandenen Beiträgen in diesem Foren gefunden. Auch muss ich zugeben, dass ich in VBA neu unterwegs bin. Nichts destotrotz...
  7. Mail aus Access. Laufzeitfehler 287

    in Microsoft Access Hilfe
    Mail aus Access. Laufzeitfehler 287: Hallo, habe seither mit Access 2007 gearbeitet und heute auf Access 2013 umgestellt. In meiner DB hatte ich eine Funktion, um direkt aus Access eine Mail zu erzeugen: Code: Dim Applikation As...
  8. Laufzeitfehler 381

    in Microsoft Excel Hilfe
    Laufzeitfehler 381: Hallo zusammen, ich habe eine Eingabemaske mit VBA erstellt. diese hat auch bisher funktioniert. nun bekomme ich plötzlich folgenden fehler Laufzeitfehler 381: Eigenschaft List konnte nicht...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden