Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1348to1352
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formelabhängige Zellen als Wert abspeichern

Formelabhängige Zellen als Wert abspeichern
02.03.2014 11:34:33
Peter
Hallo zusammen,
leider habe ich nur geringe VBA Kenntnisse und würde mich über Hilfe von Euch freuen.
Funktionsbeschreibung der Arbeitsmappe:
Ich habe eine Arbeitsmappe, die aus zwei Blättern besteht.
In dem Blatt Berechtigungen wird der aktuelle Benutzer ausgelesen:
--------------------------------------------
Private Sub Workbook2_open()
Sheets("Berechtigungen").Unprotect "Passwort"
'UserName Windows
'Range("Berechtigungen!B2")=Environ("UserName")
'UserName Excel
Range("Berechtigungen!B2") = Application.UserName
Sheets("Berechtigungen").Protect "Passwort"
End Sub

--------------------------------------------
Die Rollen des Benutzers werden dann im Blatt Berechtigungen ausgelesen und in Berechtigungen!$B$4, Berechtigungen!$B$5, Berechtigungen!$B$6, Berechtigungen!$B$7 gespeichert. Ebenfalls wird der richtige Name zugeordnet (Berechtigungen!$B$3). Das Blatt Berechtigungen ist komplett geschützt.
Das Blatt Aktionen ist das eigentliche Blatt, in welches die Benutzer den Arbeitsstatus ankreuzen können. Dieses Blatt ist ebenfalls geschützt bis auf die Ankreuzzellen. Diese Zelle haben eine Gültigkeitsoption, in der sie überprüfen, ob der aktuelle Benutzer der das Kreuz setzt auch berechtigt ist, das "x" zu setzen. Die daneben benachbarten Zellen tragen, wenn ein "x" gesetzt wurde Name und Datum ein.
Kreuz (J14) Daten>Gültigkeit
=UND(ODER(WENNFEHLER(SUCHEN(Berechtigungen!$B$4; B14);0)=1; WENNFEHLER(SUCHEN(Berechtigungen!$B$5; B14);0)=1;WENNFEHLER(SUCHEN(Berechtigungen!$B$6; B14);0)=1;WENNFEHLER(SUCHEN(Berechtigungen!$B$7; B14);0)=1);J20="x";C20="Holz")
Name (K14): =WENN(ISTLEER(J14);"";Berechtigungen!$B$3)
Datum (L14): =WENN(K14="";"";HEUTE())
Danach wird durch ein VBA Makro die Zellen mit den Kreuzen nach dem Speichern ebenfalls gesperrt.
Es gibt mehrere Ankreuzspalten mit Name und Datum.
--------------------------------------------
Option Explicit
Private bRangeEdited As Boolean
Private WithEvents ws As Worksheet
Private Sub Workbook_open()
Set ws = Range("Aktionen!A1:Aktionen!AF45").Parent
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sMSG As String
sMSG = "Das Speichern der Datei schützt Ihre Eingaben vor nachträglicher Veränderung!" &  _
vbLf
sMSG = sMSG & "Möchten Sie fortfahren?"
If Not bRangeEdited Then GoTo Xit
If Not Me.ReadOnly Then
With Range("Aktionen!A1:Aktionen!AF45")
If MsgBox(sMSG, vbExclamation + vbYesNo) = vbNo Then
Cancel = True
GoTo Xit
End If
.Parent.Unprotect "Passwort"
If .SpecialCells(xlCellTypeBlanks).Address  .Address Then
.SpecialCells(xlCellTypeConstants).Locked = True
bRangeEdited = False
End If
.Parent.Protect "Passwort"
End With
End If
Xit:
End Sub
Private Sub ws_Change(ByVal Target As Range)
If Not Intersect(Range("Aktionen!A1:Aktionen!AF45"), Target) Is Nothing Then
bRangeEdited = True
End If
End Sub

--------------------------------------------
Problembeschreibung:
Alles funktioniert so weit. Allerdings, wenn ein anderer Benutzer die Datei öffnet, geht natürlich die Beziehung zu den abhängigen Zellen (Name, Datum) verloren. In dem Blatt Aktionen kann man die Eintragungen vom vorherigen Benutzer nicht mehr korrekt sehen (#NV). Der Bezug fehlt jetzt.
Meine Überlegung wäre gewesen, dass die Zellen, welche Name und Datum enthalten nach dem Ankreuzen und Speichern in WERTE umgewandelt werden. Ist das mit VBA möglich? Ich habe schon viel gesucht, aber leider nichts gefunden.
Vielleicht gibt es ja auch eine andere Lösung.
Für Hilfe bin ich schon im Voraus dankbar, leider eilt es ein wenig ;(
Schöne Grüße
Peter

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formelabhängige Zellen als Wert abspeichern
03.03.2014 11:46:12
fcs
Hallo Peter,
du musst hier die Workbook_BeforeSave-Ereignis-Prozedur anpassen.
Die Zeilen müssen vor dem Speichern in allen Spalten mit "x"-Einträgen geprüft werden, ob rechts daneben der Name des angemeldeten Anwenders per Formel angezeigt wird. Falls Ja, dann in entsprechenden Zellen die Formeln durch Werte ersetzen.
Ich hab mal auf Basis deiner Beschreibung versucht das Makro anzupassen. Dies musst du noch etwas ergänzen/anpassen bezüglich der Spalten mit den "x"-Werten.
Gruß
Franz
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sMSG As String
Dim lZeile As Long, lSpalte As Long, sName As String
sMSG = "Das Speichern der Datei schützt Ihre Eingaben vor nachträglicher Veränderung!" _
& vbLf
sMSG = sMSG & "Möchten Sie fortfahren?"
If Not bRangeEdited Then GoTo Xit
If Not Me.ReadOnly Then
With Range("Aktionen!A1:Aktionen!AF45")
If MsgBox(sMSG, vbExclamation + vbYesNo) = vbNo Then
Cancel = True
GoTo Xit
End If
.Parent.Unprotect "Passwort"
With .Parent
sName = Worksheets("Berechtigung").Range("B3").Value
For lZeile = 1 To 45
For lSpalte = 1 To 32 'Spalten A bis AF
'Spalten mit X-Einträgen prüfen
Select Case lSpalte
Case 10, 14 'Spalten J und N, hier ggf. Spalten mit "X" anpassen/ergänzen
With .Cells(lZeile, lSpalte)
'prüfen, ob "x" in Zelle und Name des angemeldeten Users _
in rechter Nachbarzelle per Formel eingetragen sind
If LCase(.Value) = "x" _
And .Offset(0, 1).Value = sName _
And .Offset(0, 1).HasFormula = True Then
'in rechten Nachbarzellen mit Name/Datum die Formeln durch Werte  _
ersetzen
With .Range("B1:C1")
.Value = .Value
End With
End If
End With
Case Else
'do nothing
End Select
Next lSpalte
Next lZeile
End With
If .SpecialCells(xlCellTypeBlanks).Address  .Address Then
.SpecialCells(xlCellTypeConstants).Locked = True
bRangeEdited = False
End If
.Parent.Protect "Passwort"
End With
End If
Xit:

Anzeige
AW: Formelabhängige Zellen als Wert abspeichern
03.03.2014 13:38:59
Peter
Hallo Franz,
vielen Dank für deine Hilfe! Ich bin mit VBA leider nicht sehr vertraut und habe mir den bisherigen Code zusammen gegoogelt. Daher noch ein paar blöde Fragen.
Bei mir stehen in den Spalten J, N, S, X, AC die "x". Trage ich dann Case 10, 14, 19, 24 ,29 ein?
Bedeutet With .Range("B1:C1"), dass alle Zellen welche ein "x" haben und sich in den in Case genannten Spalten befinden, dass dann die nächste und übernächste Zelle (nach rechts) auf Formeln durchsucht wird?
Schöne Grüße, Peter

AW: Formelabhängige Zellen als Wert abspeichern
03.03.2014 17:51:42
fcs
Hallo Peter,
Bei mir stehen in den Spalten J, N, S, X, AC die "x". Trage ich dann Case 10, 14, 19, 24 ,29 ein?
Ja!
Bedeutet With .Range("B1:C1"), dass alle Zellen welche ein "x" haben und sich in den in Case genannten Spalten befinden, dass dann die nächste und übernächste Zelle (nach rechts) auf Formeln durchsucht wird?
Auf eine Formel in der rechten Nachbarzelle wird eine Zeile Oberhalb geprüft
                            And .Offset(0, 1).HasFormula = True Then

Ich denke, dass man hir nicht beide Nachbarzellen auf Formel prüfen muss.
Mit der "With .Range("B1:C1")" wird hier festgelegt, in welchen Zellen relativ zu einer Zelle mit einem "X" in der nächsten Zeile die Formeln durch ihre Ergebnisse ersetzt werden. Die Zelle mit dem "X" ist hier dann Zelle A1.
Ich hoffe, das hilft weiter.
Ansonsten: Auch mal etwas Mut zeigen und einfach mal in einer Kopie der Datei den neuen Code testen.
Gruß
Franz

Anzeige
AW: Formelabhängige Zellen als Wert abspeichern
05.03.2014 20:21:24
Peter
Hallo Franz,
dein Makro funktioniert wunderbar!!! Vielen herzlichen Dank für deine Hilfe!!!
Jetzt wo dies alles funktioniert, ist mir aufgefallen, dass ein Teil meines Codes nicht funktioniert.
Der Benutzername wird beim Öffnen der Datei nicht aktualisiert. Erst wenn ich das Makro manuell starte (über Debugger) wird der Name aktualisiert. Hast du eine Idee warum das so ist?
Private Sub Workbook2_open()
Sheets("Berechtigungen").Unprotect "Passwort"
'UserName Windows
'Range("Berechtigungen!B2")=Environ("UserName")
'UserName Excel
Range("Berechtigungen!B2") = Application.UserName
Sheets("Berechtigungen").Protect "Passwort"
End Sub

Schöne Grüße, Peter

Anzeige
AW: Formelabhängige Zellen als Wert abspeichern
06.03.2014 09:26:06
fcs
Hallo Peter,
der Name des Makros ist nicht korrekt.
Der Name des beim Öffnen der Datei automatisch auszuführenden Ereignis-Makros ist immer
Private Sub Workbook_Open()
Dieses Makro muss im VBA-Editor immer unter "DieseArbeitsmappe" für die Datei angelegt werden.
Gruß
Franz

AW: Formelabhängige Zellen als Wert abspeichern
06.03.2014 09:46:24
Peter
Hallo Franz,
wieder einmal funktioniert deine Lösung auf Anhieb. Vielen vielen Dank dafür!
Schöne Grüße,
Peter

303 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige