Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Userform
BildScreenshot zu Userform Userform-Seite mit Beispielarbeitsmappe aufrufen

Macro Sprung zu Ziel und Aktion

Betrifft: Macro Sprung zu Ziel und Aktion von: Volker Bastler
Geschrieben am: 29.10.2020 16:50:26

Hallo VBA-Profis,
leider breche ich bei einer meines Erachtens recht übersichtlichen VBA-Programmierung ab. Der Stand ist folgender:
Eine externe App trägt in Blatt1 einer Datei „Name1“ der Reihe nach fünf Werte, als letztes ein Datum ein. Die jeweils aus den 5 Werten bestehenden Datensätze werden untereinander gesetzt.

  • Lagerplatz Artikel Entnahme Einlagerung Datum
    G2 15 FALSCH WAHR 28.10.2020
    B5 11 FALSCH WAHR 28.10.2020
    E2 8 FALSCH WAHR 28.10.2020
    D1 5 FALSCH WAHR 28.10.2020
    C3 WAHR FALSCH 28.10.2020
    R7 25 FALSCH WAHR 29.10.2020

  • Danach springt der Cursor automatisch in die nächste Zeile und wartet auf den nächsten Datensatz. Das Datum selbst ist gleichgültig, nur der Status des Eintrages an sich spielt als Trigger für das Macro eine Rolle (Worksheet Change, Range ("A2:E1000")):

    Meine Aufgabe ist nun:
    1) Wenn sich eine Zelle im Blatt1 „Name1“ in einem Bereich konkret A2:E1000, zB Zelle E3 durch Eintrag eines Datums (von leer ausgehend) ändert, soll das Makro an den in der Zelle A3 eingegebenen Ort=Zellbezeichnung in Blatt2 „Name2“ springen. Wenn also in Zelle A3 der Wert „G2“ steht, springt das Makro auf Blatt2 „Name2“, Zelle G2.
    2) Dort im Blatt2 „Name2“, soll dann das Macro je nach Wahrheitsgehalt der Spalte C oder D in Blatt1 „Name1“ eine Aktion durchführen. Es ist gesichert, dass nur eine der beiden Zellen WAHR ist. Die andere Zelle hat dann Status FALSCH:
    Wenn Zelle zB C3=WAHR, dann Eintrag „Leer“
    Wenn Zelle zB D3=WAHR, dann den Wert aus Blatt1/Zelle B3 eintragen, hier im Beispiel "15"
    Sollten dazu noch Fragen bestehen, einfach noch mal melden. Ansonsten bedanke ich mich schon mal sehr für Eure Hilfe.
    Volker der Bastler

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: fcs
    Geschrieben am: 30.10.2020 09:21:12

    Hallo Volker,

    mit nachfolgendem Ereignismakro sollte es funktionieren

    LG
    Franz
    'Ereignismakro im Code-Modul von Blatt "Name1"
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rngZiel As Range
      Dim varLagerplatz, varArtikel, bolEntnahme As Boolean, bolEinlagerung As Boolean, varDatum
      Dim Zeile As Long
      If Target.Row > 1 And Target.Cells.Count = 1 Then
        Zeile = Target.Row
        Select Case Target.Column
        Case 5 'Spalte E
          If Cells(Zeile, 5) <> "" Then 'Datum ist eingetragen
            varLagerplatz = Cells(Zeile, 1).Value
            Set rngZiel = ActiveWorkbook.Worksheets("Name2").Range(varLagerplatz) 'Blattname ggf.  _
    anpassen
            varArtikel = Cells(Zeile, 2).Value
            bolEntnahme = Cells(Zeile, 3).Value
            bolEinlagerung = Cells(Zeile, 4).Value
            varDatum = Cells(Zeile, 5).Value
            If bolEntnahme = True Then
              rngZiel.Value = "leer" ' oder rngZiel.ClearContens
            End If
            If bolEinlagerung = True Then
              rngZiel.Value = varArtikel
            End If
          End If
        End Select
      End If
    End Sub
    
    
    oder als "Kurzversion"
    'Ereignismakro im Code-Modul von Blatt "Name1"
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rngZiel As Range
      Dim Zeile As Long
      If Target.Row > 1 And Target.Cells.Count = 1 Then
        Zeile = Target.Row
        Select Case Target.Column
        Case 5 'Spalte E
          If Cells(Zeile, 5) <> "" Then 'Datum ist eingetragen
            Set rngZiel = ActiveWorkbook.Worksheets("Name2").Range(Cells(Zeile, 1).Value) ' _
    Blattname ggf. anpassen
            If Cells(Zeile, 3).Value = True Then
              rngZiel.Value = "leer" ' oder rngZiel.ClearContens
            End If
            If Cells(Zeile, 4).Value = True Then
              rngZiel.Value = Cells(Zeile, 2).Value
            End If
          End If
        End Select
      End If
    End Sub
    


    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: Volker Bastler
    Geschrieben am: 30.10.2020 16:00:48

    Hallo Franz,

    zunächst mal vielen Dank für Deine Mühe und die Antwort. Ich habe nun gleich das Macro in Blatt1="Bewegungen" eingebaut. Leider funtioniert das Macro noch nicht-will sagen, dass meine übertragenen Daten wie beschrieben in Blatt1 ankommen, aber nicht weitergereicht werden. Leider kann ich mangels Kenntnisse mit dem Debugger nicht umgehen. Daher nachstehend der eingegebene Code. Folgende Änderungen wurden gemäß meiner Datei vorgenommen:

    Blatt1 mit dem Macro heißt bei mir "Bewegungen", ist aber wohl weniger relevant

    Aus "Artikel" wurde "Wein"

    Blatt 2 heißt "Regal"

    Wie bereits beschrieben, kommen die Daten im Blatt "Bewegungen" in den Spalten

    A=Lagerplatz

    B=Wein

    C=Entnahme

    D=Einlagerung

    E=Datum an. Datum wurde als dd.mm.yyyy und mal versuchsweise als Zahl definiert:
    'Ereignismakro im Code-Modul von Blatt "Bewegungen"
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rngZiel As Range
      Dim varLagerplatz, varWein, bolEntnahme As Boolean, bolEinlagerung As Boolean, varDatum
      Dim Zeile As Long
      If Target.Row > 1 And Target.Cells.Count = 1 Then
        Zeile = Target.Row
        Select Case Target.Column
        Case 5 'Spalte E
          If Cells(Zeile, 5) <> "" Then 'Datum ist eingetragen
            varLagerplatz = Cells(Zeile, 1).Value
            Set rngZiel = ActiveWorkbook.Worksheets("Regal").Range(varLagerplatz) 'Blattname ggf. _
    anpassen
            varWein = Cells(Zeile, 2).Value
            bolEntnahme = Cells(Zeile, 3).Value
            bolEinlagerung = Cells(Zeile, 4).Value
            varDatum = Cells(Zeile, 5).Value
            If bolEntnahme = True Then
              rngZiel.Value = "Leer" ' oder rngZiel.ClearContens
            End If
            If bolEinlagerung = True Then
              rngZiel.Value = varWein
            End If
          End If
        End Select
      End If
    End Sub
    Könntest Du bitte noch mal den Code durchsehen? Falls wir so nicht weiterkommen: Was haben wir dann für Alternativen, die nicht den Regeln des Forums widersprechen?

    Viele Grüße

    Volker

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: fcs
    Geschrieben am: 30.10.2020 23:37:05

    Hallo Volker,

    das Makro scheinst du korrekt eingebaut zu haben.
    Du kannst es auch testen, indem du in Spalte E ein Datum von Hand änderst.

    Mögliche Probleme:
    1) Dein Excel ist so konfiguriert, das keine Makro gestartet werden.
    2. Beim Öffnen der Datei werden die Makros nicht aktiviert - dann wird normalerweise eine entsprechende Meldung angezeigt.
    2) Die App, die die Daten einträgt, deaktiviert die Ereignismakros in Excel - evtl. auch nur vorübergehend.

    Ich hab dir hier mal die Datei hochgeladen, die ich zum Testen benutzt habe.
    https://www.herber.de/bbs/user/141198.xlsm

    Mögliche Lösung: Das Übertragen der Werte wird manuell per Knopfdruck gestartet.
    Ich hab ein entsprechendes Makro in meine Test-Datei eingebaut.

    Wenn es gar nicht funktioniert, dann kannst du mir auch die Datei per E-Mail schicken - Mail-Adresse findest du in meinem Profil. Wenn das Problem in der App begründet ist, die die Daten überträgt, dann kann ich natürlich nichts ausrichten.

    LG
    Franz

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: Bastler
    Geschrieben am: 31.10.2020 14:57:28

    Hallo Franz,
    vielen Dank für Deine Antwort. Dein genannten Problemvarianten 1 und 2 sind es nicht, weil mit Prog-start schon mal ein Makro mit Begrüßungsbildschirm startet. Aus Langeweile löschte ich dann zunächst eines des bereits eingegeben Datums und gab es neu ein: BINGO. Nun tätigte ich eine neue Datenübertragung mit der App(Scan-IT to office). Die kommt sofort rein, es erfolgt aber keine Datenübertragung.
    Nun gab ich das Datum als simple Reaktion in Form F_=wenn(E_="";"";E_) an die Spalte F weiter und änderte das Macro wie beigefügt (Änderung bei: Case 6 'Spalte F, If Cells(Zeile, 6)): Keine Datenübertragung, aber auch nicht mehr, bei manueller Eingabe in Spalte E!
    'Ereignismakro im Code-Modul von Blatt "Bewegungen"
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rngZiel As Range
      Dim varLagerplatz, varWein, bolEntnahme As Boolean, bolEinlagerung As Boolean, varDatum
      Dim Zeile As Long
      If Target.Row > 1 And Target.Cells.Count = 1 Then
        Zeile = Target.Row
        Select Case Target.Column
        Case 6 'Spalte F
          If Cells(Zeile, 6) <> "" Then 'Datum ist eingetragen
            varLagerplatz = Cells(Zeile, 1).Value
            Set rngZiel = ActiveWorkbook.Worksheets("Regal").Range(varLagerplatz) 'Blattname ggf. _
    anpassen
            varWein = Cells(Zeile, 2).Value
            bolEntnahme = Cells(Zeile, 3).Value
            bolEinlagerung = Cells(Zeile, 4).Value
            varDatum = Cells(Zeile, 5).Value
            If bolEntnahme = True Then
              rngZiel.Value = "Leer" ' oder rngZiel.ClearContens
            End If
            If bolEinlagerung = True Then
              rngZiel.Value = varWein
            End If
          End If
        End Select
      End If
    End Sub
    

    Irgendwie sperrt also eine unbekannte Zeit die App die Makrofunktion oder meine Änderung stimmt eher nicht, da die manuelle Aktualiserung hier auch nicht funktioniert. Würdest Du die Änderung bitte mal prüfen? Wenn meine Änderung stimmt und hinsichtlich meiner Überlegung von Dir bestätigt wird, würde ich zunächst am Montag den Lieferanten der App in Österreich kontaktieren. Käme ich da nicht weiter, müsste ich über den manuellen Button entscheiden oder mir eine andere Lösung überlegen. Die Datei Dir zuzusenden, wäre nicht das Thema. Problem wäre dann, dass Du die Dateneingabe mit der App wohl realisieren müsstest(Dauert mit meiner Anleitung 30 Minuten, brauchst aber ein Android-Gerät. Wenn Du da mitspielen würdest, wollte ich Dir dann Deinen Aufwand erstatten und nicht nur verbales Dankeschön zusenden ;-)

    LG
    Volker

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: fcs
    Geschrieben am: 01.11.2020 18:24:06

    Hallo Volker,

    das Change-Ereignis in einem Tabellenblatt reagiert nicht auf Änderung von Formelergebnissen. Deshalb funktioniert deine Variante nicht.

    Das Change-Ereignis reagiert nur auf Eingabe/Änderung von Werten, manuell oder per Makro.

    Es scheint also mit der Art, wie die externe App die Daten einfügt, zu tun zu haben, warum das Ereignis-Makro nicht anspricht.

    Es macht keinen Sinn, dass ich mir die App installiere, denn typischerweise sind die Möglichkeiten begrenzt, Einstellungen innerhalb einer App zu machen.

    Eine Möglichkeit für die automatische Übertragung der Werte sehe ich jetzt noch.

    Einbau eines Makros mit OnTime-Methode, dass in regelmäßigen Abständen (ab 1 Sekunde aufwärts) gestartet wird und sich wiederholt aufruft, bis es gezielt gestoppt wird.
    Der Start des Makros wird in das schon vorhandene Workbook_Open-Ereignisnakro eingebaut, und zwar nachdem das Intro-Userform angezeigt wurde.
    Für den Stopp des Makros kann man ein Makro einbauen und das Workbook_BeforeClose-Ereignis nutzen.

    Ich hab das jetzt in meine Testdatei eingebaut.
    https://www.herber.de/bbs/user/141236.xlsm

    Der Code ist unter DieseArbeitsmappe und im Modul "Modul_Uebertragen" zu finden.

    Das Testmakro zum Eintragen von Werten erzeugt jetzt Zufallswerte.


    LG
    Franz

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: Bastler
    Geschrieben am: 01.11.2020 18:34:37

    Hallo Franz,
    durch Zufall sehe ich erst jetzt in der Übersicht Deine Nachricht. Ich bekam keine Info vom Eingang Deiner Nachricht.

    Vielen Dank zunächst für Deine Mühe. So macht das Ganze aber keinen Sinn. Ich kontaktiere jetzt erst mal den Anbieter des AddIn und der App. Ich melde mich nochmal bei Dir, wenn ich von dort eine Antwort haben

    Ich weiß nicht, wie ich für ein PN an Deine Mailadresse komme. Bitte sende mir diese doch mal per PN zu.

    Danke und Viele Grüße
    Volker

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: fcs
    Geschrieben am: 02.11.2020 16:43:33

    Hallo Volker,

    Vielen Dank zunächst für Deine Mühe. So macht das Ganze aber keinen Sinn. Ich kontaktiere jetzt erst mal den Anbieter des AddIn und der App.

    Warum macht mein Vorschlag keinen Sinn?
    Das OnTime-Ereignis läuft im Hintergrund solange die Datei geöffnet ist und aktualisiert entsprechend der eingestellten Zeitdifferenz die eingescannten Werte.

    An meine E-Mail-Adresse kommst du per Klick auf das Bild-Symbol links neben meinen Antworten.

    LG
    Franz

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: Grawert
    Geschrieben am: 02.11.2020 17:03:16

    Hallo Franz,
    dann habe ich das falsch verstanden und komme darauf zurück, sobald ich die Antwort vom Anbieter der Übertragungslösung auf meine Anfrage von gestern habe. Ich denke, die kommt bis Mittwoch. Morgen bin ich erst am späten Nachmittag wieder an diesem PC.

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: Bastler
    Geschrieben am: 02.11.2020 20:15:11

    Hallo Franz,
    eben habe ich mich erst mal richtig mit Deiner Idee beschäftigt: Sorry, da war ich gestern wegen der unsauberen Funktion des AddIn etwas angekratzt. (Es gibt noch einen weiteren Mangel des AddIN bei dessen Start. Immerhin kostet die App was...)
    Ich halte Deine Lösung für eine gute Idee, die ich mal in der im Bau befindlichen Datei und somit mit der Datenübertragungs-AddIn testen wollte bzw. müsste.
    Bitte erkläre mir mal, was ich dazu wo aus Deiner Testdatei einsetzen müsste. Diese Lösung ist ja nun doch etwas umfangreicher. Oder ist es einfacher, Dir die Datei per PN zu senden?
    Ich sehe kein Bild-Symbol links von Deinen Nachrichten. Kommst Du an MEINE Mailadresse? Dann sende mir doch bitte mal Deine für eine PN. Denn das artet ja nun aus-siehe meine Worte gestern.
    Viele Grüße
    Volker

    Betrifft: AW: OmTime-Makro einbauen
    von: fcs
    Geschrieben am: 03.11.2020 16:33:26

    Hallo Volker,

    an meine E-mail-Adresse kommst du über diese Schaltfläche, wenn du den Thread anzeige lässt.


    Oder über "Forum & Services --> Profile --> Profilliste
    hier dann zu meinem Usernamen "fcs" scrollen.

    Deine E-Mail-Adresse ist nur verfügbar, wenn du ein Profil anlegst inkl. Anzeige der E-Mail-Adresse.

    Du kannst wie folgt vorgehen, um die On-Time-Funktion in deine Datei zu integrieren:
  • 1. Öffne deine Testdatei und meine Beispiel-Datei.

  • 2. Wechsele zum VBA-Editor

  • 3. Kopiere per Drag&Drop aus meiner Datei das Modul "Modul_Uebertragen" in deine Datei.

  • 4. Modul "DieseArbeitsappe" (in deutscher Version) oder "Thisworkbook" (in englischer Version)
    hier findest du die beiden Makros
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Call UebertragenStopp
      
    End Sub
    
    Private Sub Workbook_Open()
        UserForm1.Show
        Call Uebertragen
        MsgBox "Makro zum Übertragen der Werte von ""Bewegungen"" nach ""Regal"" wurde aktiviert.",  _
    _
            vbOKOnly, "Makro: Workbook_Open"
    End Sub
    

  • Workbook_BeforeClose:
    Wenn es noch nicht in deiner Datei vorhanden ist, dann kopiere das komplette Makro.
    Wenn das Makro schon vorhanden ist, dann kopiere die Zeile
      Call UebertragenStopp
    

    und füge sie als 1. Zeile (nach der Sub-Zeile) des Makros ein.

    Workbook_Open:
    Dieses Makro sollte schon vorhanden sein, um die Intro-Userform anzuzeigen.
    Füge die Zeile
        Call Uebertragen
    

    als letzte Zeile vor End Sub ein.
    Die Zeile zur Anzeige der MsgBox ist nicht zwingend erforderlich. Sie dient nur zur Info des Anwenders. Die Info könntest du auch in dein Intro-Userform einbauen.

    LG
    Franz

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: Bastler
    Geschrieben am: 01.11.2020 18:16:55

    Hallo Franz,
    möglicherweise werden die 5 Daten überhaupt nicht der Reihe nach übertragen, bzw. das Datum ist nicht der letzte Wert. Daher eine Idee: Dem Macro eine Wartezeit von sagen wir mal 3 bis 5 Sekunden verpassen. Ist dies machbar?

    Viele Grüße
    Volker

    Betrifft: AW: Macro Sprung zu Ziel und Aktion
    von: fcs
    Geschrieben am: 01.11.2020 18:27:51

    Hallo Volker,

    siehe meine andere Antwort.

    Eine Verzögerung funktioniert nicht, da man den Start der Verzögerung nicht auslösen kann.
    Man kann aber per OnTime-Methode regelmäßig prüfen, ob eine oder mehrere Zeilen hinzugekommen sind und die Daten der neuen Zeilen übertragen.

    LG
    Franz

    Beiträge aus dem Excel-Forum zum Thema "Macro Sprung zu Ziel und Aktion"