Blattschutz mit VBA
Adrian Herrmann
ich habe ein Arbeitsblatt, daß standardmäßig geschützt ist. Diesem Blatt
liegen teilweise Daten aus anderen Tabellen der Arbeitsmappe zugrunde. Weil
es sich hierbei um unterschiedlich lange Listen handelt werden einfach alte
Zeilen gelöscht und neu eingefügt.
Zu diesem Vorgang muß ich den Schutz aufheben. Beim Versuch das zu tun
bekomme ich eine Fehlermeldung
ws = das Worksheet um das es geht, die Zeile
ws.Unprotect
verursacht einen Laufzeitfehler 1004, Die Methode 'Unprotect' für das Objekt
'_Worsheet' ist fehlgeschlagen
Das Schützen mit
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
funktioniert tadellos. Hm, woran liegts?
Gruß
Adrian Herrmann
Excel 97, Win2k
Thomas Ramel
Adrian Herrmann schrieb:
>
> ws = das Worksheet um das es geht, die Zeile
Wie hast du ws dimensioniert und wie hat Du das Tabellenblatt zugewiesen?
Dim ws as worksheet
Set ws = Worksheets(1)
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für Microsoft-Excel -
Adrian Herrmann
Hallo,
erstmal danke für Deine Blitzreaktion. Ich deklariere die Tabellenblätter
immer mit Namen. Konkret heißt das hier:
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("HOAI_Gebühren")
das müßte Deinem Vorschlag gleich kommen. Komischerweise läuft es mal und
mal nicht. Ich habe es auch schon über ein aufgezeichnetes Makro probiert.
Anfangs ging das, mittlerweile auch nicht mehr. Vermutlich habe ich irgendwo
etwas eingebaut was die Sache verhindert.
Ich füge den Code der Funktion einfach mal an. Wie gesagt es geht nur um die
Sperre. Ein Paßwort wird nciht verwendet.
Public Function Leistungsphasen(strTabelle As String)
Dim iz1 As Long, iz4 As Long
Dim iz2 As Long, iz3 As Long
Dim i As Long
Dim stStart As String, stEnde As String
Dim ws As Worksheet, ws2 As Worksheet
Dim hilf As Range
Set ws = ActiveWorkbook.Worksheets("HOAI_Gebühren")
Set ws2 = ActiveWorkbook.Worksheets(strTabelle)
ws.Activate
'alte Werte Löschen
iz1 = findRow("HOAI_Gebühren", 2, 1, "Leistungsphasen", "Summe:", 0) + 2
iz2 = findRow("HOAI_Gebühren", 2, iz1, "Summe:", "Summe:", 0) - 2
'Makro3 'ws.Unprotect "frei"
'********************************************
ws.Unprotect
'********************************************
'Alte Zeilen löschen
Set hilf = Range(Rows(iz1), Rows(iz2))
hilf.Select
Selection.Delete Shift:=xlUp
stStart = "<Leistungsphasen>"
stEnde = "</Leistungsphasen>"
'Zeilen der Leistungsphasen in der Quelltabelle
iz3 = findRow(strTabelle, 1, 1, stStart, stEnde, 0) + 1
iz4 = findRow(strTabelle, 1, 1, stEnde, stEnde, 0) - 1
Rows(iz1).Select
Selection.Insert Shift:=xlUp
For i = iz4 To iz3 Step -1
Selection.Insert Shift:=xlUp
ws.Cells(iz1, 2) = ws2.Cells(i, 1) 'LP
ws.Cells(iz1, 3) = ws2.Cells(i, 2) 'LP-Text
ws.Cells(iz1, 6) = ws2.Cells(i, 4) 'Anteil
ws.Cells(iz1, 7).Locked = False
ws.Cells(iz1, 7).FormulaHidden = False
ws.Cells(iz1, 7).Interior.ColorIndex = xlNone
ws.Cells(iz1, 8).FormulaR1C1 = "=RC[-1]*RC[-2]*R15C8"
ws.Cells(iz1, 9) = "Euro"
Next i
Selection.Insert Shift:=xlUp
'****************************************************
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'****************************************************
Set ws2 = Nothing: Set ws = Nothing
End Function
> Dim ws as worksheet
> Set ws = Worksheets(1)
Hm, hat das Vorteile wenn ich die Blätter mit Nummern indiziere, was
passiert wenn jemand neue Blätter einfügt?
Gruß
Adrian Herrmann
Thomas Ramel
Adrian Herrmann schrieb:
>>Wie hast du ws dimensioniert und wie hat Du das Tabellenblatt zugewiesen?
>
> erstmal danke für Deine Blitzreaktion. Ich deklariere die Tabellenblätter
> immer mit Namen. Konkret heißt das hier:
>
> Dim ws As Worksheet
> Set ws = ActiveWorkbook.Worksheets("HOAI_Gebühren")
Ja; das ist in Ordnung so.
> das müßte Deinem Vorschlag gleich kommen. Komischerweise läuft es mal und
> mal nicht. Ich habe es auch schon über ein aufgezeichnetes Makro probiert.
> Anfangs ging das, mittlerweile auch nicht mehr. Vermutlich habe ich irgendwo
> etwas eingebaut was die Sache verhindert.
>
> Ich füge den Code der Funktion einfach mal an. Wie gesagt es geht nur um die
> Sperre. Ein Paßwort wird nciht verwendet.
Die Funktion wir *auschliesslich* in VBA verwendet, also von dort heraus
aufgerufen?
Vielleicht hift es eine Sub daraus zu machen?
Der Aufruf und die Deklaration erfolgen identisch.
Startest Du ev. von einem Button auf dem Tabellenblatt?
Dann setze dessen Eigenschft 'TakeFocusOnClick' auf 'False'.
> Public Function Leistungsphasen(strTabelle As String)
> Dim iz1 As Long, iz4 As Long
> Dim iz2 As Long, iz3 As Long
> Dim i As Long
> Dim stStart As String, stEnde As String
> Dim ws As Worksheet, ws2 As Worksheet
> Dim hilf As Range
>
> Set ws = ActiveWorkbook.Worksheets("HOAI_Gebühren")
> Set ws2 = ActiveWorkbook.Worksheets(strTabelle)
> ws.Activate
>
> 'alte Werte Löschen
> iz1 = findRow("HOAI_Gebühren", 2, 1, "Leistungsphasen", "Summe:", 0) + 2
> iz2 = findRow("HOAI_Gebühren", 2, iz1, "Summe:", "Summe:", 0) - 2
> 'Makro3 'ws.Unprotect "frei"
>
> '********************************************
> ws.Unprotect
Wenn ws wie oben aktiviert wird, sollte auch folgendes funktionieren
vielleicht:
ActiveSheet.Unprotect
> '********************************************
>
> 'Alte Zeilen löschen
> Set hilf = Range(Rows(iz1), Rows(iz2))
> hilf.Select
> Selection.Delete Shift:=xlUp
Hier eventuell wie flgt kürzen (hat nichts mit dem Problem zu tun; fiel mir
einfach auf:
Set hilf = Range(Rows(iz1), Rows(iz2))
hilf.Delete Shift:=xlUp
> stStart = "<Leistungsphasen>"
> stEnde = "</Leistungsphasen>"
> 'Zeilen der Leistungsphasen in der Quelltabelle
> iz3 = findRow(strTabelle, 1, 1, stStart, stEnde, 0) + 1
> iz4 = findRow(strTabelle, 1, 1, stEnde, stEnde, 0) - 1
> Rows(iz1).Select
>
> Selection.Insert Shift:=xlUp
Ebenfalls hie eventuell zu kürzen:
Rows(iz1).Insert Shift:=xlUp
> For i = iz4 To iz3 Step -1
> Selection.Insert Shift:=xlUp
Hier dann ein referenzierter Bezug anstelle der Selection (nicht getestet):
Rows(iz1).Insert Shift:=xlUp
> ws.Cells(iz1, 2) = ws2.Cells(i, 1) 'LP
> ws.Cells(iz1, 3) = ws2.Cells(i, 2) 'LP-Text
> ws.Cells(iz1, 6) = ws2.Cells(i, 4) 'Anteil
> ws.Cells(iz1, 7).Locked = False
> ws.Cells(iz1, 7).FormulaHidden = False
> ws.Cells(iz1, 7).Interior.ColorIndex = xlNone
> ws.Cells(iz1, 8).FormulaR1C1 = "=RC[-1]*RC[-2]*R15C8"
> ws.Cells(iz1, 9) = "Euro"
> Next i
>
> Selection.Insert Shift:=xlUp
> '****************************************************
> ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> '****************************************************
> Set ws2 = Nothing: Set ws = Nothing
>
> End Function
>
>>Dim ws as worksheet
>>Set ws = Worksheets(1)
>
> Hm, hat das Vorteile wenn ich die Blätter mit Nummern indiziere, was
> passiert wenn jemand neue Blätter einfügt?
Das war 'nur' ein allgemeines Beispiel; die Deklaration, resp. zuweisung
mittels Blattnamen ist gleichwrtig und in diesem Falle unabhängig der
Position innerhalb der Tabellenblätter.
Adrian Herrmann
> > Ich füge den Code der Funktion einfach mal an. Wie gesagt es geht nur um
die
> > Sperre. Ein Paßwort wird nciht verwendet.
>
> Die Funktion wir *auschliesslich* in VBA verwendet, also von dort heraus
> aufgerufen?
Ja ich greife nur händisch ein, wenn das Programm seinen Dienst nicht tut
und ich an das Blatt muß
> Vielleicht hift es eine Sub daraus zu machen?
Habe ich probiert mit vorher Blatt aktivieren und activesheet.unprotect. Der
Fehler bleibt
> Der Aufruf und die Deklaration erfolgen identisch.
Das verstehe ich jetzt nicht ganz.
> Startest Du ev. von einem Button auf dem Tabellenblatt?
> Dann setze dessen Eigenschft 'TakeFocusOnClick' auf 'False'.
Nein, gearbeitet wird auf einem Tabellenblatt namens HOAI_Gebühren. Dort
befinden sich 2 Kombinationslistenfelder. Mit dem ersten dieser Felder wählt
man praktisch ein Tabellenblatt auf, mit dem gerechnet werden soll. Beim
Ereignis Change löst dieses Kombinationslistenfeld eine Reihe von Funktionen
aus
Private Sub Honorartabelle_Change()
Dim hilf As String
If Not IsNull(Me.Honorartabelle) And Me.Honorartabelle <> "" Then
'*** Datenbereich für das 2. LF (=Honorarzone) zuweisen
hilf = getBereich("Honorarzone", Me.Honorartabelle)
Me.Honorarzone.ListFillRange = hilf
'*** Die Leistungsphasen werden angelegt, in dieser Public Function
' werden die alten Zeilen gelöscht und neu geschrieben, dort
muß der
' Schutz aufgehoben sein
Leistungsphasen Me.Honorartabelle
'*** Die Honorarzonenauswahl wird zurückgesetzt '
Me.Honorarzone = Null
End If
End Sub
> > '********************************************
> > ws.Unprotect
>
> Wenn ws wie oben aktiviert wird, sollte auch folgendes funktionieren
> vielleicht:
>
> ActiveSheet.Unprotect
das Makro hatte diese Befehlszeile, die auch zeitweise funktionierte, was
mich eben so stutzig macht.
> Hier eventuell wie flgt kürzen (hat nichts mit dem Problem zu tun; fiel
mir
> einfach auf:
>
> Set hilf = Range(Rows(iz1), Rows(iz2))
> hilf.Delete Shift:=xlUp
ja danke, ich bin noch nicht so sattelfest in Excel und mißbrauche neben der
Literatur die Makroaufzeichnung um bestimmte Vorgänge in VBA zu sehen und
übernehme nicht immer optimierten Code.
> > For i = iz4 To iz3 Step -1
> > Selection.Insert Shift:=xlUp
>
> Hier dann ein referenzierter Bezug anstelle der Selection (nicht
getestet):
>
> Rows(iz1).Insert Shift:=xlUp
dito
auf jeden Fall nochmal herzlichen Dank.
Gruß
Adrian Herrmann
Adrian Herrmann
> Dann setze dessen Eigenschft 'TakeFocusOnClick' auf 'False'.
Das hat mich auf den richtigen Weg gebracht, ich selectiere jetzt bevor ich
unprotect durchführe eine Zelle die nicht geschützt wird, also
ws.cells(10,8).select
dann funktionierts. Mann, was für eine Geburt, darauf muß man erst mal
kommen.
Nochmal vielen Dank.
Grüße
Adrian Herrmann
Thomas Ramel
Adrian Herrmann schrieb:
>
>>Vielleicht hift es eine Sub daraus zu machen?
>
> Habe ich probiert mit vorher Blatt aktivieren und activesheet.unprotect. Der
> Fehler bleibt
>
>>Der Aufruf und die Deklaration erfolgen identisch.
>
> Das verstehe ich jetzt nicht ganz.
Ich meinte aus der Funtion ein e Sub machen (im Pronzip ersetzen der Wortes
'Function' durch 'Sub') Der Rest ist derselbe wie mit einer Funktion;
alsot die Deklaration der Variablen und auch der eigentliche Aufruf der
Prozedur.
>>ActiveSheet.Unprotect
>
> das Makro hatte diese Befehlszeile, die auch zeitweise funktionierte, was
> mich eben so stutzig macht.
Ja; wirklich eingenartig.
>> Set hilf = Range(Rows(iz1), Rows(iz2))
>> hilf.Delete Shift:=xlUp
>
> ja danke, ich bin noch nicht so sattelfest in Excel und mißbrauche neben der
> Literatur die Makroaufzeichnung um bestimmte Vorgänge in VBA zu sehen und
> übernehme nicht immer optimierten Code.
Kein Problem; eine Hilfe hierbei könnte viellicht die folgende Datei sein.
Es wir IMHO sehr anschaulich der Unterschied zwischen Selektieren und
Refernzieren eklärt:
http://www.excel-cd.de/downloads/basics/xlBasics.zip
> auf jeden Fall nochmal herzlichen Dank.
Bitte schön, ist gern geschehen; es frut mcih, wenn's hilft.
Thomas Ramel
Adrian Herrmann schrieb:
>>Startest Du ev. von einem Button auf dem Tabellenblatt?
>>Dann setze dessen Eigenschft 'TakeFocusOnClick' auf 'False'.
>
> Das hat mich auf den richtigen Weg gebracht, ich selectiere jetzt bevor ich
> unprotect durchführe eine Zelle die nicht geschützt wird, also
>
> ws.cells(10,8).select
>
> dann funktionierts. Mann, was für eine Geburt, darauf muß man erst mal
> kommen.
So quasi von hinten durch die Brust ins Auge, was?
So richtig klar ist mir das Ganze nicht; sollte os im Prinzip nich
tnotwendig sein.
Ohne die Mappe aber selbst 'in Händen' zu haben wirs's nun echt schwierig.
Aber schön, dass Du eine Lösung gefunden hat.
Adrian Herrmann
> So richtig klar ist mir das Ganze nicht; sollte os im Prinzip nich
> tnotwendig sein.
> Ohne die Mappe aber selbst 'in Händen' zu haben wirs's nun echt schwierig.
Ich kanns mir auch nicht erklären. Die Mappe habe ich jetzt soweit
fertiggestellt. Es handelt sich um eine Tabelle zur Berechnung von
Ingenieurhonoraren nach der deutschen HOAI. Dieses Werk wollte ich neben
anderen Werken auf unserer noch nicht existierenden Homepage frei verfügbar
machen und ist auch deshalb kein allzu großes Geheimnis.
Ich werde die Datei vorab schon zum Testen weitergeben, falls Interesse
besteht das Problem näher zu erörtern kann ich die Arbeitsmappe mit allem
gerne zumailen. Der vorhandene Blattschutz läßt sich ohne Paßwort
deaktivieren.
Gruß
Adrian Herrmann
Thomas Ramel
Adrian Herrmann schrieb:
>
> Ich kanns mir auch nicht erklären. Die Mappe habe ich jetzt soweit
> fertiggestellt. Es handelt sich um eine Tabelle zur Berechnung von
> Ingenieurhonoraren nach der deutschen HOAI. Dieses Werk wollte ich neben
> anderen Werken auf unserer noch nicht existierenden Homepage frei verfügbar
> machen und ist auch deshalb kein allzu großes Geheimnis.
>
> Ich werde die Datei vorab schon zum Testen weitergeben, falls Interesse
> besteht das Problem näher zu erörtern kann ich die Arbeitsmappe mit allem
> gerne zumailen. Der vorhandene Blattschutz läßt sich ohne Paßwort
> deaktivieren.
Das kannst Du gerne tun.
Vielleicht hilst es auch, die Mappe nochmals 'von Null' aufzubauen.
Gerade wenn vile programmiert und angepasst wird, kann es gut sein, dass
mal was 'kapputt' geht und nicht mehr 'repariert' werden kann.
Module kannst Du exportiern und wieder importieren; Tabellenblätter
gegebenefalls kopieren.