Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1200to1204
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

Zellen sperren über VBA

Zellen sperren über VBA
Mirko
Liebe VBA-Experten,
ich wende mich mit folgendem Problem an euch. Ich habe eine Tabelle, in der die Zellen N19:N27 gesperrt werden sollen, sofern die Zelle AH20 (befindet sich auf der gleichen Seite wie die Zellen N19:N27) 2 ist. Wenn die Zelle AH20 nicht 2 ist (also 1 – da sie über eine Droppdown angesteuert wird) ist, dann sollen die Zellen N19, N21, N23, N25 und N27 nicht gesperrt werden. Meine Formel führt leider zu nix. Die Zellen sind immer gesperrt. Was muss ich machen? (Die Funktion unter Zellenformatieren-Schutz ist nicht angeklickt) Ich will den folgenden Makro bei der Navigation einbinden, also wenn ich diese entsprechende Seite aufrufe, dann soll mir der Wert AH20 überprüft werden. Würde mich sehr freuen, wenn mir jemand helfen könnte.
Sub Grunddaten_Neutralwerte()
Application.ScreenUpdating = False
Blattschutz_aus
Sheets("Grunddaten_Neutralwerte").Visible = True
Sheets("Grunddaten_Neutralwerte").Select
Sheets("Anleitung").Visible = False
Sheets("Navigation").Visible = False
Sheets("Grunddaten_Vermoegensklassen").Visible = False
Sheets("Grunddaten_Grenzwerte").Visible = False
Sheets("Simulation").Visible = False
ActiveWindow.Zoom = 85
With Worksheets("Grunddaten_Neutralwerte")
If Range("AH20") = "2" Then
Range("N19:N27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Selection.Locked = False
Selection.FormulaHidden = False
End If
End With
Blattschutz_ein
Range("A1").Select
End Sub
Vielen Dank für eure Hilfe
Möglicherweise ist AH20 nie "2", sondern...
15.02.2011 17:56:09
Luc:-?
…eher 2, also eine echte, keine Textzahl wie in deinem Makro, Mirko… ;->
Gruß Luc :-?
AW: Möglicherweise ist AH20 nie "2", sondern...
15.02.2011 18:06:08
Mirko
und das heißt? Wie muss ich mein Makro schreiben, damit die 2, die ich augenscheinlich in Zelle AH20 sehe, auch vom Makro als solche erkannt wird?
2 ist auch im Code 2 und nicht "2", das...
15.02.2011 18:47:23
Luc:-?
…macht man nur mit Texten, Mirko.
Außerdem war Gerold nicht konsequent, den 1.Teil des If…Else…-Konstrukts hätte er auch ändern sollen…
     If Range("AH20") = 2 Then
Bereich.Locked = True
Bereich.FormulaHidden = False
Erich hat ja dann schon das Übrige getan…!
Dein HptFehler bestand darin, überflüssigerweise zu selektieren, und das nicht vor dem Konstrukt, sondern nur in seinem 1.Teil. Im Else-Teil fehlte das, so das, wenn mit diesem Teil begonnen wird, auch nichts selektiert wird.
Gruß Luc :-?
Anzeige
eine 2 ist eine 2 oder eine "2"
15.02.2011 19:28:51
Erich
Hi Mirko,
die 2, die du in Zelle AH20 siehst, kann eine Zahl sein, sie kann aber auch ein Text mit einem Zeichen sein.
Mit den Augen kannst du das nicht so einfach unterscheiden.
Schreib doch mal in irgend eine freie Zelle die Formel
=ISTZAHL(AH17)
oder
=ISTTEXT(AH17)
Die Funktionen werden dir sagen, was da in der Zelle steht. Wir können das nicht wissen.
Wenn es ein Text ist, solltest du auch im Code mit Text arbeiten, also "2" schreiben.
Wenn es eine Zahl ist, solltest du auch im Code mit der Zahl 2 ohne Gänsefüße arbeiten.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Möglicherweise ist AH20 nie "2", sondern...
15.02.2011 18:06:38
Gerold
Hallo Mirko
Wie wärs hiermit.

Sub Grunddaten_Neutralwerte()
Dim Bereich As Range
Application.ScreenUpdating = False
Blattschutz_aus
Sheets("Grunddaten_Neutralwerte").Visible = True
Sheets("Grunddaten_Neutralwerte").Select
Sheets("Anleitung").Visible = False
Sheets("Navigation").Visible = False
Sheets("Grunddaten_Vermoegensklassen").Visible = False
Sheets("Grunddaten_Grenzwerte").Visible = False
Sheets("Simulation").Visible = False
ActiveWindow.Zoom = 85
Set Bereich = Union(Range("N19"), Range("N21"), Range("N23"), _
Range("N25"), Range("N27"))
With Worksheets("Grunddaten_Neutralwerte") '
If Range("AH20") = "2" Then
Range("N19:N27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
End With
Blattschutz_ein
Range("A1").Select
End Sub
Mfg Gerold
Rückmeldung wäre nett.
Anzeige
AW: Möglicherweise ist AH20 nie "2", sondern...
15.02.2011 18:16:59
Mirko
Gerold, Du bist der Held!!! Vielen vielen Dank!!! Hast mir sehr gut geholfen! Ich wünschte, man könnte hier Sterne für die Beiträge vergäben - dann hättest du jetzt ***** Stern!
Beste Grüße
Mirko
Bemerkungen zum Code
15.02.2011 18:31:00
Erich
Hi zusammen,
statt
Set Bereich = Union(Range("N19"), Range("N21"), Range("N23"), Range("N25"), Range("N27"))
kann man auch einfach schreiben:
Set Bereich = Range("N19,N21,N23,N25,N27")
Die With-Klammer
With Worksheets("Grunddaten_Neutralwerte") ... End With
wird überhaupt nicht genutzt. Sie ist hier auch überflüssig, weil oben mit
Sheets("Grunddaten_Neutralwerte").Select
das Blatt zum aktiven Blatt wurde.
Die With-Klammer wirkt auf Ausdrücke, die mit einem Punkt beginnen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Bemerkungen zum Code
15.02.2011 18:35:28
Mirko
Hallo Erich,
danke für die Optimierung, das Makro funktioniert mit deiner Reduktion weiterhin wie gewünscht!
Vielen Dank für eure Unterstützung!!!
Mirko
AW: Möglicherweise ist AH20 nie "2", sondern...
15.02.2011 18:30:51
Mirko
Hallo Gerold,
wenn ich jetzt mehrere Zellen prüfen will, kann ich das so schreiben? An sich funtkioniert es perfekt, ich frage mich nur, ob man das nun auch kürzer schreiben könnte.
Sub Grunddaten_Neutralwerte()
Dim Bereich As Range
Application.ScreenUpdating = False
Blattschutz_aus
Sheets("Grunddaten_Neutralwerte").Visible = True
Sheets("Grunddaten_Neutralwerte").Select
Sheets("Anleitung").Visible = False
Sheets("Navigation").Visible = False
Sheets("Grunddaten_Vermoegensklassen").Visible = False
Sheets("Grunddaten_Grenzwerte").Visible = False
Sheets("Simulation").Visible = False
ActiveWindow.Zoom = 85
Set Bereich = Union(Range("H19"), Range("H21"), Range("H23"), _
Range("H25"), Range("H27"))
With Worksheets("Grunddaten_Neutralwerte") '
If Range("AH17") = "2" Then
Range("H19:H27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
End With
Set Bereich = Union(Range("J19"), Range("J21"), Range("J23"), _
Range("J25"), Range("J27"))
With Worksheets("Grunddaten_Neutralwerte") '
If Range("AH18") = "2" Then
Range("J19:J27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
End With
Set Bereich = Union(Range("L19"), Range("L21"), Range("L23"), _
Range("L25"), Range("L27"))
With Worksheets("Grunddaten_Neutralwerte") '
If Range("AH19") = "2" Then
Range("L19:L27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
End With
End Sub

Anzeige
Codevorschlag
15.02.2011 19:04:30
Erich
Hi Mirko,
das geht vielleicht so (ungetestet!):

Option Explicit
Sub Grunddaten_Neutralwerte()
Dim strAdrA(1 To 3) As String, strAdrB(1 To 3) As String
Dim ii As Integer
Application.ScreenUpdating = False
Blattschutz_aus
Sheets("Grunddaten_Neutralwerte").Visible = True
Sheets("Grunddaten_Neutralwerte").Select
Sheets("Anleitung").Visible = False
Sheets("Navigation").Visible = False
Sheets("Grunddaten_Vermoegensklassen").Visible = False
Sheets("Grunddaten_Grenzwerte").Visible = False
Sheets("Simulation").Visible = False
ActiveWindow.Zoom = 85
strAdrA(1) = "H19:H27"
strAdrA(2) = Replace(strAdrA(1), "H", "J")
strAdrA(3) = Replace(strAdrA(1), "H", "L")
strAdrB(1) = "H19,H21,H23,H25,H27"
strAdrB(2) = Replace(strAdrB(1), "H", "J")
strAdrB(3) = Replace(strAdrB(1), "H", "L")
For ii = 1 To 3
If Cells(16 + ii, 34) = "2" Then    ' AH17, AH18, AH19
With Range(strAdrA(ii))          ' H19:H27 usw.
.Locked = True
.FormulaHidden = False
End With
Else
With Range(strAdrB(ii))    ' H19,H21,H23,H25,H27 usw.
.Locked = False
.FormulaHidden = False
End With
End If
Next ii
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Codevorschlag
15.02.2011 19:26:57
Mirko
Hallo Erich,
funktioniert leider noch nicht. Die entsprechenden Zellen sind nicht gesperrt, wenn AH17 oder die anderen AH-Werte 2 aufweisen. Ich vermute, das liegt daran, dass der meine AH-Werte nicht findet. Zumindest finde ich im Makro keinen Hinweis darauf, wann er in die AH-Werte schauen soll.
Deine ursprüngliche Zeile: If Cells(16 + ii, 34) = "2" Then
habe ich wie folgt angepasst: If Cells(17 + ii, 28) = "2" Then (müsste so sein oder?, für AH17, AH18,…AH28)
Erweiterung?
15.02.2011 19:44:58
Erich
Hi Mirko,
deine Änderung war leider falsch.
In der Zeile:
If Cells(16 + ii, 34) = "2" Then
steht die 34 für die Spalte AH. AH ist eben die 34. Spalte im Tabellenblatt.
Du willst hier in Spalte AH bleiben - die 34 muss also stehen bleiben.
ii löäuft in meinem Code von 1 bis 3 - für die Spalten H, J und L.
Bei ii = 1 soll AH17 abgefragt werden - das ist Cells(16 + ii, 34)
Bei ii = 2 soll AH18 abgefragt werden - das ist Cells(16 + ii, 34) usw.
"Zumindest finde ich im Makro keinen Hinweis darauf, wann er in die AH-Werte schauen soll."
Sicher? Gibt nicht der Kommentar in dieser Zeile einen Hinweis?
If Cells(16 + ii, 34) = "2" Then ' AH17, AH18, AH19
Neu ist mir, dass jetzt nicht nur AH17, AH18, AH19 auf 2 (oder "2") geprüft werden sollen,
sondern AH17, AH18, ... AH28. Das kam bisher noch nicht vor.
Soll das bedeuten, dass nicht nur die Spalten H, J und L, sondern deren Fortsetzungen nach rechts
bearbeitet werden sollen?
Bislang sind in den Arrays und der Schleife 3 Durchläufe vorgesehen. Wenn das viele werden,
wird man die Belegung der strAdrA bzw. strAdrB anders codieren, evtl. nicht mit Replace().
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Erweiterung?
15.02.2011 20:14:25
Mirko
Hallo Erich,
anbei die komplette Makro, wie sie momentan auch perfekt funktioniert. Du hast Recht, in der anfänglichen Formel war bislang die Rede von AH17, AH18, AH19. Ich habe schon die anderen Spalten schon in Gedanken gehabt, deshlab die AH28. Sorry wenn ich zur Verwirrung geführt habe. Hinsichtlich der auskommentierten Hinweisen ' AH17, AH18, AH19, hast Du natürlich recht, dass damit die AH´s gemeint sind, aber woher weiß das System das, wenn diese auskommentiert sind? Zumindest dachte ich, dass ein auskommentierter Hinweis, nur für mich als User gedacht ist.
Funktioniert bei Dir der ursprüngliche Code (der vereinfachte)? Bei mir waren die Zellen weiterhin offen.
Vielen vielen Dank für Deine Unterstützung!
Sub Grunddaten_Neutralwerte2()
Dim Bereich As Range
Application.ScreenUpdating = False
Blattschutz_aus
Sheets("Grunddaten_Neutralwerte").Visible = True
Sheets("Grunddaten_Neutralwerte").Select
Sheets("Anleitung").Visible = False
Sheets("Navigation").Visible = False
Sheets("Grunddaten_Vermoegensklassen").Visible = False
Sheets("Grunddaten_Grenzwerte").Visible = False
Sheets("Simulation").Visible = False
ActiveWindow.Zoom = 85
Set Bereich = Range("H19,H21,H23,H25,H27")
If Range("AH17") = "2" Then
Range("H19:H27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("J19,J21,J23,J25,J27")
If Range("AH18") = "2" Then
Range("J19:J27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("L19,L21,L23,L25,L27")
If Range("AH19") = "2" Then
Range("L19:L27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("N19,N21,N23,N25,N27")
If Range("AH20") = "2" Then
Range("N19:N27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("P19,P21,P23,P25,P27")
If Range("AH21") = "2" Then
Range("P19:P27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("R19,R21,R23,R25,R27")
If Range("AH22") = "2" Then
Range("R19:R27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("T19,T21,T23,T25,T27")
If Range("AH23") = "2" Then
Range("T19:T27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("V19,V21,V23,V25,V27")
If Range("AH24") = "2" Then
Range("V19:V27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("X19,X21,X23,X25,X27")
If Range("AH25") = "2" Then
Range("X19:X27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("Z19,Z21,Z23,Z25,Z27")
If Range("AH26") = "2" Then
Range("Z19:Z27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("AB19,AB21,AB23,AB25,AB27")
If Range("AH27") = "2" Then
Range("AB19:AB27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Set Bereich = Range("AD19,AD21,AD23,AD25,AD27")
If Range("AH28") = "2" Then
Range("AD19:AD27").Select
Selection.Locked = True
Selection.FormulaHidden = False
Else
Bereich.Locked = False
Bereich.FormulaHidden = False
End If
Blattschutz_ein
Range("A1").Select
End Sub

Anzeige
Ich sehe in deinem Code immer noch einigen...
15.02.2011 21:19:38
Luc:-?
…von uns bereits monierten Code, Mirko!
Entweder hast du etwas den Überblick verloren oder bist beratungsresistent…
Luc :-?
Kommentar
16.02.2011 08:00:03
Erich
Hi Mirko,
einen neuen Code habe ich ja gestern schon gepostet. Hier nur zum Thema "Kommentar". Du schriebst:
"..., aber woher weiß das System das, wenn diese auskommentiert sind?
Zumindest dachte ich, dass ein auskommentierter Hinweis, nur für mich als User gedacht ist."
Dem System (oder VBA) ist ziemlich egal, was in Kommentaren steht. Es muss davon nichts wissen.
Wenn ein Programm ok ist, versteht VBA das - ohne jegliche Kommentare.
VBA führt einfach Anweisungen aus - ohne irgendwelche Hinweise in Kommentaren zu berücksichtigen. Wie auch?
VBA verarbeitet
If Cells(16 + ii, 34) = "2" Then
Dahinter hatte ich für dich geschrieben: ' AH17, AH18, AH19
Zusammen mit der "2" hätte dir klar werden können, dass hier die Überprüfung der AH-Zellen auf "2" steht.
Also: Ja, der Kommentar sollte ein Hinweis für dich sein!
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
neuer Codevorschlag
15.02.2011 20:11:37
Erich
Hi Mirko,
dieser Code prüft AH17 bis AH28 und bearbeitet Spalten von H bis AD:

Sub Grunddaten_Neutralwerte()
Dim strAdrA As String, strAdrB As String
Dim ii As Integer
'   Application.ScreenUpdating = False
'   Blattschutz_aus
'   Sheets("Grunddaten_Neutralwerte").Visible = True
'   Sheets("Grunddaten_Neutralwerte").Select
'   Sheets("Anleitung").Visible = False
'   Sheets("Navigation").Visible = False
'   Sheets("Grunddaten_Vermoegensklassen").Visible = False
'   Sheets("Grunddaten_Grenzwerte").Visible = False
'   Sheets("Simulation").Visible = False
'   ActiveWindow.Zoom = 85
strAdrA = "H19:H27"                 ' bis AD19:AD27
strAdrB = "H19,H21,H23,H25,H27"     ' bis AD19,AD21,...
For ii = 17 To 28                               ' Zeilen von AH
If Cells(ii, 34) = 2 Then                    ' AH17, AH18, ...
With Range(strAdrA).Offset(, 2 * (ii - 17))  ' H19:H27 ...
.Interior.ColorIndex = 5
.Locked = True
.FormulaHidden = False
End With
Else
With Range(strAdrB).Offset(, 2 * (ii - 17)) ' H19,H21, ...
'            .Interior.ColorIndex = 3
.Locked = False
.FormulaHidden = False
End With
End If
Next ii
End Sub
Oben habe ich einiges auskommentiert, was ich hier nicht gebrauchen kann.
Ich habe deine Blätter nicht, muss sie ja auch nicht haben.
Unten habe ich die Zellen färben lassen - damit man leicht sehen kann, wo was passiert.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: neuer Codevorschlag
16.02.2011 21:13:31
Mirko
Hallo Erich,
und wie ich mir das angesehen habe. Habe alles implementiert und funktioniert super. Hast Du meine Rückmeldung etwa nicht erhalten? Ich habe heute morgen zurückgeschrieben. Vielen Dank, so wie bei dieser Anfrage (vorhin) fehlt mir manchmal noch das Verständnis wie ich am besten etwas erweitere. Die Hilfe hier ist echt super!!!
Vielen Dank nochmal!
Mirko
Rückmeldung nicht gefunden
17.02.2011 01:30:52
Erich
Hi Mirko,
"Hast Du meine Rückmeldung etwa nicht erhalten? Ich habe heute morgen zurückgeschrieben."
Nein, habe ich leider nicht gefunden. Kannst du mir mal Datum ( wohl 16.02.) und Uhrzeit deiner Antwort aufschreiben?
Oder den genauen Betreff? Vielleicht find ichs dann...
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Da gibt's nichts, Erich, aber das weißt du ja! ;-)
17.02.2011 03:16:29
Luc:-?
…Wahrscheinl meint er die AW vom 15. oder gar den Dank an Rudi im neuen Thread…?! ;->>
Oder sollte er dir 'ne private Mail geschickt haben…?
Gruß Luc :-?
...übrigens hat er in den letzten 3 Threads...
17.02.2011 03:21:04
Luc:-?
…auch keinen einzigen Beitrag morgens geschrieben, Erich,
wenn man mal von 00:17h absieht…
Luc :-?

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige