Hallo zusammen,
Franz hat mir geholfen folgendes Codes zur entwickeln.
Sub Speichern()
Dim wks As Worksheet, wbThis As Workbook, wbSave As Workbook
Dim rng As Range
Set wbThis = ThisWorkbook
With wbThis.Worksheets("Tabelle1")
strDateiname = ThisWorkbook.Path & "\" & .Range(" J4") & "_" & .Range("R8") & "_" & _
.Range("R6") & "_" & "Backup" & "_" & Format(Date, "YYYYMMDD") & ".xls"
End With
wbThis.SaveCopyAs strDateiname
Set wbSave = Workbooks.Open(FileName:=strDateiname)
wbSave.Unprotect
For Each wks In wbSave.Worksheets
wks.Unprotect
For Each rng In wks.UsedRange.Cells
If Not IsEmpty(rng) Then rng.Locked = True
Next rng
wks.Protect
Next wks
wbSave.Protect
wbSave.Close savechanges:=True
End Sub
Es funktioniert.
Ich habe es etwas abgeändert, um den Code in meiner Arbeitsmappe einzufügen.
Jetzt kommt die Fehlermeldung: Die Locked Eigenschaft des Range-Objektes kann nicht festgelegt werden. Beim Debuggen wird die Zeile: rng.Locked = True gelb markiert.
Private Sub CommandButton1_Click()
Me.Hide
Dim wks As Worksheet, wbThis As Workbook, wbSave As Workbook
Dim rng As Range
Set wbThis = ThisWorkbook
With wbThis.Worksheets("Abrechnungsblatt")
strDateiname = ThisWorkbook.Path & "\" & .Range(" J4") & "_" & .Range("R8") & "_" & _
.Range("R6") & "_" & "Backup" & "_" & Format(Date, "YYYYMMDD") & ".xls"
End With
wbThis.SaveCopyAs strDateiname
Set wbSave = Workbooks.Open(Filename:=strDateiname)
wbSave.Unprotect
For Each wks In wbSave.Worksheets
wks.Unprotect
For Each rng In wks.UsedRange.Cells
If Not IsEmpty(rng) Then rng.Locked = True
Next rng
wks.Protect
Next wks
wbSave.Protect
wbSave.Close savechanges:=True
End Sub
Kann das jemand nachvollziehen? wäre für einen Feedback dankbar ;)
Danke und Gruss
Salim