Makro

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Makro
von: winne
Geschrieben am: 23.03.2005 17:49:38

Hallo Allemiteinander,
ich habe von "Smart Tools" folgendes Makro instaliert:
Voll automatischer Tabellenschutz per Mausklick
Versionen: Excel 97, 2000, 2002/XP und 2003
Wenn Sie eine Tabelle so einrichten möchten, dass andere Anwender möglicht komfortabel damit arbeiten können, nutzen Sie den Tabellenschutz von Excel. Dabei schützen Sie gezielt die Zellen mit Formeln, so dass das versehentliche Ändern oder Löschen verhindert wird. Für Änderungen freigegeben sind ausschließlich Zellen mit Zahlen.
Leider ist das Schutzkonzept von Excel etwas umständlich, denn nach dem Anwählen des Menüs Extras-Schutz-Blatt schützen werden alle Zellen geschützt, für die der Status "Gesperrt" aktiviert wurde (Dialogfeld Format-Zellen, Register "Schutz"). Standardmäßig ist das aber bei allen Zellen der Fall, so dass Sie zunächst einmal gezielt die Zellen auswählen müssen, die nicht geschützt werden sollen. Dann wählen Sie das Menü Format-Zellen an, wechseln auf das Register "Schutz" und schalten das Kontrollkästchen Gesperrt aus.
Damit Sie sich diesen Aufwand in Zukunft ersparen können, möchten wir Ihnen in diesem Tipp der Woche ein interessantes Makro vorstellen:


Sub procFormelzellenSchuetzen()
Dim rngAktiveZelle As Range
  If MsgBox("Möchten Sie alle Formelzellen in diesem " & _
  "Blatt schützen? Der Schutz für Zellen mit Zahlen " & _
  "wird aufgehoben.", vbQuestion + vbYesNo, _
  "Excel Weekly: Formeln schützen") = vbNo Then Exit Sub
  On Error GoTo errorHandling
  ActiveSheet.Unprotect
  For Each rngAktiveZelle In ActiveSheet.UsedRange
    If Not rngAktiveZelle.HasFormula And _
    Not TypeName(rngAktiveZelle.Value) = "Date" And _
    Application.IsNumber(rngAktiveZelle) Then
        rngAktiveZelle.Locked = False
        rngAktiveZelle.Font.ColorIndex = 5
    Else
      rngAktiveZelle.Locked = True
      rngAktiveZelle.Font.ColorIndex = _
      xlColorIndexAutomatic
    End If
  Next
  ActiveSheet.Protect
  Exit Sub
errorHandling:
  MsgBox "Der folgende Fehler ist aufgetreten: " & _
  Err.Number & " - " & Err.Description, vbCritical + _
  vbOKOnly, "Excel Weekly: Formelzellen schützen"
End Sub

Nachdem Sie das Makro eingegeben haben, können Sie ein neues Kalkulationsmodell jederzeit per Mausklick so vorbereiten, dass es an andere Anwender weitergegeben werden kann: Der ausgefüllte Bereich der Tabelle wird Zelle für Zelle geprüft. Bei Zellen mit Formeln oder Datumsangaben wird der Schutz aktiviert und bei Zellen mit Zahlen ausgeschaltet. Zusätzlich werden die ungeschützten Zellen in blau formatiert, so dass auf den ersten Blick zu sehen ist, an welchen Stellen Daten verändert werden können.
Sollten Sie bisher keine Erfahrung mit dem Einsatz von Makros in VBA haben, empfehlen wir diese beiden kostenlosen Techinfos:
Excel-Makros Schritt für Schritt erfassen:
http://www.add-in-world.com/katalog/techinfo-makro/
Excel-Makros optimal verwalten:
http://www.add-in-world.com/katalog/xlmakro/

Optimale Makro-Verfügbarkeit mit Add-Ins
Im folgenden zeigen wir Schritt für Schritt, wie Sie zum Erstellen eines Add-Ins mit Ihren eigenen Makros vorgehen müssen. Sie werden sehen, dass es viel einfacher ist, als Sie bisher dachten.

1. Erstellen Sie eine neue Arbeitsmappe und wechseln Sie mit Alt+F11 in den VBA-Editor.

2. Wenn Sie bereits eine PERSONL.XLS mit Makros angelegt haben, verwenden Sie jetzt den Project-Explorer, um diese Module in die neue Arbeitsmappe zu übernehmen. Ziehen Sie die Module einfach bei gedrückter Strg-Taste in das neue VBA-Projekt.
3. Geben Sie die VBA-Makros und benutzerdefinierten Funktionen in ein oder mehrere Module der neuen Arbeitsmappe ein, die Ihnen in Zukunft immer zur Verfügung stehen sollen.
4. Verlassen Sie den VBA-Editor mit Datei-Schließen und zurück zu Microsoft Excel.

5. Wählen Sie in Excel das Menü Datei-Eigenschaften an. Geben Sie dann in das Feld Titel die Zeichenkette ein, die im Add-In-Manager für Ihr neues Add-In erscheinen soll. Zum Beispiel: "VBA-Bibliothek". Unter Kommentare können Sie noch weitere Anmerkungen eingeben, die beim Markieren des entsprechenden Eintrags ebenfalls im Add-In-Manager erscheinen. Bestätigen Sie Ihre Eingaben mit Ok.
6. Klicken Sie nun das Diskettensymbol an und markieren Sie in der Liste Dateityp den Eintrag "Microsoft Excel-Add-In". In den Versionen 2000 und 2002 wechselt Excel daraufhin automatisch in das "Add-Ins"-Verzeichnis, das bei der Installation angelegt wurde. Es empfiehlt sich, diesen Vorschlag anzunehmen, weil es sich dabei auch um das Verzeichnis handelt, das der Add-In-Manager beim Hinzufügen neuer Add-Ins als erstes anzeigt. In Excel 97 wird zur Speicherung das aktuelle Verzeichnis angeboten. Wechseln Sie auf Wunsch in ein anderes Verzeichnis, das Sie speziell für Add-Ins verwenden möchten.
7. Geben Sie den gewünschten Dateinamen für das Add-In ein und bestätigen Sie mit Speichern.
8. Excel erstellt daraufhin das Add-In aus der aktuellen Arbeitsmappe. Die im ersten Schritt angelegte Datei bleibt erhalten, kann an dieser Stelle aber ohne Speicherung geschlossen werden, weil Sie sie nicht mehr benötigen.
9. Wählen Sie das Menü Extras-Add-Ins-Manager an.

10. Klicken Sie auf Durchsuchen und wählen Sie das soeben erstellte Add-In aus. Excel zeigt es daraufhin in der Liste der verfügbaren Add-Ins im Add-In-Manager mit einem aktivierten Kontrollkästchen an. Beenden Sie den Vorgang mit Ok.
11. Sollten Sie bisher eine persönliche Makro-Arbeitsmappe eingesetzt haben, deren Module nun in das Add-In übernommen wurden, löschen oder verschieben Sie die Datei PERSONL.XLS aus dem XLSTART-Verzeichnis, damit sie nicht mehr automatisch geladen wird.

Alle Makros aus dem Add-In stehen Ihnen in Zukunft automatisch direkt nach dem Start von Excel in allen Arbeitsmappen zur Verfügung. Das gleiche gilt für die benutzerdefinierten Funktionen, bei denen Sie außerdem auf den vorangestellten Dateinamen verzichten können. Weitere nützliche Tipps und Hinweise zu Add-Ins:

· Wenn Sie das Add-In um weitere Makros erweitern oder vorhandene Makros bearbeiten möchten, wechseln Sie einfach in den VBA-Editor und greifen über den Project-Explorer auf die Module zu. Verwenden Sie dann das Disketten-Symbol im Editor, um die Änderungen zu sichern.

· Sie können das Add-In jederzeit aus dem Speicher entfernen, indem Sie den Add-In-Manager aufrufen und das Kontrollkästchen vor dem entsprechenden Eintrag entfernen. Bitte beachten Sie dabei, dass in diesem Fall das Add-In auch beim nächsten Start von Excel nicht mehr geladen wird. Dazu müssen Sie das Kontrollkästchen im Add-In-Manager erst wieder einschalten.

· Für Funktionen und Makros, die Sie nur selten benötigen, können Sie weitere Add-Ins erstellen, die Sie standardmäßig im Add-In-Manager nicht aktivieren und nur bei Bedarf hinzuschalten.
Nun erhalte ich folgende Fehlermeldung: " 1004 - Die Locked-Eigenschaft des
Rande - Objektes kann nicht festgelegt werden ".
Kann ich das Makro benutzen ?
wenn nicht ,was muß ich änder.
Für eure Hinweise
Vielen Dank
und
Frohe Ostern
winne
Bild

Betrifft: AW: Makro
von: Bert
Geschrieben am: 23.03.2005 18:00:37
Stimmt deine angegebene Excelversion wirklich?
Bert
Bild

Betrifft: AW: Makro
von: Peter W
Geschrieben am: 23.03.2005 19:27:35
Servus,
die Excelversion scheint nicht das Problem, hab das Makro ein bisschen auf meine Bedürfnisse angepasst und es in einer neuen Mappe angelegt.
Dort funktioniert es wunderbar, sobald ich es jedoch in eine bereits genutzte Mappe reinkopiere funtzt es nicht mehr (Fehler 1001 s.o.).
Warum kann ich allerdings nicht sagen, deshalb frage offen.
MfG
Peter
P.S.: für jeden dens Intresiert obriges Makro in abhängigkeit von Zellfarbe (hier hellblau).


Sub procFormelzellenSchuetzen()
' Umgeschriebenes Makro von Excel-Weekly
Dim rngAktiveZelle As Range
  If MsgBox("Möchten Sie alle Zellen bis auf die Blau Hinterlegten schützen", vbQuestion + vbYesNo, _
  "Zellen schützen") = vbNo Then Exit Sub
  On Error GoTo errorHandling
  ActiveSheet.Unprotect
  For Each rngAktiveZelle In ActiveSheet.UsedRange
    If rngAktiveZelle.Interior.ColorIndex = 37 Then
        rngAktiveZelle.Locked = False
    Else
      rngAktiveZelle.Locked = True
    End If
  Next
  ActiveSheet.Protect
  Exit Sub
errorHandling:
  MsgBox "Der folgende Fehler ist aufgetreten: " & _
  Err.Number & " - " & Err.Description, vbCritical + _
  vbOKOnly, "Zellen schützen"
End Sub


Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 19:30:03
Hallo Peter,
unter Version 5/7 wurde noch nicht so programmiert. Darum der Hinweis von Bert.

Bitte keine Mail, Probleme sollten im Forum gelöst werden.
Microsoft MVP für Excel
Das Forum lebt auch von den Rückmeldungen.
Betriebssystem XP Home SP2 und Excel Version 2003 SP1.


Bild

Betrifft: AW: Makro
von: Peter W
Geschrieben am: 23.03.2005 19:36:13
Servus Hajo,
da hab ich doch schon wieder was neues Erfahren, muss also nicht dumm sterben.
Nur so ganz versteh ich nicht warums dann unter meine office xp nicht läuft?
Fand das Makro an sich sehr interesant und Zeitsparend. Ne Idee? Danke.
MfG
Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 19:58:17
Hallo Peter,
ich habe mir das Makro nicht so angesehen aber jetzt habe ich mal in die Hilfe und meine Bücher geschaut. Die Hilfe hat nichts über Application.IsNumber und meine Bücher halten sich da auch ein wenig bedeckt. Sie würden das übersetzen mit IstZahl. Darum erscheint mir der Vergleich nicht logisch ich hätte nur
If Not rngAktiveZelle.HasFormula Then
geschrieben.
Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: .isnumber
von: ransi
Geschrieben am: 23.03.2005 20:07:30
hallo
Function IsNumber(Arg1) As Boolean
Element von Excel.IWorksheetFunction
sagt der objectkatalog, und der muss das wissen !
;-)
ransi
Bild

Betrifft: AW: .isnumber
von: Hajo_Zi
Geschrieben am: 23.03.2005 20:11:55
Hallo ransi,
also würde ich das so interpretieren wie meine Literatur schon sagt istZahl
Application.IsNumber(rngAktiveZelle) ergibt True falls Zahl in der Zelle.
Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: AW: Makro
von: Peter W
Geschrieben am: 23.03.2005 20:07:55
Servus Hajo,
erstmal vielen Dank das du sogar Literatur für mich bemühst.
Aber in meiner Version des Makros benutze ich den von dir beschriebenen Codeschnipsel gar nicht mehr, hab in ja umgemodelt in:
If rngAktiveZelle.Interior.ColorIndex = 37 Then
Aus diesem Grund kann dies nicht das Prob sein.
Aber trotzdem nochmal vielen lieben Dank für deine Mühe.
MfG
Peter
Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 20:13:16
Hallo Peter,
mir scheint wir schreiben von unterschiedlichen Zeilen

Sub procFormelzellenSchuetzen()
    Dim rngAktiveZelle As Range
    If MsgBox("Möchten Sie alle Formelzellen in diesem " & _
        "Blatt schützen? Der Schutz für Zellen mit Zahlen " & _
            "wird aufgehoben.", vbQuestion + vbYesNo, _
            "Excel Weekly: Formeln schützen") = vbNo Then Exit Sub
        On Error GoTo errorHandling
        ActiveSheet.Unprotect
        For Each rngAktiveZelle In ActiveSheet.UsedRange
'           Prüfung Formel; Datumswert; IstZahl ??
'            If Not rngAktiveZelle.HasFormula And _
'            Not TypeName(rngAktiveZelle.Value) = "Date" And _
'            Application.IsNumber(rngAktiveZelle) Then
            If Not rngAktiveZelle.HasFormula Then
                rngAktiveZelle.Locked = False   ' Zelle nicht gesperrt
                rngAktiveZelle.Font.ColorIndex = 5
            Else
                rngAktiveZelle.Locked = True
                rngAktiveZelle.Font.ColorIndex = xlColorIndexAutomatic
            End If
        Next
        ActiveSheet.Protect
        Exit Sub
errorHandling:
        MsgBox "Der folgende Fehler ist aufgetreten: " & _
            Err.Number & " - " & Err.Description, vbCritical + _
            vbOKOnly, "Excel Weekly: Formelzellen schützen"
End Sub

Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: AW: Makro
von: Peter W
Geschrieben am: 23.03.2005 20:19:07
Servus Hajo,
im Prinzip nicht, du beziehst das nur auf den Orginal Code.
Ich habe versucht den Code so umzuschreiben das er immer nur die Zellen nicht schützt die mittelblau hinterlegt sind.
Das funktioniert auch einwandfrei und super, nur leider nur in einer neuen Mappe.
Sobald ich das Makro in bereits bestehende Mappen einfüge (ich benutze immer das Schemma das blaue Felder für eingaben genutzt werden),
gibt er den Fehlerwert 1001 zurück.
Und das begreif ich nicht, ist jetzt kein größeres Problem ich wollts halt nur verstehen.
MfG
Peter
Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 20:21:06
Hallo Peter,
dann Poste doch mal Deinen Code. Zu diesem Sachverhalt habe ich ja schon mehrmals geäußert (Glaskugel)
Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: AW: Makro
von: Peter W
Geschrieben am: 23.03.2005 20:24:38
Servus Hajo,
Nur das du siehst das der Vorwurf bei mir diesmal nicht zutrifft:
https://www.herber.de/forum/messages/589851.html
Aber hier nochmal der Code:


Sub procFormelzellenSchuetzen()
' Umgeschriebenes Makro von Excel-Weekly
Dim rngAktiveZelle As Range
  If MsgBox("Möchten Sie alle Zellen bis auf die Blau Hinterlegten schützen", vbQuestion + vbYesNo, _
  "Zellen schützen") = vbNo Then Exit Sub
  On Error GoTo errorHandling
  ActiveSheet.Unprotect
  For Each rngAktiveZelle In ActiveSheet.UsedRange
    If rngAktiveZelle.Interior.ColorIndex = 37 Then
        rngAktiveZelle.Locked = False
    Else
      rngAktiveZelle.Locked = True
    End If
  Next
  ActiveSheet.Protect
  Exit Sub
errorHandling:
  MsgBox "Der folgende Fehler ist aufgetreten: " & _
  Err.Number & " - " & Err.Description, vbCritical + _
  vbOKOnly, "Zellen schützen"
End Sub


Danke für deine Geduld.
MfG
Peter
Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 20:30:35
Hallo Peter,
das habe ich dann wohl überlesen. Dein Makro läuft doch ohne Probleme.
Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: AW: Makro
von: Peter W
Geschrieben am: 23.03.2005 20:36:48
Servus Hajo,
nemma Problema.
In einer neuen Arbeitsmappe Ja, in bereits bestehenden leider Nein.
Und ich wollt halt verstehen warum? Aber tragisch isses nicht :)
MfG
Peter
Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 20:41:34
Hallo Peter,
ich habe es in einer vorhanden Mappe getestet.
Hast Du vielleicht beim Schutz ein Passwort vergeben?
Es gibt schon einen benutzten Bereich?
Die Farbe stimmt schon?
Dies sind eigentlich so die ersten Sachen die man prüft und Du bestimmt auch schon gemacht hast. Ich sehe keinen Grund warum es nicht auch in vorhandenen Mappen gehen sollte.
Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: AW: Makro
von: Peter W
Geschrieben am: 23.03.2005 20:52:57
Servus Hajo,
Für heute hab ich Büroschluß beschäftige mich mal morgen mit dem Prob.
Aber nochmals vielen Dank für deine Geduld.
MfG
Peter
Bild

Betrifft: AW: Makro
von: winne
Geschrieben am: 23.03.2005 20:55:13
Hallo Hajo,
Habe deinen Rat gefolgt, leider ohne Erfolgt
Er ergab einen Syntaxfehler.

Mfg
winne
Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 20:58:06
Hallo Winne,
Dein Makro sieht so aus wie in diesem Beitrag
https://www.herber.de/forum/messages/589876.html
Welche Zellen sollen den bei Dir veränderbar sein. Nach dem Makro alle die keine Formel enthalten.
Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: AW: Makro
von: winne
Geschrieben am: 23.03.2005 21:09:39
Hallo Hajo,
ja das ist richtig, denn wenn ich die Tabelle weitergebe soll diese nicht zerschossen werden und die Formeln erhalten bleiben.
Im übrigen bei jeder Antwort muß ich die Adresse den Namen und den Code eingeben.
Muß das sein oder geht es auch anders.
MfG
winne
Bild

Betrifft: AW: Makro
von: Hajo_Zi
Geschrieben am: 23.03.2005 21:13:34
Hallo Winne,
schaue mal im Technik Forum der Beitrag von Russi
https://www.herber.de/forum/technik/messages/1247.html
Dein Problem ist geklärt?
Ich schalte jetzt meinen Rechner aus, damit sind alle Beispieldateien fort.
Gruß Hajo
Das Forum lebt auch von den Rückmeldungen.
Bild

Betrifft: AW: Makro
von: winne
Geschrieben am: 24.03.2005 11:52:22
Hallo Hajo,
wenn ich ehrlich bin habe ich einiges dazugelernt aber einen Lösungsansatz meines Problems konnte ich nicht herausfiltern.
Sicherlich ist es bei einer Diskussion immer so, daß viele Forummitglieder helfen wollen.
Da ich nicht so oft im Forum war, ist mir die Art und Weise der Problemlösung nicht geläufig.
Es ist auch schwierig aus den Anfragen der Hilfgesuchenden das Richtige zu erkennen, da es sich meistens um keine Profis handelt und den der Wortschatz nicht geläufig ist um sich nicht richtig erklären können.
Nun gut
ich bin ermal zufrieden
und wünsche Allen
ein gesundes Oster.
winne
Bild

Betrifft: AW: Makro
von: winne
Geschrieben am: 23.03.2005 20:00:36
Hallo, vielen Dank für die zahlreiche Anteilnahme.
Ihre habt recht ich habe mich geirrt. Die Version ist w.o. angegeben 9.0-office 2000.
ich hoffe es hilft weiter um mir zuhelfen.
MfG
winne
 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA: Ereignis bei Änderung eines Zelleninhaltes."