Office: Zellbereiche sperren für bestimmte User

Sofixultra · 11. Juni 2008

Hallöchen Profis,

ich habe aus nem älteren Thread hier folgenden Code gefunden:

Code:

Option Explicit
'****************************************
'* erstellt von Karin (Beverly) *
'* 31.12.2005 *
'****************************************
Dim BoZugriff As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sperren
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If BoZugriff = False Then Zugriff
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sperren
BoZugriff = False
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Sperren
End Sub

Sub Sperren()
With Worksheets("Januar 2008")
.Unprotect ("test")
.Range("A:IV").Locked = True
.Protect ("test")
End With
End Sub

Sub Zugriff()
With Worksheets("Januar 2008")
If .ProtectContents Then
.Unprotect ("test")
Select Case UCase(Environ("Username"))
Case "DOMSZKS" ' kann aus dem Code entfernt werden, war nur auf meinem Rechner gültig
.Range("A:a").Locked = False ' kann aus dem Code entfernt werden, war nur auf meinem Rechner gültig
Case "USER_F"
.Range("A:B").Locked = False
Case "USER_G"
.Range("C:G").Locked = False
Case "USER_ICH"
.Range("A:IV").Locked = False
Case "USER_H"
Case Else ' für unberechtigte Nutzer
MsgBox "Sie haben keine Zugriffsrechte für diese Arbeitsmappe"
ActiveWorkbook.Close False
End Select
.Protect ("test")
End If
End With
BoZugriff = True
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Damit kann ich ja für ein Tabellenblatt die Zellbereiche je Kennung frei geben und der Code ist mal richtig gut !!!

Nun habe ich versucht, diesen Code anzupassen das ich alle bzw. 12 Tabellenblätter so versorgen kann und die entsprechenden User freigeben.

Nun ja, das ist mir nicht gelungen, ich komme da nicht weiter.

Ich habe schon versucht in den einzelnen Tabellenblätter beim Activate Ereigniss den Codeteil mit der Userfreigabe einzubauen, das bringt mir aber leider nicht den erwünschten Erfolg.

Auch mit ner Globalen Variable welche mit den Namen des Activierten Sheets ausgibt und anpassung des oberen Codes (Name Tabellenblatt=Globale Variable) brachte mir so leider keinen Erfolg.

Nun hoffe ich, das mir einer von euch weiterhelfen kann, das ich meine 12 Tabellenblätter nach Userkennung bearbeiten kann (also die entsprechenden Zellbereiche).

Schon einmal im Voraus vielen Danke euch ;O)

:)

jinx · 13. Juni 2008

Moin, Sofixultra,

und wie bist Du vorgegangen? Workbook_SheetActivate in DieseArbeitsmappe, die Namen der Tabellen bekommt man per Sh, dann in einer Select Case-Anweisung? Oder zuerst feststellen, welche Tabellen für welchen User überhaupt einsehbar sind und die Bereiche schützen?

Sofixultra · 13. Juni 2008

Hallo Jinx,

ich hatte es auf diese weise Versucht

Code:

Option Explicit
'****************************************
'* erstellt von Karin (Beverly) *
'* 31.12.2005 *
'****************************************
Dim BoZugriff As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sperren
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If BoZugriff = False Then Zugriff
akt = Sh.Name
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sperren
BoZugriff = False
ThisWorkbook.Save
End Sub

Public Sub Workbook_Open()
akt = "Januar 2008"
Sperren
End Sub

Sub Sperren()
With Worksheets(akt)
.Unprotect ("test")
.Range("A:IV").Locked = True
.Protect ("test")
End With
End Sub

Sub Zugriff()
With Worksheets(akt)
If .ProtectContents Then
.Unprotect ("test")
Select Case UCase(Environ("Username"))
Case "DOMSZKS" ' kann aus dem Code entfernt werden, war nur auf meinem Rechner gültig
.Range("A:a").Locked = False ' kann aus dem Code entfernt werden, war nur auf meinem Rechner gültig
Case "USER_F"
.Range("A:B").Locked = False
Case "USER_G"
.Range("C:G").Locked = False
Case "USER_ICH"
.Range("A:IV").Locked = False
Case "USER_H"
Case Else ' für unberechtigte Nutzer
MsgBox "Sie haben keine Zugriffsrechte für diese Arbeitsmappe"
ActiveWorkbook.Close False
End Select
.Protect ("test")
End If
End With
BoZugriff = True
End Sub

Public Sub Workbook_SheetActivate(ByVal Sh As Object)
akt = Sh.Name
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

also in Diese Arbeitsmappe noch den Namen des aktivierten Tabellenblattes abfragen und an die Globale Variable übergeben.
Damit es keine Fehlermeldung beim ersten Aufrufen des Workbook gibts, habe ich bei WORKBOOK_OPEN die Variabkle auf Tabellenblatt Januar 2008 gestellt (sonst kommt Index ausserhalb des Bereiches).

Im moment sieht es so aus...Januar ist total geschützt und z.B. Tabellenblatt September ist total offen und zwar für alle User...also scheint in meinem Coding was total nicht zu stimmen...

jinx · 13. Juni 2008

Moin, Sofixultra,

ein Sperren würde ich auch in einer Schleife für alle Tabellen der Mappe durchführen:

Code:

Dim ws As Worksheet
For Each ws in Worksheets
akt = ws.Name
Sperren
Next ws
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Die Variable akt ist global dimensioniert und gehört in ein allgemeines Modul:

Code:

Public akt as String
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Code ist ungetestet, und die Variable akt hält nach dem Durchlauf den Namen der letzten Tabelle: dies ist ggf. durch eine Zuweisung eines anderen Namens zu ändern...

Sofixultra · 13. Juni 2008

Nabend Jinx,

also, die Sheets sind bereits über Extras->Optionen gesperrt (damit bei deaktivierten Makros trotzdem keiner die Zellbereiche bearbeiten kann).

Und die Globale Variable ist natürlich bereits im Modul 1 hinterlegt ;O)
(Global akt as string)

Mein Prob ist jedoch, das mit meinem Coding anscheinend nicht die Tabellenblätter bzw. die Zellbereicht frei gegeben werden...da haperts noch.

Oder aber, der Code gibt ein ganzes Blatt frei *frown.gif*

jinx · 13. Juni 2008

Moin, Sofixultra,

in meiner angehängten Beispielmappe habe ich auf das Auslesen des Benutzernamens zugunsten Daten/Gültigkeit in Tabelle1 verzichtet...

Code:

'DieseArbeitsmappe
Option Explicit
'****************************************
'* erstellt von Karin (Beverly) *
'* 31.12.2005 *
'* 14.06.2008 jinx Anpassungen
'****************************************
Dim BoZugriff As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
akt = ws.Name
Sperren
Next ws
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
BoZugriff = False
ThisWorkbook.Save
End Sub

Public Sub Workbook_Open()
akt = "Januar 2008"
Zugriff
End Sub

Sub Sperren()
With Worksheets(akt)
If .ProtectContents Then
.Unprotect ("test")
.Cells.Locked = True
.Protect ("test")
End If
End With
End Sub

Sub Zugriff()
With Worksheets(akt)
If .ProtectContents Then
.Unprotect ("test")
Select Case UCase(Range("Kennung")) '!!! geändert wegen Daten/Gültigkeit!!!
Case "DOMSZKS" ' kann aus dem Code entfernt werden, war nur auf meinem Rechner gültig
.Range("A:A").Locked = False ' kann aus dem Code entfernt werden, war nur auf meinem Rechner gültig
Case "USER_F"
.Range("A:B").Locked = False
Case "USER_G"
.Range("C:G").Locked = False
Case "USER_ICH"
.Range("A:IV").Locked = False
Case "USER_H"
Case Else ' für unberechtigte Nutzer
MsgBox "Sie haben keine Zugriffsrechte für diese Arbeitsmappe"
ActiveWorkbook.Close False
End Select
.Protect ("test")
End If
End With
BoZugriff = True
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Code:

'Standardmodul
Option Explicit

Public akt As String
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Sofixultra · 15. Juni 2008

Hallo Jinx,

sorry das ich erst so spät antwort.

Habe mir Dein Beispiel mal angeschaut und getestet.

Klappt soweit Super, vielen Dank schon einmal dafür !!!!

Nur ist mir bei mir zuhause eins aufgefallen.

Wenn ich die Exceltabelle öffne und Makros deaktiviere, Excel wieder schliesse und danach öffne, kann jeder bereich des Sheets bearbeitet werden, der Schutz ist aber noch vorhanden. Wenn ich nun den Schutz manuell aufhebe und die Zellen neu schützen möchte, dann ist immer der Haken bei (User dürfen gesperrte Zellen bearbeiten) gesetzt, diesen kann ich dann auch nur schwer wieder entfernen, denn beim speichern der "geänderten" Tabelle wird der Haken anscheinend automatisch gesetzt.

Ist das bei Dir auch so oder liegt das an einer ominösen EInstellung meines Excel (2002) zuhause?

Nochmals besten Dank ;O)

jinx · 15. Juni 2008

Moin, Sofixultra,

zur Zeit kann ich es nur auf einer virtuellen Maschine mit ExcelXP ohne SP testen, und da habe ich keinerlei Zugriff auf die beiden Monatstabellen, wenn ich die Mappe ohne Makros öffne (was ich eigentlich auch aufgrund der beim Schließen ablaufenden Prozeduren erwartet habe). Und auch der Schutz funktioniert hier "klag"los so, wie ich es erwarte. An die Version Excel2002/XP mit SP3 komme ich zur Zeit nicht ran, geschrieben hatte ich die Beispielmappe unter Excel2003 mit SP3.

Das geschilderte Verhalten erinnert mich eher an Excel2000 denn an Excel2002/XP.

Sofixultra · 15. Juni 2008

Morschen Jinx,

ich werde es heute mal auf Arbeit testen, da habe ich sowohl E2k, 2002 und 2003 zur Verfügung. Mal schauen ob ich da Unterschiede feststellen kann.

Melde mich dann heute Nachmittag wieder.

Danke für Deine Mühen die Du Dir machst. *hands

jinx · 15. Juni 2008

Moin, Sofixultra,

auch auf dem Rechner mit Excel2002/XP und SP3 kann ich Dein geschildertes Verhalten nicht nachvollziehen (oder ich habe die Vorgehensweise nicht verstanden, was auch sein kann): die Bereiche sind beim Öffnen ohne Makros gesperrt, wenn für die Tabelle ein Schutz bestand, und nach dem Aufheben des Blattschutzes stehen mir dort nur die gewählten Optionen zur Verfügung, nicht aber die Option Gesperrte...

Sofixultra · 15. Juni 2008

Huhu Jinx,

also auf arbeit habe ich nun mit E2002 / SP3 getestet. Das Verhalten wie zuhause tritt hier nicht auf, also ohne Makro sind die Sheets gesperrt. Mit Makro allerdings, wird nur das Blatt Januar frei gegeben, nicht aber Februar...

Bei E2002 / SP3 ist es das selbe.

Anscheinend wird beim Wechsel vom Arbeitsblatt, die Freigabe nicht berücksichtigt, fehlt hier ein Change oder ein Activate Ereigniss?

Ich spiel mal noch ein wenig rum und gebe wieder Rückmeldung ;O)

Grüße

Sofixultra · 15. Juni 2008

Huhu,

ich denke ich habe es rausgefunden ;O)
Habe nun noch
Code:

Public Sub Workbook_SheetActivate(ByVal sh As Object)

akt = sh.Name
Zugriff

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

eingefügt und schon klappt es auch, wenn ich das Sheet wechsle ;O)

Danke Dir vielmals für die Hilfe und wünsche nen sonnigen Tag ;O)

Beste Grüße
Stefan

Office: Zellbereiche sperren für bestimmte User

Zellbereiche sperren für bestimmte User

Zellbereiche sperren für bestimmte User

Zellbereiche sperren für bestimmte User

Zellbereiche sperren für bestimmte User

Zellbereiche sperren für bestimmte User - Similar Threads - Zellbereiche sperren bestimmte

Textblock/Zellbereich aus Tabellenblatt in Worddokument übergeben

Intelligent eTabelle bestimmter Zellbereich markieren VBA

Excel-Zellbereich mit Formatierung aber ohne Formeln nach Visio

Zellbereich Namen per Makro vergeben

Zellbereich als Text ausgeben

VBA Zellbereiche anhand einer Suchvariable in neues Tabellenblatt kopieren

VBA Zellbereiche (Zeilen) verschieben