Zum Inhalt springen
Frage 2. Oktober 2012 · 25 Antworten

VBA Excel2010 condicional formating

Hallo zusammen,

ich habe ein VBA-Excel Programm das mehrere Codes für ein bestimmtes Worksheet hat.
Diese möchte ich aber auch auf andere Worksheets verwenden.
Einer davon wäre, das ich in einem Blatt über eine UF ein Standardmass eingeben will und daneben die Toleranz, damit mir das Programm selber Ober- und Untergrenze ermittlet und demensprechend Eingaben entweder schwarz (in der Toleranz) oder rot (ausserhalb der Toleranz) einfärbt.

Mein bisheriger Code war der Folgende:
With Worksheets(„xy“).Range(„A1:Z5“).FormatConditions _
.Add(Type:=xlValidateWholeNumber, _
Operator:=xlNotBetween, Formula1:=„148,70“, Formula2:=„149,30“)
With .Font
.Italic = True
.ColorIndex = 3
End With
End With

Hier möchte ich also die Formula1 und 2 variabel halten, weiss aber leider nicht wie!!
Ich freue mich sehr wenn mir da jemand helfen kann!!!
Danke!

Grüße

Diese Frage stammt aus dem Jahr 2012. Weißt du es heute besser?

Aktuelle Antwort ergänzen

25 Antworten

Würde es mal damit versuchen:

Dim dblF1 as Double, dblF2 as Double
With Worksheets(„xy“).Range(„A1:Z5“).FormatConditions _
.Add(Type:=xlValidateWholeNumber, _
Operator:=xlNotBetween,
Formula1:= dblF1, Formula2:= dblF2)
With .Font
.Italic = True
.ColorIndex = 3
End With
End With

Grüsse
Sebastian

PS: Sollte es nicht gehen, dann: CStr(dblF1) bzw. CStr(dblF2)

0 Hilfreich
Antwort auf Sebra7zum Beitrag ↑

Würde es mal damit versuchen: Dim dblF1 as Double, dblF2 as Double With Worksheets(„xy“).Range(„A1:Z5“).FormatConditions _ .Add(Type:=xlValidateWholeNumber, _ Operator:=xlNotBetween, Formula1:= dblF1, Formula2:= dblF2) W …

Hallo Sebastian,

leider haben beide Varianten nicht funktioniert.
Vielleicht habe ich einen Fehler gemacht oder was übersehen (bin nämlich keine VBA-Kennerin)… Aber ich glaube ich habe vielleicht mein Problem nicht so gut beschreiben können:
Ich habe eine UF mit mehreren TextBoxen in denen ich Maße eingeben kann. Ein Maß ist immer das Soll-Maß und das andere die Toleranz.
Was das Programm machen soll ist dieses Soll-Maß aus der Textbox nehmen und einmal die Toleranz abziehen und einmal addieren, damit ich die Ober- und Untergrenze habe.
Mit diesen Werten möchte ich dann in Fomula1 und 2 arbeiten.
In dem Code den ich nämlich in meine Frage kopiert habe, sind die Ober- und Untergrenze nämlich immer fix, und das möchte ich eben ändern, da ich immmer wieder neue Werte benötige…

Was kann ich da machen??

Liebe Grüße,
Sonja

0 Hilfreich

Sorry, ich hab’s eher mit Word.

0 Hilfreich

Hallo Sonja,

wenn du statt „Worksheets(„xy“).“ „ActiveSheet.“ verwendest, dann funktioniert es auf jedem ausgewählten Worksheet.

Gruß
Natator

0 Hilfreich

Grüezi Sonja

Mein bisheriger Code war der Folgende:
With Worksheets(„xy“).Range(„A1:Z5“).FormatConditions _
.Add(Type:=xlValidateWholeNumber, _
Operator:=xlNotBetween, Formula1:=„148,70“,
Formula2:=„149,30“)
With .Font
.Italic = True
.ColorIndex = 3
End With
End With

Hier möchte ich also die Formula1 und 2 variabel halten, weiss
aber leider nicht wie!!

Das könntest Du z.B. über je eine InputBox machen, in der diese beiden Werte abgefragt und dann im Code verwendet werden.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
0 Hilfreich

Hallo,

Mein bisheriger Code war der Folgende:
With Worksheets(„xy“).Range(„A1:Z5“).FormatConditions

http://ms-excel.eu/vba/vbaworksheets/vbatabellenblat…

„xy“ ersetzen, was in der oben angeführten Seite steht.

Gruß

0 Hilfreich

Hallo

Ja, um etwas aus eine UF einzulesen benötigst du folgendes:
dim strtxt as string;
strtxt = ufname.meinetextbox.value
if strtxt=„“ then
msgbox „Eingabefehler“,critical,„Fehler“
exit sub
end if
Grüsse Sebastian

0 Hilfreich

Hallo Sonja,

dafür reichen meine VBA-Kenntnisse leider noch nicht aus.

gruß Hugo

0 Hilfreich

Hallo Sonja,

hier ein angepasster Code, wenn die Werte für Maß und Toleranz in Textboxen eines Userforms eingegeben werden.
Die Namen der Textboxen muss du ggf. im VBA-Editor anpassen.

Gruß
Franz

'Code im Userform-Module
Private Sub CommandButton1\_Click()
 'Zellbereich A1:Z5 im aktiven Tabellenblatt gemäß Eingaben im Userform \_
 für Wert und Toleranz bedingt formatieren
 'Werte außerhalb Toleranz werden kursiv/rot angezeigt
 Dim wks As Worksheet
 Dim varWert1, varWert2

 Set wks = ActiveSheet
' Set wks = Worksheets("TabelleXYZ")

 'Textboxen auf Zahlenwerte prüfen
 If IsNumeric(Me.TextBox\_Mass) And IsNumeric(Me.TextBox\_Toleranz) Then
 'untere Grenze
 varWert1 = CDbl(Me.TextBox\_Mass) - CDbl(Me.TextBox\_Toleranz)
 'obere Grenze
 varWert2 = CDbl(Me.TextBox\_Mass) + CDbl(Me.TextBox\_Toleranz)
 With wks.Range("A1:Z5")
 .FormatConditions.Delete
 With .FormatConditions.Add(Type:=xlCellValue, \_
 Operator:=xlNotBetween, Formula1:=varWert1, Formula2:=varWert2)
 With .Font
 .Italic = True
 .ColorIndex = 3
 End With
 End With
 End With
 Me.Hide
 Else
 MsgBox "Für Wert und Toleranz muss eine Zahl eingeben werden!", \_
 vbInformation + vbOKOnly, "Bedingte Formatierung Zellbereich A1:Z5"
 End If
End Sub
0 Hilfreich

Hallo *Sonja*

das musst du über Change-Event des Worksheets machen.

Private Sub Worksheet\_Change(ByVal Target As Range)
 'hier aufruf deiner Methode
End Sub

MfG.
W.W.

0 Hilfreich

hallo sonja,

wenn ich dich richtig verstehe, sollen 148,70 und 149,30 vom anwender verändert werden können, ohne das vba-programm zu ändern. dafür wäre folgender code geeignet; min und max stehen in den zellen a7 und a8:

With Worksheets(„xy“).Range(„A1:Z5“).FormatConditions _
.Add(Type:=xlValidateWholeNumber, _
Operator:=xlNotBetween, Formula1:=„=$A$7“, Formula2:=„=$A$8“)
With .Font
.Italic = True
.ColorIndex = 3
End With
End With

viel erfolg, ascan

Antwort auf Franz_e6478dzum Beitrag ↑

Hallo Sonja, hier ein angepasster Code, wenn die Werte für Maß und Toleranz in Textboxen eines Userforms eingegeben werden. Die Namen der Textboxen muss du ggf. im VBA-Editor anpassen. Gruß Franz 'Code im Userform-Module …

Hallo Franz,

du hast mir wirklich sehr geholfen!!
Funktioniert einwandfrei! =)
Vielen lieben Dank!!!

Vielleicht kannst du mir noch bei zwei anderen - für dich wahrscheinlich - Kleinigkeiten helfen:
Ich habe Textboxen in die ich Zahlen eingebe. Jetzt muss ich aber immer einen Punkt für die Kommastellen eingeben statt ein Komma. Ich hab schon mit der Ländereinstellung herumgepfuscht, aber es will immer noch nicht so wie ich gerne hätte.
Hättest du da auch noch einen Tipp?
Und dann habe ich noch eine Schleife, die ich gerne per Klick auf einen Button im Excel beenden möchte.
Geht das überhaupt?

Der Code dieser Schleife ist der Folgende:
Dim Spa As Long
Application.ScreenUpdating = False
With Worksheets(„prueba_reunión“)
Spa = Application.Max(.Rows(20))
If CDate(Spa)

0 Hilfreich
Antwort auf Sonja_49b49dzum Beitrag ↑

Hallo Franz, du hast mir wirklich sehr geholfen!! Funktioniert einwandfrei! =) Vielen lieben Dank!!! Vielleicht kannst du mir noch bei zwei anderen - für dich wahrscheinlich - Kleinigkeiten helfen: Ich habe Textboxen in …

Hallo Sonja,

eigentlich fehlen noch ein paar Informationen für eine passgenaue Antwort.

1. Auftrag beenden
Der einfachste Weg wäre hier einen bestimmten Eintrag in eine Zelle zu machen, der von dem Schleifenmakro geprüft wird, so dass keine weiteren Tages-Leer-Blöcke kopiert werden.

Im Code ist keine Schleifen-Struktur erkennbar.
Wird das Makro automatisch ausgeführt, wenn die Datei geöffnet wird?
Die Lösungsansätze können jetzt unterschiedlich sein:
a) Dein Schleifen-Makro fragt in einer MsgBox jedesmal ob der Auftrag beendet ist. Bei „Ja“ wird der Ende-Eintrag in die Zelle geschrieben.
b) Du baust noch eine Schaltfläche ein, deren Makro den Ende-Eintrag in die Zelle schreibt
c) Du machst den Eintrag in die Zelle von Hand.

2. Zahlen in Textbox, Punkt als Dezimalzeichen
Um Probleme zu vermeiden/minimieren sollten die Zahlen in Textboxen mit dem Dezimalzeichen eingegeben werden, das in der Systemsteuerung unter den Regions-/Sprachoptionen eingestellt ist.
Da Textboxen die eingegeben Zahlen als Text beinhalten, müssen sie vor der Weiterverarbeitung in Zahlen umgewandelt werden. Weicht das Dezimaltrennzeichen in der Textbox von dem in der Systemsteuerung ab, dann muss dieses vor der Umwandlung in eine Zahl erst ersetzt werden.

Beispiel für 2 Textboxen in einem Userform:

Private Sub CommandButton3\_Click()
 Dim Zahl(1 To 2) As Variant, bolEintragen As Boolean

 'Zahlen in Textboxen prüfen und in Variable(n) merken
 bolEintragen = True
 If fncZahl(strZahl:=Me.TextBoxZahl1, Ergebnis:=Zahl(1), \_
 strInfo:="Zahl1", strDezi:=".") = False Then bolEintragen = False
 If fncZahl(strZahl:=Me.TextBoxZahl2, Ergebnis:=Zahl(2), \_
 strInfo:="Zahl2", strDezi:=".") = False Then bolEintragen = False

 If bolEintragen = True Then
 'Zahlen in Textboxen weiter verarbeiten
 With ActiveSheet 'Worksheets("TabelleXYZ")
 .Cells(2, 3) = Zahl(1)
 .Cells(2, 4) = Zahl(2)
 End With
 End If
End Sub

Function fncZahl(ByVal strZahl As String, ByRef Ergebnis As Variant, \_
 Optional ByVal strInfo As String = "Textbox für Zahl", \_
 Optional strDezi As String) As Boolean
 'gibt Text in Textbox als Zahl zurück
 'leere Textbox --\> Ergebnis = 0
 'abweichendes Dezimaltrennzeichen in Textbox umwandeln in Dezimalzeichen in \_
 Windows-Systemsteuerung
 If strDezi "" Then
 strZahl = VBA.Replace(strZahl, strDezi, Application.International(xlDecimalSeparator))
 End If
 fncZahl = True
 If IsNumeric(strZahl) Then
 Ergebnis = CDbl(strZahl)
 Else
 If strZahl = "" Then
 Ergebnis = 0
 Else
 fncZahl = False
 MsgBox "Eingabe: " & strZahl & vbLf \_
 & "Für """ & strInfo & """ ist keine Zahl eingegeben!" & vbLf \_
 & "Bitte Eingabe korrigieren", vbInformation + vbOKOnly, "Prüfung Zahlen-Eingabe"
 End If
 End If
End Function

Gruß
Franz

0 Hilfreich

Hallo Sonja,

da wirst Du wohl nicht um eine Userform herumkommen, in der Du die Werte eingibst. Wenn Du nicht weisst, wie man eine Userform nutzt, meld Dich noch mal.
Gruß,
Ptonka

0 Hilfreich

Hallo *Sonja*,

klingt jetzt zunächst wie Datenbank, und ich meine, Du mußt eine Beziehung zwischen benutztes Worksheet und den Toleranzgrenzen herstellen.

Das geht z. B. mit einem Array oder einem (ausgeblendetem) Worksheet. Du hast aber immer folgende Arbeiten:
Deklaration Array oder Erstellung Worksheet mit folgenden Feldern:

  • Name Worksheet
  • Toleranz oben
  • Toleranz unten

Dein Code muss dann den Namen des aktuellen Worksheets holen, dann im Array/Tabelle die Werte für Toleranzen holen und dann erst die Formate (italic, Color) setzen.

Hoffe, Du kommst mit dem Denkanstoß erst mal klar. Wenn nicht, dann kontakte mich noch mal.

Gruß
Harry

0 Hilfreich
Antwort auf Franz_e6478dzum Beitrag ↑

Hallo Sonja, eigentlich fehlen noch ein paar Informationen für eine passgenaue Antwort. 1. Auftrag beenden Der einfachste Weg wäre hier einen bestimmten Eintrag in eine Zelle zu machen, der von dem Schleifenmakro geprüft …

Hallo Franz,

danke für Deine Hilfe!! Hat mir sehr viel gebracht!! =)

Vielleicht darf ich Dir noch eine letzte kleine Frage stelle?!

Ich habe ein Tabellenblatt in dem ich eine Liste von Produkten habe.
In Zelle A5 steht immer der Produktname, in B5 das Volumen und in C5 das Material.
Per klick auf einen Button lasse ich ein UserForm öffnen in dem ich bei einem neuen Produkt alle wichtigen Daten eingeben kann und es mir automatisch ein neues Tabellenblatt mit diesen drei Variablen als Name erstellt (jeweils mit einem Abstand (Space) dazwischen).
Weiters werden diese drei Variablen in meiner Liste hinzugefügt, wo ich nun per Mausklick auf den Produktnamen in A5 auf das jeweilige Blatt kommen möchte.
Dieser Code fehlt mir noch, da ich nicht weiss, was ich als Adresse im „Hyperlink.Add“ Code eingeben soll.
D.h. ich habe z.B. Textbox1, TB2 und TB3 im UF in denen ich die Variablen eintragen kann, aus denen der Name des neuen Tabellenblattes entsteht und aus denen ich somit auch die Adresse für den Hyperlink zusammen setzten möchte.

Ich hoffe ich habe das verständlich geschrieben…
Ich komme da nämlich auch nicht mehr weiter…

Vielen herzlichen Danke für Deine Hilfe und Mühe!!
Liebe Grüße,
Sonja

0 Hilfreich
Antwort auf Sonja_49b49dzum Beitrag ↑

Hallo Franz, danke für Deine Hilfe!! Hat mir sehr viel gebracht!! =) Vielleicht darf ich Dir noch eine letzte kleine Frage stelle?! Ich habe ein Tabellenblatt in dem ich eine Liste von Produkten habe. In Zelle A5 steht i …

Hallo Sonja,

ich nehme an, dass du den Hyperlink zu dem Tabellenblatt des Produkts jeweils in Spalte A in der Zeile einfügen möchtest, in die Daten des Produktes eingetragen werden.

…D.h. ich habe z.B. Textbox1, TB2 und TB3 im UF in denen ich
die Variablen eintragen kann, aus denen der Name des neuen
Tabellenblattes entsteht und aus denen ich somit auch die
Adresse für den Hyperlink zusammen setzten möchte.

Der Code zum Einfügen des Hyperlinks muss etwa wie folgt aussehen:

Private Sub CommandButton1\_Click()
 'Tabelle anlegen, Werte in Liste eintragen, Hyperlink anlegen
 Dim Zeile\_N As Long, strTabName As String
 Dim wksListe As Worksheet, wksNeu As Worksheet
 Set wksListe = ActiveWorkbook.Worksheets("Liste") 'Tabelle mit Produktliste
 With Me
 strTabName = .TextBox1 & " " & .TB2 & " " & .TB3
 End With
 'Neues Blatt für Produkt anlegen
 With ActiveWorkbook 'Arbeitsmappe in der sich die Produktliste befindet
 .Worksheets.Add After:=.Sheets(.Sheets.Count)
 Set wksNeu = .Sheets(.Sheets.Count)
 End With
 'Neues Blatt umbenennen und Daten eintragen
 With wksNeu
 .Name = strTabName
 'nachfolgend Eingaben im neuen Blatt nur zum Testen
 .Cells(2, 1).Value = "Produkt"
 .Cells(2, 2).Value = "Volumen"
 .Cells(2, 3).Value = "Material"
 .Cells(3, 1).Value = Me.TextBox1
 .Cells(3, 2).Value = Me.TB2
 .Cells(3, 3).Value = Me.TB3
 End With 'wksNeu
 'Daten in Produktliste eintragen
 With wksListe
 'nächste freie Zeile in Spalte A
 Zeile\_N = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
 .Cells(Zeile\_N, 1).Value = Me.TextBox1
 .Cells(Zeile\_N, 2).Value = Me.TB2
 .Cells(Zeile\_N, 3).Value = Me.TB3
 'Hyperlink in Spalte A einfügen
 .Hyperlinks.Add Anchor:=.Cells(Zeile\_N, 1), Address:="", \_
 SubAddress:="'" & strTabName & "'!A1", \_
 ScreenTip:="anzeigen Tabelle """ & strTabName & """"
 End With 'wksKiste
 Set wksListe = Nothing: Set wksNeu = Nothing
End Sub

Statt per Hyperlink könntest du die die Produkt-Tabellenblätter auch per Doppelklick in die Zellen mit dem Produktnamen anzeigen.

'Code unter dem Tabellenblatt mit der Produktliste
Private Sub Worksheet\_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Dim strName As String
 On Error GoTo Fehler
 If Target.Column = 1 And Target.Row \> 4 And Target.Value "" Then
 'aktivieren des Tabellenblattes zum Produkt
 Cancel = True
 With Cells(Target.Row, 1)
 strName = .Text & " " & .Offset(0, 1).Text & " " & .Offset(0, 2).Text
 End With
 ActiveWorkbook.Sheets(strName).Activate
 End If
Fehler:
 With Err
 Select Case .Number
 Case 0 'alles OK
 Case Else
 MsgBox "Tabellenblatt """ & strName & """ ist nicht vorhanden!", \_
 vbInformation + vbOKOnly, \_
 "Tabellenblatt zu Produkt """ & strName & """ anzeigen"
 End Select
 End With
End Sub

Liebe Grüße
Franz

0 Hilfreich
Antwort auf Franz_e6478dzum Beitrag ↑

Hallo Sonja, ich nehme an, dass du den Hyperlink zu dem Tabellenblatt des Produkts jeweils in Spalte A in der Zeile einfügen möchtest, in die Daten des Produktes eingetragen werden. …D.h. ich habe z.B. Textbox1, TB2 und …

Hallo Franz,

vielen, vielen herzlichen Dank!!
Du hast mir sehr geholfen. Ich habe Deinen Code mit dem Hyperlink verwendet und es funktioniert einwandfrei.
Ich habe gestern schon was ganz ähnliches probiert, aber leider hab ich es nicht so herbekommen wie Du!

Danke Dir vielmals =)

Liebe Grüße,
Sonja

0 Hilfreich
Antwort auf Franz_e6478dzum Beitrag ↑

Hallo Sonja, ich nehme an, dass du den Hyperlink zu dem Tabellenblatt des Produkts jeweils in Spalte A in der Zeile einfügen möchtest, in die Daten des Produktes eingetragen werden. …D.h. ich habe z.B. Textbox1, TB2 und …

Hallo Franz,

da ist mir noch was eingefallen… wenn ich deine Hilfe vielleicht nochmal haben könnte?

Ich möchte die über die UserForm neu erstellten Datenblätter mit einem Passwort automatisch schützen (also auch mit einem VBA-Code).
Man soll in diesen Datenblättern immer nur die Zellen A31:ECA38 und A75:ECA83 bearbeiten können ohne dass das Fenster mit der Meldung aufgeht, dass dieses Datenblatt/-bereich geschützt ist.

Geht das?
Ich habe etwas annäherndes gefunden, aber leider hilft mir das nicht ganz weiter…

Vielen lieben dank schon im Voraus!!

Liebe Grüße,
Sonja

0 Hilfreich
Antwort auf Sonja_49b49dzum Beitrag ↑

Hallo Franz, da ist mir noch was eingefallen… wenn ich deine Hilfe vielleicht nochmal haben könnte? Ich möchte die über die UserForm neu erstellten Datenblätter mit einem Passwort automatisch schützen (also auch mit eine …

Hallo Sonja,

es ist kein Problem den Blattschutz mit der Anlage eines neuen Blatts zu gestalten. Wenn du in einem VBA-Projekt mit geschützten Blättern arbeiten willst, dann ist es sinnvoll, die entsprechenden Makros in einem eigenen Modul des Projekts anzulegen. Diese Makros kann man dann von überall im VBA-Projekt aufrufen.

Da du sehr große Zellbereiche bearbeiten willst ist ratsam auch gleich eine Fixierung des Fensters der neuen Datei einzubauen.

In meinem Beispiel zu den Hyperlinks baust du dann die Zeile ein um das Blatt zu schützen:

 'Neues Blatt umbenennen und Daten eintragen
 With wksNeu
 .Name = strTabName
 'nachfolgend Eingaben im neuen Blatt nur zum Testen
 .Cells(2, 1).Value = "Produkt"
 .Cells(2, 2).Value = "Volumen"
 .Cells(2, 3).Value = "Material"
 .Cells(3, 1).Value = Me.TextBox1
 .Cells(3, 2).Value = Me.TB2
 .Cells(3, 3).Value = Me.TB3
 End With 'wksNeu
 'Blatt schützen
 Call BlattSchutz\_EIN\_Neu(wks:=wksNeu, PW:=True)
 'Daten in Produktliste eintragen

Wenn du mit festen Kennwörtern im Code arbeitest, dann muss du auch das VBA-Projekt per Kennwort schützen, sonst kann jeder den Code einsehen.

Gruß
Franz

'Code zum Blattschutz in einem allgemeinen Modul
Option Explicit
Private Const BlattKW = "MeinpassWort" 'Kennwort ggf. anpassen

Sub BlattSchutz\_EIN\_Neu(wks As Worksheet, Optional PW As Boolean)
 'Blattschutz für neues Blatt einrichten-inkl. Fensterfixierung
 Dim strPW As String
 With wks
 'Zellbereiche: Schutz aufheben
 .Range("A31:ECA38,A75:ECA83").Locked = False
 'Tabellenblatt fixieren, falls Zustand = nicht fixiert.
 With ActiveWindow
 If .FreezePanes = False Then
 .ScrollColumn = 1
 .ScrollRow = 1
 Range("B5").Select 'Zelle für Fenster-Fixierung ggf. anpassen
 .FreezePanes = True
 End If
 End With
 'Blatt schützen
 .Protect Password:=IIf(PW = True, BlattKW, ""), DrawingObjects:=True, \_
 Contents:=True, Scenarios:=True
 .EnableSelection = xlNoRestrictions
 Range("A31").Select
 End With
End Sub

Sub BlattSchutz\_EIN(wks As Worksheet, Optional PW As Boolean)
 'Blatt schützen
 With wks
 .Protect Password:=IIf(PW = True, BlattKW, ""), DrawingObjects:=True, \_
 Contents:=True, Scenarios:=True
 .EnableSelection = xlNoRestrictions
 End With
End Sub

Sub BlattSchutz\_AUS(wks As Worksheet, Optional PW As Boolean)
 'Blattschutz aufheben
 If PW = True Then
 wks.Unprotect Password:=BlattKW
 Else
 wks.Unprotect
 End If
End Sub


'Admin-Makros für Blattschutz-Aktionen
'Diese müssen ggf. direkt aus dem VBA-Editor gestartet werden \_
 - sie sind im Makro-Start-Dialog nicht verfügbar
Private Sub AktivesBlatt\_Schutzaus()
 Call BlattSchutz\_AUS(wks:=ActiveSheet, PW:=True)
End Sub

Private Sub AktivesBlatt\_Schutzein()
 Call BlattSchutz\_EIN(wks:=ActiveSheet, PW:=True)
End Sub

Private Sub AktivesBlatt\_Schutzein\_NEU()
 'Makro um Blattschutz für aktives Tabellenblatt nachträglich einzurichten wie für neues Blatt
 Call BlattSchutz\_EIN\_Neu(wks:=ActiveSheet, PW:=True)
End Sub
0 Hilfreich
Antwort auf Franz_e6478dzum Beitrag ↑

Hallo Sonja, es ist kein Problem den Blattschutz mit der Anlage eines neuen Blatts zu gestalten. Wenn du in einem VBA-Projekt mit geschützten Blättern arbeiten willst, dann ist es sinnvoll, die entsprechenden Makros in e …

Hallo Franz,

eine Frage, ich habe es jetzt glaube ich soweit hergebracht den Code für mich abzuwandeln, was aber immer noch nicht funktioniert, ist das ich mit einem Userform diesen geschützen Bereich trotzdem noch beschreiben kann.
Ich möchte den Bereich deswegen schützen, weil Mitarbeiter von Hand dort keine Nachtragungen machen können sollen, ausser ich weiss davon - sprich, sie müssen mich holen, weil nur ich das Passwort habe und weiss wie’s funktioniert…
Die Mitarbeiter sollen den Bereich nur per Userform bearbeiten können (also nur einmalige Einträge machen können)…

Liebe Grüße,
Sonja

0 Hilfreich
Antwort auf Sonja_49b49dzum Beitrag ↑

Hallo Franz, eine Frage, ich habe es jetzt glaube ich soweit hergebracht den Code für mich abzuwandeln, was aber immer noch nicht funktioniert, ist das ich mit einem Userform diesen geschützen Bereich trotzdem noch besch …

Hallo Sonja,

die entsprechende Aktion muss du in das Makro/die Makros einbauen, das/die Userformdaten in die Tabelle(n) schreiben.
Dazu muss das Makro nach dem Eintragen von Daten den Schutz des Eingabeblattes erst aufheben und dann den relevanten Zellbereich nach ungeschützen Zellen mit Inhalt durchsuchen und jeweils den Zellschutz aktivieren.
Nach der Prüfung muss der Blattschutz wieder aktiviert werden.

Damit das Ganze rund läuft muss zusätzlich eine Fehlerbehandlung eingebaut werden, die eine spezielle Meldung ausgeben sollte beim Versuch eine geschützte Zelle zu überschreiben.

Gruß
Franz

Beispielmakro

Private Sub cmb\_Eintragen\_Click()
 Dim Zeile As Long, Spalte As Long, wks As Worksheet, Zelle As Range
 On Error GoTo Fehler
 Set wks = ActiveSheet 'Tabellenblatt in das Daten eingegeben werden sollen
 'Eingabezeile ermitteln/setzen
 Zeile = ActiveCell.Row
 'Muss-Eingaben prüfen
 If Me.TextBox\_A "" Or wks.Cells(Zeile, 1) "" Then
 With wks
'Daten eintragen in Spalten der Zeile
 For Spalte = 1 To 175
 With .Cells(Zeile, Spalte)
 Select Case Spalte
 Case 1
 .Value = Me.TextBox\_A.Value
 Case 3
 If Me.TextBox\_C "" Then
 .Value = Me.TextBox\_C.Value
 .Offset(0, -1).FormulaR1C1 = "=IF(RC[1]=""A"",100,50)"
 End If
 Case 4
 If Me.TextBox\_D "" Then .Value = Me.TextBox\_D.Value
 Case 10 To 175
 .Value = Spalte
 End Select
 End With
NextItem:
 Next

'Zellen mit eingetragenen Inhalten sperren - den zu prüfenden Bereich muss man \_
 ggf. an den Bereich anpassen in den mit dem Userform jeweils Daten eingetragen werden. \_
 evtl. muss man auch in mehren Schleifen mehrere Bereiche abarbeiten
 Call BlattSchutz\_AUS(wks:=wks, PW:=True)
 For Each Zelle In .Range(.Cells(Zeile, 1), .Cells(Zeile, 175)).Cells
 With Zelle
 If .Locked = False And Not IsEmpty(Zelle) Then .Locked = True
 End With
 Next
 Call BlattSchutz\_EIN(wks:=wks, PW:=True)
 End With
 Else
 MsgBox "Eingabewert für Spalte A fehlt!", vbInformation + vbOKOnly, \_
 "Eingabewerte in Tabelle eintagen"
 End If

 'Fehlerbehandlung
 Err.Clear
Fehler:
 With Err
 Select Case .Number
 Case 0 ' Alles ist OK
 Case 1004
 If MsgBox("Wert konnte in Zelle """ & wks.Cells(Zeile, Spalte).Address \_
 & """ nicht eingetragen werden, da schon Wert vorhanden." & vbLf & vbLf \_
 & "Bitte rufe Sonja (Tel. 1234) an, wenn der Wert geändert werden soll.", \_
 vbRetryCancel, \_
 "Eintragen Daten - Zelle hat schon Inhalt") = vbRetry Then Resume NextItem
 Case Else
 MsgBox "Fehler-Nr.; " & .Number & vbLf & .Description
 End Select
 End With
 Application.DisplayAlerts = True
End Sub
0 Hilfreich
Antwort auf Franz_e6478dzum Beitrag ↑

Hallo Sonja, die entsprechende Aktion muss du in das Makro/die Makros einbauen, das/die Userformdaten in die Tabelle(n) schreiben. Dazu muss das Makro nach dem Eintragen von Daten den Schutz des Eingabeblattes erst aufhe …

Vielen lieben Dank für deine grossartige Hilfe!!
Ich habe meinen Fehler herausgefunden!! =)

Was ich jetzt noch am Suchen bin: ich habe ein Diagramm in der Größe D40:K50 mit den Daten von D21:K21 und D23:K23 (alles perfekt formatiert).
Jetzt möchte ich, dass ich per knopfdruck jeden Tag ein neues Diagramm hinzufügen kann, d.h. ich möchte es also kopieren und direkt daneben (in L40:S50 usw.) einfügen, mit den Daten L21:S21 und L23:S23 usw.
Ist sowas möglich, dass es mir das immer wieder kopiert mit den neuen Daten und einfach daneben einfügt? Da zerbreche ich mir auch schon lange den Kopf!!

Ich hoffe du kannst mir da auch noch helfen!!

Liebe Grüße,
Sonja

0 Hilfreich
Antwort auf Sonja_49b49dzum Beitrag ↑

Vielen lieben Dank für deine grossartige Hilfe!! Ich habe meinen Fehler herausgefunden!! =) Was ich jetzt noch am Suchen bin: ich habe ein Diagramm in der Größe D40:K50 mit den Daten von D21:K21 und D23:K23 (alles perfek …

Hallo Sonja,

Programmierungen rund um Diagramme sind immer wieder eine Herausforderung, da das Objektmodel komplex und die Zahl der Varianten so groß ist.

Ich bin jetzt davon ausgegangen, dass das Diagramm eine Datenreihe hat. Den Abschnitt in dem den Datenreihen die Werte zugewiesen werden muss du also ggf. noch anpassen.
Der Code passt hier für Säulen- und Liniendiagramme bei anderen Diagrammtypen kann es Abweichungen geben. ggf. muss du mal das Anpassen der Datenbereiche mit dem Recorder aufzeichnen, um die Syntax hinzubekommen.

Gruß
Franz

Sub Chart\_kopieren()
'
' Chart\_kopieren Makro
'
 Dim objChartObject As ChartObject, objChart As Chart, objReihe As Series
 Dim objShape As Shape
 Dim wks As Worksheet
 Dim rngZiel As Variant
 Dim DeltaX As Double, DeltaY As Double

 Const Spalte\_1\_Chart = 4 'Spalte D - Spalte der linkenoberen Ecke des 1. Diagramms
 Const SpaltenChart = 8 'Anzahl Spalten von Chart zu Chart
 Const ZeileChart = 40 'Zeile in der Charts angeordnet werden
 Const Zeile\_X = 21 'Zeile mit den X-Werten der Datenreihe(n)
 Const Zeile\_Y = 23 'Zeile mit den Y-Werten der Datenreihe(n)

 On Error GoTo Fehler
 Set wks = ActiveSheet
 With wks
 'letztes Diagramm-Objekt im Blatt Objektvariablen zuordnen
 Set objChartObject = .ChartObjects(.ChartObjects.Count)
 Set objChart = objChartObject.Chart
 Set objShape = .Shapes(objChartObject.Name)

 With objShape
 'Position des Shapes relativ zur Einfügezelle merken
 DeltaX = .Left - .TopLeftCell.Left
 DeltaY = .Top - .TopLeftCell.Top

 'neue Zielzelle ermitteln
 Set rngZiel = .TopLeftCell.Offset(0, SpaltenChart)
 rngZiel.Select
 End With

 'Einfügezelle bestätigen/anpassen
 Set rngZiel = Application.InputBox(Prompt:="Diagramm in Zelle kopieren? (ggf. Auswahl ändern)", \_
 Title:="Diagramm kopieren", Default:=rngZiel.Address, Type:=8)
 'Zeile und Spalte der gewählten Zelle prüfen
 If rngZiel.Row ZeileChart Then
 MsgBox "Für Diagramm soll eine Zelle in Zeile " & ZeileChart & " gewählt werden!"
 GoTo Fehler
 End If
 If rngZiel.Column Mod SpaltenChart Spalte\_1\_Chart Then
 MsgBox "Für Diagramm soll Spalte in vielfachen von " & SpaltenChart \_
 & " relativ zu Spalte D gewählt werden (D, L, T, AB, AJ, usw.!"
 GoTo Fehler
 End If

 rngZiel.Select
 'Diagramm kopieren
 objShape.Copy
 ActiveSheet.Paste

 'Objekte für neues Diagramm setzen
 Set objChartObject = .ChartObjects(.ChartObjects.Count)
 Set objChart = objChartObject.Chart
 Set objShape = .Shapes(objChartObject.Name)
 'Diagramm genau positionieren
 With objShape
 .Left = rngZiel.Left + DeltaX
 .Top = rngZiel.Top + DeltaY
 End With

 'Datenbereiche der/den Datenreihen neu zuordnen
 Set objReihe = objChart.SeriesCollection(1)
 objReihe.Name = "='" & .Name & "'!" & .Cells(Zeile\_X - 1, rngZiel.Column).Address
 objReihe.XValues = "='" & .Name & "'!" & .Range(.Cells(Zeile\_X, rngZiel.Column), \_
 .Cells(Zeile\_X, rngZiel.Column + SpaltenChart - 1)).Address
 objReihe.Values = "='" & .Name & "'!" & .Range(.Cells(Zeile\_Y, rngZiel.Column), \_
 .Cells(Zeile\_Y, rngZiel.Column + SpaltenChart - 1)).Address
 End With
Fehler:
 With Err
 Select Case .Number
 Case 0 'alles OK
 Case 13 'wird ausgelöst wenn Zellauswahl abgebrochen wird
 'do nothing
 Case Else
 MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description
 End Select
 End With
 Set objChartObject = Nothing: Set objChart = Nothing: Set objReihe = Nothing
 Set objShape = Nothing: Set wks = Nothing: Set rngZiel = Nothing
End Sub
0 Hilfreich
Antwort auf Franz_e6478dzum Beitrag ↑

Hallo Sonja, Programmierungen rund um Diagramme sind immer wieder eine Herausforderung, da das Objektmodel komplex und die Zahl der Varianten so groß ist. Ich bin jetzt davon ausgegangen, dass das Diagramm eine Datenreih …

Hallo Franz,

ja das stimmt. Diagramme sind eine Herausforderung!!
Leider bringt mir Dein Code immer wieder Fehlermeldungen. Zudem möchte ich eigentlich keine Abfragen.
Ist es nicht mölich, dass es mir die Diagramm immer automatisch in die nächste entsprechende (+8) Zelle stellt?
Es soll einfach automatisch, per Knopfdruck das alte Diagramm kopieren und in die nächste freie Positioin kopieren und einfach die Quelle anpassen.
Als Name des Diagramms würde sich z.B. das Datum anbieten, damit es sich auch ändert und man sie unterscheiden kann…

Ich habe jetzt an Deinem Code so herumgebastelt, aber leider komme ich auf keinen grünen Zweig…
Kannst Du mir bitte nochmal helfen?

Liebe Grüße,
Sonja

0 Hilfreich

Deine Antwort

Zum Antworten bitte anmelden oder kostenlos registrieren.