Excel VBA: Automatisierungsfehler

Computer: Software & Programmierung Office Software
#1

Moin,

ich habe in Excel ein VBA-Modul geschrieben, was alle Zellen sperrt, und nur eine Zeile freigibt, wo der User aus einer Liste von möglichen Werten einen auswählen soll. Soweit funktioniert alles, nur das automatische Hinzufügen der Liste macht Probleme:

Range(„1:2,A:B“).Select
Selection.Locked = True
Range(„A“ & firstNewLine(„A“)).Select
Selection.Locked = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=NutzerKZ"
.IgnoreBlank = True
.InCellDropdown = True
End With

Es kommt die Fehlermeldung

**Laufzeitfehler -2147417848 (80010108)
Automatisierungsfehler
Das aufgerufene Objekt wurde von den Clients getrennt.**

Wenn ich alles zwischen „With“ und „End With“ auskommentiere läuft die Funktion durch.
Die Liste „NutzerKZ“ ist definiert, und zwar durch die erste Spalte der dritten Tabelle (=Tabelle3!$A:blush:A).
Wenn ich die Gültigkeit manuell einstellen will (Daten->Gültigkeit) funktioniert auch alles.
Ich nutze Excel 2003 mit Windows XP SP3 32bit.

Wo kann das Problem liegen? Ich wäre für eine Idee sehr dankbar.

Grüße Marco

PS: Wenn ihr noch mehr Informationen braucht einfach bescheid sagen.

#2

ich habe in Excel ein VBA-Modul geschrieben, was alle Zellen
sperrt, und nur eine Zeile freigibt, wo der User aus einer
Liste von möglichen Werten einen auswählen soll. Soweit
funktioniert alles, nur das automatische Hinzufügen der Liste
macht Probleme:
Es kommt die Fehlermeldung

Laufzeitfehler -2147417848 (80010108)
Automatisierungsfehler
Das aufgerufene Objekt wurde von den Clients getrennt.

Wenn ich alles zwischen „With“ und „End With“ auskommentiere
läuft die Funktion durch.

Hallo Marco,

dein Code „nn“ läuft problemlos in XL 2000 und XL 2007 (auf Win XP).
Schau dir mal „tt“ an, der macht das Gleiche aber ohne Select.

Option Explicit

Sub nn()
ThisWorkbook.Names.Add Name:="NutzerKZ", RefersToR1C1:="=Tabelle3!C1"
Range("1:2,A:B").Select
Selection.Locked = True
Range("A" & firstnewline("A")).Select
Selection.Locked = False
With Selection.Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= \_
 xlBetween, Formula1:="=NutzerKZ"
 .IgnoreBlank = True
 .InCellDropdown = True
End With
End Sub

Function firstnewline(ByVal Spalte As String) As Long
firstnewline = Cells(Rows.Count, Spalte).End(xlUp).Row + 1
End Function

Sub tt()
ThisWorkbook.Names.Add Name:="NutzerKZ", RefersToR1C1:="=Tabelle3!C1"
Range("1:2,A:B").Locked = True
With Range("A" & firstnewline("A")).Validation
 .Parent.Locked = False
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= \_
 xlBetween, Formula1:="=NutzerKZ"
 .IgnoreBlank = True
 .InCellDropdown = True
End With
End Sub
#3

Hallo Reinhard,

ich habe es gerade nochmal in einer anderen Datei probiert, dort funktioniert es auch tadellos. Ich habe jetzt herausgefunden, dass es daran liegt, dass das Blatt geschützt ist.
Ich muss also nur den Blattschutz per VBA herausnehmen und nachdem ich die Tabelle aktualisiert habe, den Blattschutz wieder aktivieren.

Das Problem an der Sache ist nur, dass wenn ich „ActiveWorksheet.Unprotect“ ausführe, der User das Passwort angeben müsste. Gibt es eine Möglichkeit das Passwort im (ebenfalls geschützten) VBA-Code anzugeben?

Grüße, Marco

#4

Hallo Marco,

ich habe es gerade nochmal in einer anderen Datei probiert,
dort funktioniert es auch tadellos. Ich habe jetzt
herausgefunden, dass es daran liegt, dass das Blatt geschützt
ist.

Hmmh, hatte das kurz geprüft, wenn ich das Blatt manuell schütze läuft der Code gar nicht bzw. hängt schon am ersten „Locked“ fest, eben weil das Blatt geschützt ist.
Und die Fehlermeldung ist eine andere.

Ich muss also nur den Blattschutz per VBA herausnehmen und
nachdem ich die Tabelle aktualisiert habe, den Blattschutz
wieder aktivieren.

Du kennst ja protct und unprotect also kriegste das sicher hin.

Das Problem an der Sache ist nur, dass wenn ich
„ActiveWorksheet.Unprotect“ ausführe, der User das Passwort
angeben müsste.

Wieso das denn? Der User startet das Makro, das Makro regelt das mit Passwort, der User kriegt das Passwort nicht zu sehen bzw. muß es eingeben.

Oder gibt es User die das makro nicht starten dürfen?

Gibt es eine Möglichkeit das Passwort im
(ebenfalls geschützten) VBA-Code anzugeben?

? Ja, geb’s bei unprotect und protect an.

Gruß
Reinhard