Gültigkeitsprüfung per VBA

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

Betrifft: Gültigkeitsprüfung per VBA
von: Born
Geschrieben am: 07.10.2015 10:05:07

Hallo Forum,
ich habe folgendes Problem:

In einer Prozedur nutze ich folgenden Code, um eine Gültigkeitsprüfung in Excel zu definieren. Das funktioniert auch.
With Range(Cells(i, m_erfülltSpalte), Cells(i, m_NichtErfülltSpalte))
.Validation.Delete
.Validation.Add (Type:=xlValidateList, Formula1:="x")
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True
.Validation.ErrorMessage = "In den Spalten ""Erfüllt"" und ""Nicht erfüllt"" ist nur ein ""x"" zulässig."
.FormatConditions.Delete
End With

In derselben Prozedur möchte ich eine weitere Gültigkeitsprüfung definieren:
With Cells("M7").Validation
.Add(xlValidateCustom, xlValidAlertInformation, xlBetween, "=UND(J7=""x"";M7="""")")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Hinweis"
.InputMessage = ""
.ErrorMessage = "Wenn ""Erfüllt"" angekreuzt wurde, sind Kommentare nicht zulässig."
.ShowInput = True
.ShowError = True
End With

Hierbei erhalte ich immer eine Fehlermeldung bei dem Add-Befehl.
Was mache ich falsch? Hast jemand eine Idee?

Viele Grüße,
M. Born

Bild

Betrifft: In VBA Englisch...
von: Case
Geschrieben am: 07.10.2015 10:31:58
Hallo, :-)
VBA spricht Englisch: :-)

.Delete
.Add xlValidateCustom, xlValidAlertInformation, xlBetween, "=AND(A1=""x"",M7="""")"
Servus
Case


Bild

Betrifft: AW: In VBA Englisch...
von: Born
Geschrieben am: 07.10.2015 10:39:11
Vielen Dank. Leider erhalte ich die Fehlermeldung weiterhin...

Bild

Betrifft: Der Code...
von: Case
Geschrieben am: 07.10.2015 10:45:32
Hallo, :-)
... läuft bei mir anstandslos durch - allerdings nicht mit Deiner Codezeile:

With Cells("M7").Validation
Servus
Case


Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Daniel
Geschrieben am: 07.10.2015 10:52:14
Hi
also bei mir funktioniert schon das Cells("M7") nicht.
dass muss entweder Range("M7") lauten, oder Cells(7, 13) oder Cells(7, "M")
ausserdem dürfen die Parameter bei .Add nicht in klammern gesetzt werden:

With Cells(7, "M").Validation
 .Add xlValidateCustom, xlValidAlertInformation, xlBetween, "=AND(J7=""x"",M7="""")"
Gruß Daniel

Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Born
Geschrieben am: 07.10.2015 10:57:11
Hallo Daniel,
ich habe den Code so geändert, wie von Dir vorgeschlagen, erhalte aber weiterhin "Anwendungs- oder objektorientierter Fehler". Das Problem muss irgendwo im Add-Befehl liegen.
Hat noch jemand eine Idee?
M. Born

Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Daniel
Geschrieben am: 07.10.2015 11:21:49
Hi
wenn die Zelle schon eine Gütligkeitsprüfung hast, dann musst du die bestehenende GP löschen, bevor du die neue einfügst, sonst kommt der Fehler.
im ersten Teil des Codes hast du das ja schon berücksichtigt, schau dir einfach mal an, was du dort machst, bevor du die neue Regel hinzufügst.
Gruß Daniel

Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Born
Geschrieben am: 07.10.2015 11:51:05
Hallo Daniel,
in der Zelle befindet sich noch keine GP, daher hatte ich auch kein Delete im Code drin. Nachdem ich es eingefügt habe, lief der Code ohne Probleme durch. Allerdings erfolgt die Definition der GP nicht.
Hier folgt mein aktuelle Code:
With appxl2.Cells(i, 13).Validation
.Delete
.Add(xlValidateCustom, xlValidAlertInformation, xlBetween, "=J" & i &"=""x""") ' "=AND(J" & i & "=""x"",M" & i & "="""")") '"=J7=""x""")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Hinweis"
.InputMessage = ""
.ErrorMessage = "Wenn ""Erfüllt"" angekreuzt wurde, sind Kommentare nicht zulässig."
.ShowInput = True
.ShowError = True
MsgBox(i & " " & .Formula1)
End With
Lasse ich mir am Ende der Schleife für die Zelle den Parameter Formula1 anzeigen, wird die korrekte Formel ausgegeben. Nach dem Durchlaufen ist jedoch die Zelle wieder wie vorher ohne eine GP formatiert, ohne dass ich sie nochmal per Makro ändere. Ich werde aus dem Ganzen nicht schlau...

Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Daniel
Geschrieben am: 07.10.2015 12:32:40
Hi
ich hab den Code von dir rauskopiert, bei mir eingefügt, und die Variablen mit Werten versehen.
zunächst wird die Zeile mit .Add mit "Fehler beim kompilieren, Erwartet =" angemahnt.
Diesen Fehler kann ich beheben, in dem ich die Klammern bei .Add entferne.
mit dieser Korrektur läuft der von dir gezeigte Code dann ohne Fehler durch und am Ende hat Zelle die entsprechende Gültigkeitsprüfung.
allerdings ist die Formel für die Gültigkeitsprüfung nicht funktionsfähig.
eine Formel nach dem Schema =J1="x" wird von der Gütigkeitsprüfung nicht richtig erkannt und man kann immer einen Wert in die Zelle eingeben egal was in J1 steht.
Zuverlässig Funktioniert bei mir beispielsweise: =ZählenWenn(J1;"x")
das ganze dürfte zwar ein Bug sein, aber den muss man halt irgendwie ausmanövrieren.
btw ich hab Excel 2010 mit Win7
Gruß Daniel

Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Born
Geschrieben am: 07.10.2015 13:12:30
Hallo Daniel,
ich setze ebenfalls Win7 und Excel 2010 ein.
Zählenwenn funktioniert bei mir überhaupt nicht. Wenn ich meine Formel =UND(J7="x";M7="") manuell in die Zelle eingebe, funktioniert sie problemlos. Nutze ich sie in VBA, wird die GP nicht aktiviert, d.h. auch z.B. die übergebene Fehlermeldung ist leer.
Ich muss per VBA die Eingabe in Spalte M verhindern, wenn in J ein "x" steht. Per bedingter Formatierung kann man leider den Blattschutz nicht über eine bedingte Formatierung steuern.
Gibt es ev. einen anderen Weg, die Gültigkeit per VBA zu setzen?
Viele Grüße,
M. Born

Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Daniel
Geschrieben am: 07.10.2015 13:39:14
Hi
wenn ich deinen Hinweistext richtig interpretiere, darf man in Spalte M nur dann was eingeben, wenn in Spalte J kein "x" steht.
in dem fall wäre die Gültigkeitsprüfung:
.Add xlValidateCustom, xlValidAlertInformation, xlBetween, "=CountA(J" & i & ")=0"
Gruß Daniel

Bild

Betrifft: AW: Gültigkeitsprüfung per VBA
von: Born
Geschrieben am: 07.10.2015 14:13:44
Hi Daniel,
einen kleinen Schritt bin ich jetzt weitergekommen. Ich musste Deine Formel noch leicht anpassen, damit das Makro durchlief: "=CountA(J" & i & ")>0"
Jetzt erhalte ich die Fehlermeldung, wenn ich versuche, etwas in die Zelle einzugeben. Allerdings kann ich die Meldung mit "OK" quittieren und es steht dann trotzdem etwas in der Zelle, obwohl in J ein "x" gesetzt wurde. Die Fehlermeldung kann entweder mit "OK" oder "Abbrechen" geschlossen werden.
In einer Test-Tabelle, bei der ich die GP manuell eintrage, erhalte ich eine Fehlermeldung, die mit "Wiederholen" und "Abbrechen" geschlossen werden kann.
Wenn ich das noch bereinigen kann, dann habe ich dieses Problem endlich gelöst.
Viele Grüße,
M. Born

Bild

Betrifft: AlertStyle...
von: Case
Geschrieben am: 07.10.2015 14:24:08
Hallo, :-)
Du musst einen anderen "AlertStyle" nehmen. Statt "xlValidAlertInformation" den "xlValidAlertStop".
Servus
Case


Bild

Betrifft: AW: AlertStyle...
von: Born
Geschrieben am: 07.10.2015 14:32:51
In der Hilfe stand lediglich, dass das das angezeigte Symbol beeinflusst. Das hätte Microsoft auch besser beschreiben können.
Vielen Dank Euch beiden, jetzt läuft es so, wie es soll.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Gültigkeitsprüfung per VBA"