Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Autom. Zeilenhöhe verbundene Zelle mit Blattschutz

Betrifft: Autom. Zeilenhöhe verbundene Zelle mit Blattschutz von: Chris
Geschrieben am: 25.09.2014 10:34:53

Hallo,
hab noch ein "kleines" Problem das ich leider nicht lösen kann.
Ich habe den Code aus folgendem Beitrag https://www.herber.de/mailing/Zeilenhoehe_bei_verbundenen_Zellen_anpassen.htm in ein Tabellenblatt kopiert und er funktioniert auch erst mal super.
Musterdatei habe ich angehängt, PW ist 1234.
Das Problem ist, daß das Arbeitsblatt geschützt ist und auch bleiben muss. Mittlerweile habe ich auch die Parameter beim Schutz so gesetzt, daß kein Laufzeitfehler mehr kommt.
Das Problem ist jetzt noch Folgendes, was ich mir nicht erklären kann:
Nach Eingabe des Textes z.B. in Zelle C17 mit einem 2-Zeilen langen Text und dann Drücken auf Enter paßt sich zwar die Zeilenhöhe automatisch an, aber die Zelle wird auf geschützt gesetzt und ich kann sie nicht mehr anwählen.
Wenn ich den Blattschutz aufhebe und auf Eigenschaften gehen, dann ist unter Schutz gesperrt angekreuzt, obwohl vorher der Haken nicht da war.
Hat da einer von euch eine Lösung?
Danke schon mal vorab.
Linkl zur Datei: https://www.herber.de/bbs/user/92801.xlsm

  

Betrifft: Damit man das wirklich testen kann, solltest ... von: Luc:-?
Geschrieben am: 25.09.2014 12:38:05

…du wenigstens das PW mitteilen, Chris,
wenn du schon eines verwendet hast (statt es leer zu lassen). So kann man deine Feststellung nur bestätigen, denn das Einfügen von CurrCell.Locked = False nutzt so nichts, da der Befehl nicht akzeptiert wird.
Hast du auch wirklich an geeigneter Stelle Me.Protect "passwort", True, True, True, True (für UserInterfaceOnly) angegeben? Ggf muss die VerbundZelle auch zuvor aufgehoben wdn (s. dazu auch dieses!).
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Damit man das wirklich testen kann, solltest ... von: Chris
Geschrieben am: 25.09.2014 13:02:03

Hallo Luc,
Danke für deine Antwort.
Das PW habe ich im Post angegeben - 1234.
Leider bin ich des VBA nicht so mächtig, deshalb hatte ich versucht es ohne VBA hinzukriegen.
Aber auch mit einer Hilfszelle, die den Inhalt der eigentlichen Zelle hat (wie es im Internet oft als 1. Möglichkeit empfohlen wird) hat nicht funktioniert.
Es klappt auch wirklich hervorragend bis eben auf die Sperrung der Zelle. Kann man das nicht unterbinden?
Gruß, Chris


  

Betrifft: Hatte ich tatsächlich übersehen, ... von: Luc:-?
Geschrieben am: 25.09.2014 13:41:20

…Chris,
1234 war wohl nicht auffällig genug… ;-)
So, jetzt klappt auch die Ergänzung → vor Application.ScreenUpdating = True einfach noch .Locked = False einfügen! Wenn du nun zum Ereignis Worksheet_Activate auch die bereits genannte CodeZeile angelegt hast, klappt das auch. Evtl musst du diese Prozedur auch noch vom Workbook_Open-Ereignis aus aufrufen (falls das betreffende Blatt StartBlatt ist) bzw explizit dieses Blatt aktivieren, damit der PW-Schutz mit allen Optionen ausgeführt wird.
Habe allerdings festgestellt, dass die Herber-Prozedur auf VerbundZellen nicht aufkopier-tolerant ist. Sie läuft dabei in einen nicht abgefangenen Fehler!
Luc :-?


  

Betrifft: AW: Hatte ich tatsächlich übersehen, ... von: Chris
Geschrieben am: 25.09.2014 14:18:08

Hallo Luc,
Danke für die Antwort.
Kann aber deinen Ausführungen nur teilweise folgen, weil meine VBA-Kenntnisse bescheiden sind.
Ich füge also vor "Application.ScreenUpdating = True" die Zeile ".Locked = False" ein.
Das alleine verursacht einen Laufzeit-Fehler "Die Locked-Eigenschaften des Range-Objektes kann nicht festgelegt werden".
Welche Code-Zeile soll ich im "Worksheet_Activate" anlegen?
Und wo finde ich das "Workbook_Open" Ereignis?
Vielleicht kannst du die Codes hier reinschreiben?
Oder in die Datei?
Wäre super.
Und Danke schon mal für deine Mühe.


  

Betrifft: AW: Hatte ich tatsächlich übersehen, ... von: Chris
Geschrieben am: 25.09.2014 14:34:38

Hallo Luc,
Danke für die Antwort.
Kann aber deinen Ausführungen nur teilweise folgen, weil meine VBA-Kenntnisse bescheiden sind.
Ich füge also vor "Application.ScreenUpdating = True" die Zeile ".Locked = False" ein.
Das alleine verursacht einen Laufzeit-Fehler "Die Locked-Eigenschaften des Range-Objektes kann nicht festgelegt werden".
Welche Code-Zeile soll ich im "Worksheet_Activate" anlegen?
Und wo finde ich das "Workbook_Open" Ereignis?
Vielleicht kannst du die Codes hier reinschreiben?
Oder in die Datei?
Wäre super.
Und Danke schon mal für deine Mühe.


  

Betrifft: Nahm an, dass du bescheid weißt, ... von: Luc:-?
Geschrieben am: 25.09.2014 15:39:38

…Chris,
denn du hattest ja geschrieben „Mittlerweile habe ich auch die Parameter beim Schutz so gesetzt, daß kein Laufzeitfehler mehr kommt“.
Wenn du den Cursor auf die vorhandene Prozedur setzt, kannst du rechts oben im Kopf Activate auswählen. Das legt den ProzedurRahmen an. In die Leerzeile dazwischen musst du jetzt nur noch die bereits gezeigte Protect-CodeZeile eintragen.
Wenn du der Mappe ein weiteres Blatt hinzufügst und darauf wechselst und anschließend wieder zurück, wird der Eintrag wirksam und der Locked-Befehl wird bei Eintrag akzeptiert.
Mit Open ist's genauso einfach → aufs Klassenmodul der Mappe (DieseArbeitsmappe) wechseln und im Kopf links auf Workbook klicken. Open wird sofort angelegt, weil Standard-Ereignis. Hierin dann Me.Sheets("Change").Activate (bzw …Select) eintragen. Das fktioniert auf jeden Fall dann, wenn Change nicht das 1.Blatt ist. Wenn's bei Mappenstart nicht klappt, hier Call Me.Sheets("Change").Worksheet_Activate eintragen.
Luc :-?


  

Betrifft: AW: Nahm an, dass du bescheid weißt, ... von: Chris
Geschrieben am: 25.09.2014 16:32:30

Hallo Luc,
den ersten Teil hab ich verstanden und sieht jetz so aus:

Private Sub Worksheet_Activate()
Me.Protect "1234", True, True, True, True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MergedRangeWidth As Single, MergedCellWidth As Single, NewRowHeight As Single
Dim CurrCell As Range, iX As Integer
     If Target.MergeCells Then
        If Target.Cells(1) = "" Then
            Target.EntireRow.AutoFit
        Else
            With Target.MergeArea
                If .Rows.Count = 1 And .WrapText = True Then
                    Application.ScreenUpdating = False
                    MergedCellWidth = .Cells(1).ColumnWidth
                    For Each CurrCell In Target.MergeArea
                        MergedRangeWidth = CurrCell.ColumnWidth + MergedRangeWidth
                        iX = iX + 1
                    Next
                    MergedRangeWidth = MergedRangeWidth + (iX - 1) * 0.71
                    .MergeCells = False
                    .Cells(1).ColumnWidth = MergedRangeWidth
                    .EntireRow.AutoFit
                    NewRowHeight = .RowHeight
                    .Cells(1).ColumnWidth = MergedCellWidth
                    .MergeCells = True
                    .RowHeight = NewRowHeight
                    .Locked = False
                    Application.ScreenUpdating = True
                End If
            End With
        End If
    End If
End Sub
Und so das Workbook:
Private Sub Workbook_Open()
Call Me.Sheets("Change").Worksheet_Activate
End Sub
Funktioniert auch. Wenn ich die Datei speicher, schließe, dann wieder öffne und in der nächsten Zeile was eintrage kommt ein Fehler, daß er die MergeCells Eigenschaften des Range-Objektes nicht festlegen kann. Beim Klick auf Debuggen markiert er den Eintrag .Locked=False gelb.
Wenn ich den Blattschutz aktiviere dann habe ich nur nicht gesperrte Zellen auswählen und Zellen und Zeilen formatieren angeklickt.
Ohne Blattschutz klappt alles. Ich vermute es liegt an den Freigaben für den Blattschutz.
Hast du da noch eine Idee?


  

Betrifft: Es kommt bei 'Protect' auf das 4.'True' an, ... von: Luc:-?
Geschrieben am: 25.09.2014 17:28:19

…Chris;
solange Xl das nicht verinnerlicht hat, fktioniert Locked nicht, denn solange sind derartige MakroAktionen verboten, anderenfalls erlaubt. Bevor du irgendetwas ins Blatt Change einträgst, solltest du erst mal das Blatt wechseln und wieder zurück gehen, damit die Protect-Zeile wirken kann. Danach muss der Schutz nicht mehr aufgehoben wdn. Falls du das mal machen musst, dann nicht wieder manuell setzen, sondern per Blattwechsel hin-und-zurück. Manuell wird das 4.True nämlich nicht gesetzt!
Ansonsten sollte alles OK sein (bis auf die Sache mit dem Aufkopieren).
Luc :-?


  

Betrifft: AW: Es kommt bei 'Protect' auf das 4.'True' an, ... von: Ewald
Geschrieben am: 25.09.2014 21:00:55

Hallo,

wie wärs so.

ins Tabellenblatt

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Passwort
Dim MergedRangeWidth As Single, MergedCellWidth As Single, NewRowHeight As Single
Dim CurrCell As Range, iX As Integer
     If Target.MergeCells Then
        If Target.Cells(1) = "" Then
            Target.EntireRow.AutoFit
        Else
            With Target.MergeArea
                If .Rows.Count = 1 And .WrapText = True Then
                    Application.ScreenUpdating = False
                    MergedCellWidth = .Cells(1).ColumnWidth
                    For Each CurrCell In Target.MergeArea
                        MergedRangeWidth = CurrCell.ColumnWidth + MergedRangeWidth
                        iX = iX + 1
                    Next
                    MergedRangeWidth = MergedRangeWidth + (iX - 1) * 0.71
                    .MergeCells = False
                    .Cells(1).ColumnWidth = MergedRangeWidth
                    .EntireRow.AutoFit
                    NewRowHeight = .RowHeight
                    .Cells(1).ColumnWidth = MergedCellWidth
                    .MergeCells = True
                    .RowHeight = NewRowHeight
                    .Locked = False
                    .FormulaHidden = True
                    Application.ScreenUpdating = True
                End If
            End With
        End If
    End If
   ActiveSheet.Protect Passwort
End Sub
in "Diese Arbeitsmappe"
Private Sub Workbook_Open()
 Setzen
End Sub
in ein allgemeines Modul
Public Passwort As String
Function Setzen()
Passwort = "1234"
End Function
Gruß Ewald


  

Betrifft: Das 'Entschützen' ist normalerweise bei ... von: Luc:-?
Geschrieben am: 25.09.2014 22:37:44

…Verwendung von UserInterfaceOnly:=True (4.True) nicht erforderlich, Ewald.
So, wie der Code jetzt bei Chris aussieht, liegt das Nicht-Fktionieren offensichtlich an manuellen Manipulationen ohne dass danach auch das Activate-Ereignis ausgelöst wird, denn bei mir hat's wie beschrieben fktioniert.
Gruß, Luc :-?
PS: Bin dann mal übers WE weg. Dir & Chris ein schönes solches.


  

Betrifft: AW: Das 'Entschützen' ist normalerweise bei ... von: Chris
Geschrieben am: 26.09.2014 07:45:58

Im Grunde genommen funktioniert es mit dem Code von Luc.
Ich hatte das nicht verstanden, daß der Schutz automatisch gesetzt wird, wenn ich in ein anderes Blatt wechsel.
Nur wenn ich die Datei neu starte, dann ist ja meistens das zuletzt bearbeitete Blatt offen. In dem Fall Change. Wenn ich jetzt was eintrage, dann kommt der Laufzeitfehler. Ich muß erst in ein anderes Blatt wechseln und wieder zurück dann funktioniert es.
Kann man nicht beim Starten der Datei festlegen, mit welchem Blatt er starten soll?
In meinem Fall wäre das das Blatt "data". Die Datei würde nach dem Öffnen immer mit dem Blatt data starten und dann müßte das Makro funktionieren.

Es ist noch ein weiteres Problem:
In der Arbeitsmappe/Workbook steht jetzt:

Private Sub Workbook_Open()
Me.Sheets("Change").Activate
End Sub

Das ist ungünstig, weil die endgültige Datei mehrere Blätter enthält, in denen ich das Makro benötige. Außerdem werden die Blätter später mal in der Praxis kopiert - auch öfters, weil sie in einem Projekt mehrmals benötigt werden. Die Datei dient als Vorlage für Projektbearbeitung.
Kann man den Code nicht ins Blatt verlagern?

Außerdem ist im VBA das Passwort sichtbar. Damit kann jeder das Passwort einsehen und so ein Schutz macht dann keinen Sinn mehr.
Ist es möglich die Anzeige des Codes zu unterbinden oder das irgendwie anders zu lösen?
Ich weiß dass man auch das umgehen kann, aber der Normaluser kommt da nicht auf die Idee und darum geht es mir.
Schon mal Danke für die tolle Unterstützung. Wir sind schon fast an der endgültigen Lösung angekommen.



  

Betrifft: AW: Das 'Entschützen' ist normalerweise bei ... von: hary
Geschrieben am: 26.09.2014 07:56:18

Moin
"Ist es möglich die Anzeige des Codes zu unterbinden"
Im VBA Editor/Rechtsklick auf die Arbeitsmappe/Eigenschaft von Projekt/Schutz/Projekt fuer die Anzeige sperren und Passwort vergeben.
gruss hary


  

Betrifft: AW: Das 'Entschützen' ist normalerweise bei ... von: Chris
Geschrieben am: 26.09.2014 08:06:20

Manchmal steht man echt daneben.
Danke, da hätte ich auch drauf kommen können.


  

Betrifft: AW: Das 'Entschützen' ist normalerweise bei ... von: Chris
Geschrieben am: 26.09.2014 08:29:36

Ich habs jetzt hinbekommen.
Hab im Workbook folgendes stehen:

Private Sub Workbook_Open()
ActiveWorkbook.Worksheets("Start").Activate
End Sub

Ich hab den bisherigen Eintrag Call Me.Sheets("Change").Worksheet_Activate
einfach rausgeworfen.

Es funktioniert alles. Es geht automatisch auf das Blatt Start, die Zeilenhöhe wird automatisch angepaßt, beim Kopieren des Blattes nimmt er das Makro mit und auch da paßt er die Zeilenhöhe automatisch an.
Und auch nach dem Manuellen Aufheben des Blattschutzes und Änderungen im Blatt setzt er automatisch wieder den Blattschutz.
Ihr seid super und vielen Dank nochmals für die tollen Unterstützung.


  

Betrifft: AW: Das 'Entschützen' ist normalerweise bei ... von: Chris
Geschrieben am: 26.09.2014 09:14:42

Jetzt hab ich doch noch ein Problem gefunden:
Und zwar sollte das Einfügen von Zeilen trotz Blattschutz erlaubt sein.
Leider kann ich nichts über die Anweisung me.protect finden, die ich drin habe und auf welche Werte sich die anschließenden True Angaben beziehen.

Private Sub Worksheet_Activate()
Me.Protect "1234", True, True, True, True
End Sub

Könnt ihr mir noch bitte helfen wie ich das Einfügen von Zeilen erlauben kann?
Vielen Dank.


  

Betrifft: Guckst du VBE-Hilfe, Worksheet.Protect-Methode,... von: Luc:-?
Geschrieben am: 28.09.2014 20:57:58

…Chris:
Ausdruck.Protect Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables
Du musst auch die, die False (bzw lt ihrem Default-Wert) bleiben sollen, nicht alle mit aufführen, sondern kannst dann ihre Benennung mitverwenden, zB so:
Sheets("Change").Protect UserInterfaceOnly:=True, AllowInsertingRows:=True
Anderenfalls kann False auch einfach weggelassen wdn (nicht aber das zugehörige Komma!).
Gruß, Luc :-?


  

Betrifft: AW: Guckst du VBE-Hilfe, Worksheet.Protect-Methode,... von: Chris
Geschrieben am: 29.09.2014 08:00:35

Hi Luc,
Dank für den Hinweis, die Zuordnung ist mir jetzt klar.
So wie ich das aber gelesen habe gilt diese Zuweisung für das Workbook, also die komplette Arbeitsmappe, ich brauche es aber für das Worksheet.
Hab jetzt folgenden Code im Worksheet Activate, der aber den Fehler "Erwartet:Ausdruck" im VBA bringt.

Private Sub Worksheet_Activate()
Ausdruck.Protect "1234", True, True, True, True, True,, True,, True,,,True,,,
End Sub



  

Betrifft: AW: Guckst du VBE-Hilfe, Worksheet.Protect-Methode,... von: Chris
Geschrieben am: 29.09.2014 12:54:30

Habs soweit hinbekommen, aber ich kann trotzdem keine Zeilen einfügen.
Ich habe es jetzt über Schaltflächen gelöst mit Zeile einfügen und Zeile löschen.
Danke nochmals für eure Hilfe und Unterstützung.
Chris


 

Beiträge aus den Excel-Beispielen zum Thema "Autom. Zeilenhöhe verbundene Zelle mit Blattschutz"

Zeilenhöhe in cm festlegen
Beim Kopieren auch die Zeilenhöhe und Spaltenbreite übernehmen
Zeilenhöhe bei verbundenen Zellen anpassen
Bestimmung der Zeilenhöhe
Kennwort für Blattschutz
Blattschutz aufheben
Blattschutzdialog über VBA aufrufen
Blattschutz für eine Serie von Tabellen setzen und aufheben
Blattschutz beim Speichern setzen
Blattschutz setzen und dennoch mit AutoFiltern
Blattschutz aufheben, Daten eintragen, Blattschutz setzen
Blattschutz für VBA-Einträge ausschließen
Blattschutz mit und ohne Passwort aufheben und setzen
Spaltenweiser Blattschutz in Abhängigkeit des Anwendernamens
Blattschutz für alle Arbeitsblätter setzen oder aufheben