Optimierung VBA-Code (For Each...)
21.04.2006 16:52:13
Torsten
folgenden, funktionierenden Code möchte ich optimieren, weil ich 25 Sheets in den Array aufnehmen muß:
Dim ws
Dim Arr, i%
Dim ZE As Object
Arr = Array("B1", "B2", "B3", "B4", "B5", "B6")
For i = LBound(Arr, 1) To UBound(Arr, 1)
Sheets(Arr(i)).Unprotect Password:="maze"
Next i
With Sheets("B1")
.Range("BUD1").Locked = True
End With
With Sheets("B2")
.Range("BUD1").Locked = True
End With
With Sheets("B3")
.Range("BUD1").Locked = True
End With
With Sheets("B4")
.Range("BUD1").Locked = True
End With
With Sheets("B5")
.Range("BUD1").Locked = True
End With
With Sheets("B6")
.Range("BUD1").Locked = True
End With
Arr = Array("B1", "B2", "B3", "B4", "B5", "B6")
For i = LBound(Arr, 1) To UBound(Arr, 1)
Sheets(Arr(i)).Protect Password:="maze"
Next i
Meine Versuche schlagen jedoch fehl:
...
Arr = Array("B1", "B2", "B3", "B4", "B5", "B6")
For i = LBound(Arr, 1) To UBound(Arr, 1)
For Each Sheet In Array(i)
Sheets(Arr(i)).Unprotect Password:="maze"
Sheets(Arr(i)).Range("BUD1").Locked = True
Sheets(Arr(i)).Protect Password:="maze"
Next i
...
Könnt Ihr mir weiterhelfen?
Vielen Dank im voraus.
Gruß
Torsten