VBA / Excel: Fehler weil Blatt nicht aktiv

Computer: Software & Programmierung Visual Basic
#1

Hallo (mal wieder :wink:),

in meinem VBA Code möchte ich die über ein UserForm eingegebenen Werte auf Gültigkeit prüfen. Das funktioniert gut, so lange sich die Daten auf „einem“ Tabellenblatt („Tabelle1“)befinden und ich dieses vorher über Select oder Activate angewählt habe.

Wenn die Daten jedoch auf einem anderen Tabellenblatt zu finden sind („Tabelle2“), dann bekomme ich den Laufzeitfehler „Anwendungs- oder objektdefinierter Fehler“ zurück.

Wenn ich das entsprechende Tabellenblatt vor der Prüfung anwählen und nach der Prüfung wieder zurück auf das vorherige Tabellenblatt wechsel, funktioniert die Prüfung.

Versucht habe ich schon folgendes:

If Application.WorksheetFunction.CountIf(ThisWorkbook.Worksheets(„Tabelle2“).Range(Cells(3, 1), Cells(loLetzteZeile, 3)), cboWert1.Value) = 0 Then … usw

If Application.WorksheetFunction.CountIf(Worksheets(„Tabelle2“).Range(Cells(3, 1), Cells(loLetzteZeile, 3)), cboWert1.Value) = 0 Then … usw

Wenn ich den Range mit [„CityPairs!A3:A182“] definiere funktioniert zwar die Prüfung auf einem fremden Tabellenblatt, jedoch fehlt mir dann die Dynamik der letzten Zeile. Denn die kann sich erweitern.

In der VBA Hilfe bin ich leider nicht fündig geworden bzw. habe für Range genau diese Schreibweise gefunden.

Vielen Dank schon mal für’s gucken,

Gruß Axel

#2

Hi,

also ich konnte mein Problem beheben, ich habe die entsprechenden Prüfung und auch das nachfolgende ‚vlookup‘ mit in den Teil genommen, in welchem das zweite Tabellenblatt aktiv ist und übernehme das Ergebnis mittels einer Variablen in den weiteren Code.

Zwar nicht elegant, aber es funktioniert.

Wäre aber dennoch für eine kurze Erklärung dankbar, warum der Zellenbezug nicht erkannt wird.

Danke und Gruß
Axel

#3

Hallo Axel
Ich würde den Programmcode grundsätzlich in einem Modul programmieren.
Bei Codes innerhalb von Tabellen kann es zu Objekt Fehlern kommen, weil
Jedes Tabellenblatt eine eigene Klasse darstellt.
Innerhalb des Moduls sollte man die einzelnen Tabellen und Ranges
als Objekte verwalten.
z.B.
Public TabXY1 As Worksheet 'Objektvariabele als Tabelle
Public TabXY2 As Worksheet

Sub Test1()
Dim Bereich As Range 'Objektvariabele als Range
Set TabXY1 = ThisWorkbook.Sheets(1)'Erste Tabelle in Workbook
Set TabXY2 = ThisWorkbook.Sheets(„TabellenName“)
Set Bereich = Range(TabXY1.Cells(2, 1), TabXY1.Cells(5, 7))
Bereich.Clear 'Löscht Range 2,1- 5,7 in Sheet(1)
'Die ComboBox einer UserForm kann man so ansprechen
UserForm1.ComboBoxBauteil010.AddItem TabXY2.Cells(47, 11)

Ich hoffe ich konnte dir ein paar Anregungen geben, wie man ohne
Objektprobleme in einem Modul verschiedene Tabellen und UserForms
kombiniert bearbeiten kann.

Gruß
Norbert
UserForm1

#4

Hallo Norbert,

vielen Dank für den Tipp.

Mein VBA Code ist hinter einem UserForm abgelegt, auf den Tabellenblättern habe ich keinen Code hinterlegt.

Mittlerweile funktiert auch alles so wie ich es haben möchte, jedoch muss ich je nach Aktion über Select das entsprechende Blatt aktivieren.

Die Tabellenblätter als Objekt zu definieren hört sich ziemlich gut an. Ich werde das mal versuchen entsprechend einzubauen. Ich könnte mir vorstellen, dass sich das auch positiv auf die Performance auswirkt. Meine mal irgendwo gelesen zu haben, dass ein Select und Activate Zeit kostet.

Vielen Dank noch mal, Gruß Axel

#5

Hallo Norbert,

hab mich natürlich gleich mal ans probieren gemacht und ich war erstaunt, bei wie vielen Cells ich noch das Tabellenblatt als Objekt hinzufügen musste. Sieht auch schon sehr gut aus, vor allem dass das Tabellenblatt nicht ständig hin und herspringt.

Ein Problem habe ich nur noch bei einer Sortierung die laufen soll. Den Code habe ich aus den Makro Recorder übernommen und entsprechend angepasst. Den OrderCustom habe ich aus dem vorherigen Codeteil, über welchen eine Combobox ausgelesen wird um das Suchkriterium zu erhalten.
Die Sortierung funktioniert nun jedoch so lange nicht, wie das Tabellenblatt Auswertung (Variable und Tabellenblatt haben den gleichen Namen).
Sobald ich die Tabelle aktiviere, funktioniert die Sortierung wieder. Gibt es da einen speziellen Trick oder eine besondere Benennung? Google war leider keine Hilfe.

Nachfolgende Codes habe ich schon ausprobiert:

Range(Auswertung.Cells(4, 1), Auswertung.Cells(loLetzteZeileAuswertung, 95)).Sort _
Key1:=Cells(4, 3), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=lngOC, _
MatchCase:=False, Orientation:=xlTopToBottom

Auswertung.Range(Cells(4, 1), Cells(loLetzteZeileAuswertung, 95)).Sort _
Key1:=Cells(4, 3), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=lngOC, _
MatchCase:=False, Orientation:=xlTopToBottom

Danke und Gruß
Axel