VBA: Fehlermeldung: Prozedur zu groß! Zellenabfrage per Makro

Question

Hallo liebe Excel-Fangemeinde. In einem Tabellenblatt habe ich mir ein Raster angelegt mit Kalenderwoche und Akkordgruppe. Mein Ziel war es wenn ich auf eine bestimmte Zelle innerhalb dieses Rasters klicke, dass mir dann die zugeordnete Datei geöffnet wird. Als ich dieses Raster angelegt hatte war eine entsprechende Ausführung möglich. Nachdem ich aber nun 64 KW´s und 32 Gruppen als Raster angelegt habe, erscheint beim Anklicken einer Zelle aus diesem Raster die Fehlermeldung: Fehler beim Kompilieren - Prozedur zu groß

Folgende Makros habe ich für die Ausführung angelegt:

In dem Tabellenblatt mit dem Raster unter:

***********************************************************************************************

Private Sub Worksheet_SelectionChange (ByVal Target as Excel.Range)

If Target.Address = "$M$4" Then Call M4

If Target.Address = "$M$5" Then Call M5      ...    usw. für jede Gruppe mit jeder KW

*************************************************************************************************

In einem Modul stehen nun die einzelnen Makros zum Call

Sub M4

ChDir G:\Gruppe 02\Meister\2019

Workbooks.Open Filename: = G:\Gruppe 02\Meister\2019\Grp 2 KW 1 fertig. xlsm"

als nächstes kommt nun Sub M5 usw....

************************************************************************************************

Als ich am Anfang das auf diese Art getestet hatte funktionierte es auch, aber jetzt wo ich alle Makros fertig

habe geht gar nichts mehr bis auf die erwähnte Fehlermeldung.

Gibt es da eine andere Möglichkeit außer evtl. das Raster auf 12 Monatsblätter zu verteilen?

Vielen Dank schon mal an alle die hier reingeschaut und sich für das Thema interessiert haben. Ich wünsche allen noch einen schönen Tag und verbleibe mit freundlichen Grüßen... Helmut

Answer 1

Hallo M.O.,

dieses Ding treibt mich echt in den Wahnsinn...  zunächst einmal vielen Dank für Deine Überarbeitung. Bei Users saß das Problem mal wieder vorm Rechner....   Nach der Änderung hatte der Durchlauf funktioniert. Aber das war ja ohne die Einfügung von varGruppe(0) oder (1), sondern schon direkt vorgegeben. Bei der vorangestellten Null bei dem Pfadnamen hattest Du auch Recht... - da habe ich lediglich (auch wenn es nicht nötig war, da ich nur die Gruppen 02 und 04 habe) die Formel von  < 9  auf  < 10 geändert, damit die 09 auch mitgenommen werden könnte. Mir wäre lieber gewesen den eigentlichen Fehler zu sehen, da ich nach wie vor wieder auf den gleichen Fehler laufe, dass er die Datei nicht vorfindet. Grrrr....  Ich habe schon die Anzeige der Fehlermeldung mit dem Pfadnamen übereinander gestellt, so wie auch den Dateinamen. Aber beides sieht für mich identisch aus. Keine Ahnung wo da das Problem noch stecken soll. Man kann natürlich sehr schnell mal ein Leerzeichen zu viel oder zu wenig drin haben. Ich werde mir morgen früh die Datei noch mal in Ruhe vornehmen und versuchen das in kleineren Abschnitten zum durchlaufen zu kriegen. Danach werde ich mich wieder melden. Das es ja doch irgendwie geht zeigt ja der eine erfolgreiche Durchgang und lässt mich auf morgen hoffen. Echt nochmal vielen vielen Dank für Deine Bemühungen. LG Helmut

Answer 2

Hi M.O.,

es hat mir keine Ruhe gelassen und mal folgendes für die Gruppe 15 und 8 KW ausprobiert:

strDateiname = "G:\Fertigung\Abrechnung\Gruppe 15\Meister\2019\Grp 15 KW 8 fertig.xlsm"

Dann in Zelle G1 mal 15 als Ziffer eingetragen und die varGruppe(0) mit der Abfrage auf G1 geändert

strDateiname = "G:\Fertigung\Abrechnung\Gruppe " & Range("G1").Value & "\Meister\2019\Grp 15 KW 8 fertig.xlsm"

******************************************************************************************************************

Beide Versionen funktionierten, so dass irgendwas mit der varGruppe(0) nicht stimmt... obwohl mir im Makro selbst "15" als Wert angezeigt wird. Keine Ahnung was da fehlen sollte. Auch die Anzeige das die Datei nicht da sein soll hat den korrekten Aufbau... und dennoch... so greift er die Datei nicht ab.

Das mit < 9 hatte ich auf die Gruppen-Nr. abgezielt, aber gemeint war die Länge von Gruppe x und somit habe ich das auch wieder auf 9 gesetzt, damit die Formel da richtig ist. Aber das war es leider auch nicht.

Wenigstens bin ich mit dem ausprobieren etwas weiter gekommen, dass es mit anderen Methoden von Zusammensetzungen funktionieren würde.

Im Moment denke ich das ich es evtl. so versuchen sollte... Über Sheetselectionchange den aktuellen Zellinhalt mit Deiner Teilung in zwei Zellen z.B. G1 und H1 eintragen zu lassen... und dann die Datei über die Zellinhaltsabfrage wie im zweiten Beispiel dann zu öffnen. Ob ich das hinkriege weiß ich noch nicht, werde es aber morgen früh dann versuchen umzusetzen, sofern nicht zu der varGruppe(0) ein anderer Lösungsansatz kommt. Vielen vielen Dank und have a nice Day... LG Helmut

Answer 3

Guten Morgen zusammen,

mit dem nachstehenden Makro bekomme ich die beiden Werte in die Zellen G1 und H1 angezeigt und kann Sie mit dem Button-Makro dann auch öffnen - sprich den Wert mit Range ("G1").Value in den Pfad bzw. Dateinamen übertragen lassen.

Dann wollte ich den Abfragebereich der "aktiven Zellen" auf den Bereich M4:AS75 begrenzen. Aber das hat so wie in dem nachstehenden Makro nicht gefruchtet. Aktiviere ich die mit xxx gefüllten Zeilen, dann erhalte ich die Werte aus den Zellen nicht nach G1 bzw. H1 eingetragen, so dass das Makro wohl immer gleich den Exit Sub macht - egal wo ich hinklicke...

Vielleicht kann mir da noch jemand bei helfen den Bereich so einzugrenzen.

Vielen Dank an alle Interessierten und in diesem Sinne... have a nice Day... LG Helmut

*******************************************************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'xxx              Set Target = Intersect(Target, Range("m4:as75"))
'xxx                  If Target Is Nothing Then
'xxx                    Exit Sub

Dim varGruppe As Variant

'Inhalt der aktiven Zelle wird geteilt; Teiler = -
varGruppe = Split(ActiveCell.Value, "-")

Range("G1").Value = varGruppe(0)
Range("H1").Value = varGruppe(1)

'xxx                 End If

End Sub

Answer 4

und noch eine neue Fehlermeldung - schnieeffff.....

Das Makro sieht jetzt wie folgt aus:

**************************************************************************

Sub ausgewaehlteGruppeoeffnen()
Dim strDateiname As String

If Range("G1").Value = 2 Or Range("G1").Value = 4 Then

strDateiname = "G:\Fertigung\Abrechnung\Gruppe 0" & Range("G1").Value & "\Meister\2019\Grp " & Range("G1").Value & " KW " & Range("H1").Value & " fertig.xlsm"

Else

strDateiname = "G:\Fertigung\Abrechnung\Gruppe " & Range("G1").Value & "\Meister\2019\Grp " & Range("G1").Value & " KW " & Range("H1").Value & " fertig.xlsm"

End If

Workbooks.Open strDateiname

End Sub

**************************************************************************************************

Das Öffnen der ausgewählten Datei klappt auch ohne Probleme. Aber sobald ich die geöffnete Datei wieder schließe oder ich klicke nicht in den Auswahlbereich (siehe vorheriger Thread), so bekomme ich die gleiche Fehlermeldung:  Laufzeitfehler 9:  Index außerhalb des gültigen Bereiches

Wähle ich dann Debuggen aus, so wird die Zeile   Range("G1").value = varGruppe(0)   gelb hinterlegt angezeigt.

Kann mir bei den zwei Problemen noch jemand helfen?

Vielen Dank an alle Interessierten und have a nice Day... LG Helmut

Answer 5

Hallo Helmut,

hier mal ein neuer Versuch. Das Makro gehört in das VBA-Projekts deines betreffenden Arbeitsblattes:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim varGruppe As Variant
Dim strDateiname
Dim strPfad

'Makro nur dann ausführen, wenn Zelle im Bereich M4 bis AS75 geklickt wird
If Not Intersect(Target, Range("M4:AS75")) Is Nothing Then

  'Inhalt der aktiven Zelle wird geteilt; Teiler = -
   varGruppe = Split(Target.Value, "-")

   'Dateiname wird zusammengesetzt
   If CLng(varGruppe(0)) < 10 Then
      strDateiname = "Grp " & varGruppe(0) & " KW " & varGruppe(1) & " fertig.xlsm"
    Else
     strDateiname = "G:\Fertigung\Abrechnung\Gruppe " & varGruppe(0) & "\Meister\2019\Grp " & varGruppe(0) & " KW " & varGruppe(1) & " fertig.xlsm"
   End If
 
  'Pfad wird erstellt
   strPfad = "G:\Fertigung\Abrechnung\Gruppe " & varGruppe(0) & "\Meister\2019\"
    
 
  'Datei öffnen, aber nur, wenn diese auch vorhanden ist
  If Dir(strPfad & strDateiname) <> "" Then
       'Datei öffnen
      Workbooks.Open (strPfad & strDateiname)
    Else
     'Prüfen ob Pfad vorhanden
     If Dir(strPfad) = "" Then
        'Fehlermeldung, falls Pfad nicht vorhanden ist
        MsgBox "Der Pfad " & strPfad & " ist nicht vorhanden!", 16, "Datei nicht vorhanden"
      Else
       'falls Pfad vorhanden ist, prüfen, ob Datei vorhaden ist
        If Dir(strPfad & strDateiname) = "" Then
          'Nachricht, dass Datei nicht vorhanden ist
          MsgBox "Die Datei " & strDateiname & " ist nicht vorhanden!", 16, "Datei nicht vorhanden"
        End If
     End If
  End If
 
End If

End Sub

Schau mal, was dabei heraus kommt. Und ja, eigentlich solltes <10 sein, da alle Zahlen kleiner 10 ja einstellig sind.

Gruß

M.O.

Answer 6

Hi M.O.,

zunächst einmal vielen Dank für die Erweiterung mit der Abfrage, ob es die Datei überhaupt gibt oder nicht. Da Dein Makro bei mir im Original keine Datei geöffnet hat noch irgendeine Meldung angezeigt hat, habe ich mal vorsichtshalber ein neues leeres Tabellenblatt mit wenigen Daten gefüllt und dann Dein Makro eingesetzt. Aber auch in der neuen Tabelle als neue Datei brachte kein Öffnen und aber auch keine Meldung heraus, da ich mal Kombinationen eingefügt hatte, die es gar nicht gibt. Irgendwie läuft das Makro ins Leere.... Ich bin mir allerdings auch sicher, dass es mit dem direkten Eintragen von varGruppe(0) oder (1) nicht klappt, da ich das ja bereits mal getestet hatte (dritter Thread auf dieser Seite)...

Um den ganzen was positives wieder abzugewinnen... die Erweiterung gefällt und es gab zumindest keine Fehlermeldung mit Abbruch. Ich werde mal versuchen das wieder so umzubauen, wie ich es zuletzt hatte, nur mit Deinen Ergänzungen/Änderungen und melde mich dann noch mal.

Vielen vielen lieben Dank für Deinen bisherigen Einsatz und wünsche Dir noch einen schönen Tag... LG Helmut

Answer 7

Hi M.O.,

nachdem ich nun Deine Neuerungen mit eingebaut habe funktioniert das Makro - allerdings nicht wie ausgangs gewünscht direkt durch die Zellenauswahl, sondern nach dem Klick auf die entsprechende Zelle und dann auf den Button zum öffnen. Diese Lösung ist für mich vollkommen ok und ich hoch zufrieden mit dem was ich hier jetzt habe. Der Test war zwar in der neuen neutralen Datei, denke aber das in der Originaldatei auch nichts anderes mehr herauskommt. Ich weiß gar nicht wirklich wie ich mich bei Dir bedanken soll, da Du ja echt Zeit auch in mein Problem geopfert hast und ich wohl kaum die Gelegenheit habe, mich bei Dir zu revanchieren zu können.

Ich kann nur vielen vielen Dank sagen und wünsche Dir noch einen schönen Tag (auch mit dem Wissen einem Excel-User was Gutes getan zu haben)... In diesem Sinne... have a nice Day... LG Helmut

Answer 8

Hallo Helmut,

freut mich, dass es jetzt funktioniert. Das Makro hatte ich einfach auf Worksheet-Change-Ereignis gemacht, da ich nicht darauf geachtet hatte, dass es bei der Auswahl der Zelle starten soll.

Ersetzte

Private Sub Worksheet_Change(ByVal Target As Range)

durch

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

und probiere mal, ob das Makro dann bei Auswahl der Zelle auch wie gewünscht funktioniert.

Gruß

M.O.

Answer 9

Hallo M.O.,

ich bewundere ja wirklich Deinen Ehrgeiz, aber ich denke an der Stelle sollten wir nun schließen, da meine Lösung mit dem Button nach der Umwandlung von varGruppe(0) und (1) in die Zellen G1 und H1 funktioniert und mir die richtigen Wochengruppenblätter öffnet.

Mit der letzten Umstellung auf  Private Sub Worksheet_SelectionChange(ByVal Target As Range) wurde zwar der Vorgang beim Zellenanklicken ausgelöst, lief dann aber immer wieder auf Fehlermeldungen. Auch der Pfadname ist jetzt nicht mehr stimmend, wenn die Gruppe kleiner 10 ist. Da fehlt dann die 0 als Vorgabe, wie Du sie in einem der vorhergehenden Threads bereits mal eingebaut hattest. Aber man kann ja auch wirklich durcheinander kommen, wenn man das Ganze so aus dem Kopf heraus macht und nicht selber vor der tatsächlichen Datei sitzt und immer wieder mal zwischendurch was testen kann. Von daher echt mein Respekt, was Du da drauf hast.

Eine abschließende Frage hätte ich aber dennoch...

MsgBox "Die Datei " & strDateiname & " ist nicht vorhanden!", 16, "Datei nicht vorhanden"
Was hat es mit der 16 auf sich... steht die für oder? - aber durch welche Aussage wird die denn ausgelöst?

Vielen lieben Dank und LG Helmut

Answer 10

Hallo Helmut,

wenn es für dich so OK ist und alles funktioniert, dann bin auch ich zufrieden .

Die 16 stellt den Buttons-Wert dar und steht für VbCritical (= 16 = Meldung mit Stop-Symbol anzeigen) und VbOKOnly (= 0 = nur OK-Knopf anzeigen). Siehe dazu hier mehr: MsgBox-Funktion

Diese Messagebox wird ausgelöst, wenn der Pfad zwar vorhanden ist, aber Pfad und Datei nicht vorhanden sind.

Das kannst du mit Dir überprüfen. Wenn Dir(Pfad) leer ist, dann existiert der Pfad / die Datei nicht.

Gruß

M.O.