Microsoft Excel

Herbers Excel/VBA-Archiv

Gültigkeitsliste per VBA ändern

Betrifft: Gültigkeitsliste per VBA ändern von: Uppe
Geschrieben am: 14.11.2012 14:32:42

Hallo Experten,

ich habe viele Dateien mit jeweils mehreren Gültigkeitslisten der Form =INDIREKT("[Datei1.xls]Tabelle1!Bereich1").

Die Daten für die Gültigkeitslisten sind jetzt aber in der Datei Datei2.xlsm abgespeichert. Deshalb wollte ich die Gültigkeitslisten per VBA-Code in allen Dateien anpassen.

Wenn ich die Gültigkeitslsite von Hand ändern will, kommt die Fehlermeldung "Die Quelle untersucht gerade einen möglichen Fehler. Soll fortgefahren werden? " WEnn ich "ja" anklicke, wird die Gültigkeitsliste korrekt angepasst.

Für VBA habe ich folgenden Code zusammengebastelt:


For Each Blatt In wkb.Worksheets
For Each Zelle In Blatt.Range("A1:F100")
On Error Resume Next
TargetType = Zelle.Validation.Type
On Error GoTo 0
If TargetType = xlValidateList Then
If InStr(1, Zelle.Validation.Formula1, "Datei1.xls") Then
strGültigkeit = Zelle.Validation.Formula1
strGültigkeit = Replace(strGültigkeit, "Datei1.xls", "Datei2.xlsm")
Zelle.Validation.Modify Formula1:=strGültigkeit
End If
End If
TargetType = 0
Next Zelle
Next Blatt


Leider funktioniert das nicht. Fehlermeldung bei Validation.Modify ist "Anwendungs- oder objektdefinierter Fehler".

Wo liegt das Problem?

Danke für eure Hilfe!
Gruß Uppe

  

Betrifft: Scheint soweit alles richtig zu sein, ... von: Luc:-?
Geschrieben am: 14.11.2012 20:40:58

…Uppe;
hast dich an die VBE-Hilfe gehalten. Könnte deshalb der gleiche Grund wie bei der manuellen Änderung sein. Evtl hilft das vorherige Umstellen des KalkModus auf manuell (ggf Rückstellen nicht vergessen). Wenn nicht, wirst du wohl alle Einstellungen notieren, löschen, wandeln und mit Add neu anlegen müssen.
Gruß Luc :-?


  

Betrifft: AW: Scheint soweit alles richtig zu sein, ... von: Uppe
Geschrieben am: 15.11.2012 11:03:25

Hallo Luc,

danke für Deine Rückmeldung. Zunächst habe ich die Berechnung auf manuell gesetzt - von Hand hat es dann ohne Fehlermeldung funtkioniert, per Makro blieb die Fehlermeldung.

Dann habe ich es auch mit Delete und Add versucht, aber auch hier bleibt die Fehlermeldung :-(

 Application.Calculation = xlCalculationManual
  For Each Blatt In wkb.Worksheets
    For Each Zelle In Blatt.Range("A1:F100")
      On Error Resume Next
      TargetType = Zelle.Validation.Type
      On Error GoTo 0
      If TargetType = xlValidateList Then
        If InStr(1, Zelle.Validation.Formula1, "Datei1.xls") Then
          strGültigkeit = Zelle.Validation.Formula1
          Zelle.Validation.Delete
          strGültigkeit = Replace(strGültigkeit, "Datei1.xls", "Datei2.xlsm")
          Zelle.Validation.Add xlValidateList, xlBetween, Formula1:=strGültigkeit
        End If
      End If
      TargetType = 0
    Next Zelle
  Next Blatt
  Application.Calculation = xlCalculationAutomatic
Hat noch jemand einen Tipp?

Danke und Gruß
Uppe


  

Betrifft: Lösung von: Uppe
Geschrieben am: 20.11.2012 14:50:32

Hallo an alle, die vielleicht auch dieses Problem haben!

Ich habe die Lösung gefunden: Datei2.xlsm (in der die Gültigkeitsliste steht) muss bei der Änderung geöffnet sein.

Gruß Uppe


  

Betrifft: Naja, weeßte... VBA g...!? Gruß owT von: Luc:-?
Geschrieben am: 20.11.2012 19:19:47

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "Gültigkeitsliste per VBA ändern"