Geschützes Tabellenblatt Hintergrundfarbe ändern

Question

Hallo

ich möchte gerne in einem Formular wenn ggf. nicht eingetragen werden muss die Zelle grau bzw anders farbig hinterlegen so das jeder auf anhieb sieht das da nichts ausgefüllt werden muss.

Ich habe das mit der "Makro aufzeichnen" - Funktion erstellt und dann das Tabellenblatt gesperrt. Nicht aber die Zellen die betroffen sind.

     With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

Starte ich jetzt das Makro bekomme ich Laufzeitfehler 1004 und diese Zeile ist gelb markiert:

.Pattern = xlSolid

kann mir jemand bitte sagen was ich falsch mache, denn es wäre schon wichtig, dass das Tabelleblatt vor zugriff geschützt ist.

Dank euch!

Answer 1

Hallo,

wenn du das Tabellenblatt mit dem Standardeistellungen geschützt hast (gesperrte Zellen auswählen, nicht gesperrte Zellen auswählen), dann hast die Möglichkeit zur Formatierung von Zellen gesperrt. Daher die Fehlermeldung.

Am besten die Zellen, die du einfärben willst vor der Aktivierung des Blattschutzes färben.

Oder du hebst im Makro den Blattschutz auf:

Sub Farbe()

ActiveSheet.Unprotect

 With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

ActiveSheet.Protect

End Sub

Falls du den Blattschutz mit einem Passwort versehen hast, dann ergänze das Passwort (hier im Beispiel 123) so:

ActiveSheet.Unprotect "123"

..

ActiveSheet.Protect "123"

Gruß

M.O.

Answer 2

heißt aber das ich am besten VBA auch ein Passwort setze damit keiner über den Editor das Auslesen kann oder?

Wie sieht es denn aus wenn ich mehrere Tabellenblätter aktiviere

Sheets(Array("FormularDeu", "FormularEng", "FormularBlank")).Select
    Sheets("FormularDeu").Activate
    Range("B11:G12").Select

wie setze ich dann "unprotect" um?

Danke

Answer 3

Hallo,

wenn du Angst hast, dass jemand das Passwort ausliest, dann schütze das VBA-Project mit einem Passwort. Der Schutz dürfte für die meisten Nutzer ausreichen.

Wenn du mehrere Blätter sperren / entsperren willst, musst du jedes Blatt einzeln ansprechen.

Beispiel 1 (unterschiedliche Bearbeitung der einzelnen Arbeitsblätter):

Sub Schutz1()

Dim arrTabellen As Variant
Dim z As Integer

'Array mit Tabellenblättern definieren
arrTabellen = Array("FormularDeu", "FormularEng", "FormularBlank")

'Blattschutz der einzelnen Tabellenblätter aufheben
For z = LBound(arrTabellen) To UBound(arrTabellen)
 Worksheets(arrTabellen(z)).Unprotect "123"
Next z

'Nun einzelne Tabellenblätter bearbeiten

With Worksheets("FormularDeu").Range("B11:G12").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
End With

With Worksheets("FormularEng")
  .Range("A5") = "Hello"
End With

With Worksheets("FormularBlank")
  .Range("B7") = Date
End With

'Blattschutz der einzelnen Tabellenblätter wieder einrichten
For z = LBound(arrTabellen) To UBound(arrTabellen)
 Worksheets(arrTabellen(z)).Protect "123"
Next z

End Sub

Beispiel 2 (gleiche Bearbeitung aller Arbeitsblätter):

Sub Schutz2()

Dim arrTabellen As Variant
Dim z As Integer

'Array mit Tabellenblättern definieren
arrTabellen = Array("FormularDeu", "FormularEng", "FormularBlank")

'Arbeitsblätter durchlaufen
For z = LBound(arrTabellen) To UBound(arrTabellen)
  With Worksheets(arrTabellen(z))
    'Blattschutz aufheben
    .Unprotect "123"
    'Bereich im Arbeitsblatt einfärben
      With .Range("B11:G12").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
      End With
    'Blattschutz wieder setzen
    .Protect "123"
  End With
Next z

End Sub

Gruß

M.O.

Answer 4

Hallo, erstmal vielen Dank!

Ich bin gerade dabei das ganze umzusetzen und es klapp auch super mit dem Code den du geschrieben hast.

Jetzt wollte ich aber noch andere Bereiche zusätzlich farbig markieren

die Bereiche habe ich ebenfalls mit With Range("H17:M18").Interior eingefügt, zuerst habe ich vergessen  With Worksheets(arrTabellen(z))
einzufügen und er hat das active worksheet auf der die Schaltfläche liegt farbig gemacht. nachdem ich das jetzt geändert habe kommt die Debugger-Meldung und er markiert .Pattern = xlSolid

Kann mir bitte jemand sagen wo mein Fehler ist und wenn möglich was .Pattern = xlSolid bedeutet?

Sub Hintergrundfarbe()

Dim arrTabellen As Variant
Dim z As Integer

'Array mit Tabellenblättern definieren
arrTabellen = Array("FormularDEU", "FormularENG", "FormularBLANK")

'Arbeitsblätter durchlaufen
For z = LBound(arrTabellen) To UBound(arrTabellen)
  With Worksheets(arrTabellen(z))
    'Blattschutz aufheben
    .Unprotect "123"
    'Bereich im Arbeitsblatt einfärben
      With .Range("B11:G12").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
        With Range("H17:M18").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    End With

    Sheets("Makroblatt").Select
   
    'Blattschutz wieder setzen
    .Protect "123"
 
  End With

Next z

End Sub

Ebenfalls würde mich interessieren was Next z bedeutet da er mir bevor ich das End With hinzugefügt habe hier einen Fehler gezeigt hat.

Dank euch!

Answer 5

Hallo,

du hast bei deinem Makro die End With etwas falsch gesetzt (was hier nicht so schlimm ist) und bei dem zweiten Range [Range("H17:M18")] den Punkt vor dem Range vergessen. Daher wird auf das aktuelle Tabellenblatt zurückgegriffen. Ist das geschützt, kommt es zur Fehlermeldung.

Du schreibst auch Sheets("Makroblatt").Select bevor du den Blattschutz wieder setzt. Wenn du das Blatt nach Ende des Makros aktivieren willst, würde ich das auch ans Ende setzen.

Das Next z beendet die Schleife zum Durchlaufen der einzelnen Tabellenblätter. Alles was zwischen For z = LBound(arrTabellen) To UBound(arrTabellen) und next z steht, wird auf jedem Tabellenblatt ausgeführt.

Mit .Pattern = xlSolid legst du fest, dass kein Muster hinterlegt wird.

Mit .Pattern = xlVertical fügst du z.B. ein vertikal gestreiftes Muster ein.

Hier mal dein Makro bearbeitet:

Sub Hintergrundfarbe()

Dim arrTabellen As Variant
Dim z As Integer

'Array mit Tabellenblättern definieren
arrTabellen = Array("FormularDEU", "FormularENG", "FormularBLANK")

'Arbeitsblätter durchlaufen
For z = LBound(arrTabellen) To UBound(arrTabellen)
  With Worksheets(arrTabellen(z))
    'Blattschutz aufheben
    .Unprotect "123"
    'Bereich im Arbeitsblatt einfärben
      With .Range("B11:G12").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
      End With
      
      With .Range("H17:M18").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
     End With
 
    'Blattschutz wieder setzen
    .Protect "123"
 
  End With

Next z

Worksheets("Makroblatt").Select

End Sub

Gruß

M.O.

Answer 6

Danke probiere ich gleich aus ich wollte gerade eine Schaltfläche erstellen die mir alle Tabellenblätter sperrt aber irgendwas passt nicht.

Sub Sperre()

' Sperrt alles
' Tastenkombination: Strg+w
'
Dim arrTabellen As Variant
Dim z As Integer

'Array mit Tabellenblättern definieren
arrTabellen = Array("FormularDEU", "FormularENG", "FormularBLANK")
       
'Arbeitsblätter durchlaufen
For z = LBound(arrTabellen) To UBound(arrTabellen)
Worksheets(arrTabellen(z)).Protect "123"

Next z

Sheets("Makroblatt").Activate
   

End Sub

Warum wird mir das Passwort markiert?

Answer 7

Hallo,

bei mir funktioniert das Makro einwandfrei. Stimmen die Tabellennamen?

Gruß

M.O.

Answer 8

Gibt es Zeichen die in einem Makropasswort nicht verwendet werden dürfen oder übernimmt er die Zeichen 1:1. Weil logischerweise ist das Passwort nicht 123

Das Next z beendet die Schleife zum Durchlaufen der einzelnen Tabellenblätter. Alles was zwischen For z = LBound(arrTabellen) To UBound(arrTabellen) und next z steht, wird auf jedem Tabellenblatt ausgeführt.

Super Erklärung danke!

Answer 9

Hallo,

mit ist jetzt keine Einschränkung bekannt. Du kannst ja mal zu Testzwecken ein anderes Passwort probieren. Wenn der Fehler auftaucht gehe mal mit dem Cursor auf das z, dann wird angezeigt welchen Wert z hat und du kannst sehen welchen Blatt es betrifft. Aber Achtung: die Zählung fängt bei Null an. Welche Meldung wird denn bei Auftauchen des Fehler angezeigt?

Gruß

M.O.

Answer 10

Ok das werde ich noch probieren.

Ich habe jetzt noch wie folgt den Druckbereich vorgegeben

Sheets("FormularDEU").Select

Range ("A1:E17").Select

ActiveSheet.PageSetup.PrintArea = ("$A$1:$K$17")

Sheets("FormularENG").Select

usw.... 

jetzt mit dem was ich gelernt habe sieht das ziemlich zweckdienlich aus, aber nicht schön.

Ich werde jetzt mal folgendes probieren:

Sub Hintergrundfarbe()

Dim arrTabellen As Variant
Dim z As Integer

'Array mit Tabellenblättern definieren
arrTabellen = Array("FormularDEU", "FormularENG", "FormularBLANK")

'Arbeitsblätter durchlaufen
For z = LBound(arrTabellen) To UBound(arrTabellen)
  With Worksheets(arrTabellen(z))
    'Blattschutz aufheben
    .Unprotect "123"

.PageSetup.PrintArea = ("$A$1:$K$17") 

Das mit dem Druckbereich hat wunderbar geklappt und ich habe langsam mit eurer Hilfe verstanden wie VBA funktioniert.

Der Fehler wegen dem Passwort = "Falsches Passwort Groß oder Kleinschreibung beachten" obwohl er damit die Tabellenblätter sperrt und sie lassen sich auch manuell mit dem passwort wieder entsperren.

auch wenn man nur ein Tabellenblatt entsperrt geht das