[Excel] Automatischer Eintrag nach Checkbox-Abfrage ins nächste sichtbare Sheet

[Likedeeler]

Liebe Community,

ich komme bei VBA mit meinen schwachen Kenntnissen an folgender Stelle nicht weiter:

* es gibt 9 Sheets in der Mappe, nur die erste ist standardmäßig eingeblendet
* auf Sheet1 ("Auswahl") finden mehrere Abfragen über Checkboxen und if...then statt, bis neben der Tab1 ein einziges weiteres sichtbares Sheet (bspw. "Materialliste 5") übrig bleibt
* das funktioniert in alle Richtungen und mit allen Checkbox-Kombinationen gut ;-)

* wir sind immer noch auf Sheet 1
* eine letzte Auswahl über eine von drei Checkboxen (3 Ölsorten) soll nun in besagtes übrig gebliebenes Sheet automatisch die Art.-Nr. in A7 und die Ölsorte in B7 eintragen
* für alle 8 Materiallisten sind nämlich alle 3 Ölsorten möglich


Könnt ihr mir bei diesem Eintrag helfen?


* falls möglich, soll der Eintrag erfolgen, ohne dass gleich in die Tabelle gesprungen wird
* am Ende soll nämlich noch ein Button eingebaut werden, der dann erst bei Klick in die fertige Materialliste führt



Ich danke euch im Voraus!
Marcus

 

[Alchemist]

Hallo,

so ganz verstehe ich es leider nicht. Aber hier ein paar Vorschläge:

Wert verändern, ohne auf Tabellenblatt zu wechseln:

ActiveSheet.Cells(1, 1).Value = Worksheets("Tabelle2").Cells(1, 1).Value
Worksheets(1).Cells(1, 1).Value = Worksheets(2).Cells(1, 1).Value

Du kannst mit ActiveSheet auf das aktuell geöffnete Sheet zugreifen, mit Worksheets(Index) oder Worksheets(NAME) auf ein anderes.
Cells(ZEILE,SPALTE) greift dann auf die jeweilige Zelle zu.

Ein Springen mit .select ist nicht notwendig, um auf ein anderes Tabellenblatt zuzugreifen. Im Gegenteil, meist führt das nur zu Verwirrung.

Ich hoffe das hilft schon mal, wenn noch was fehlt, bitte melden

 

[Likedeeler]

Hallo Alchemist,

ich teste es mal und melde mich dann. Vielen Dank und noch einen schönen Tag!

marcus

Ergänzung (15. Februar 2017)

Leider komme ich nicht so recht weiter.

Der wichtigste Punkt für mich wäre, dass bei Klick auf die letzte Checkbox in Sheet1 die Einträge "Ölsorte" und "Art-Nr" in jeweils eine bestimmte Cell im nächsten aktuell sichtbaren Sheet eingetragen werden.

Kann mir da jemand konkret den Weg dahin zeigen? Die if...then-Geschichten dazu werde ich dann hinkriegen, denke ich. Schließlich sollen die Einträge auch wieder verschwinden bei Abwahl der Checkbox.

Vielen Dank.

 

[cumulonimbus8]

Du wirst »im Code« dieser Checkbox alle nötigen Befehle anbringen müssen die je nach Zustand (True/False) passieren sollen - was ins Baltt einzutragen ist und wieder zu löschen.

Aus dem Bauch raus willst du etwas herstellen das man in Access einen Bericht, allgemeiner gesagt eine Datenbankabfrage, nennt.
Weswegen du das über so ein Häkchen abwickeln willst ist mir auch nicht recht klar. Es geht (doch) um Blätter die nicht standardmäßig sichtbar sind - da würde ich mit starren Formeln einfach dasjenige darstellen was da ist und die Betrachtung (der Resultate) über das Anschauen des des Blattes abwickeln.
Analog zum Datenbank-Konzept wäre eine UserForm als Schaltzentrale wohl in MS' Sinne diese Dinge in Excel abzuarbeiten. »Knöpfe« zum Drücken und eine Anzeigefläche liefern die Ergebnisse des Drückens.

CN8

 

[Janush]

Hallo,

Da muss ich CN8 zustimmen, das sieht aus wie eine Datenbank. Da kann man viele schöne Dinge mit VBA machen :-)

Gib uns doch mal ein Beispiel von dem was Du schon hast, denn momentan kann ich es mir noch nicht ganz vorstellen ;-)

Mfg

 

[Andreas_]

Ich habe schon Jahre nichts mehr mit VBA gemacht und mangels MS-Office habe ich das Ganze auch nicht testen können, aber so in etwa müsste das funktionieren:

Private Sub optOil1Checkbox_Click()
	
	' Zu beachten ist noch, dass die Click-Routine einer Checkbox auch bei der '
	' Initialisierung aufgerufen wird. Das muss natürlich abgefangen werden. '
	
	Dim shWorksheet As Worksheet
	Dim MaterialNr1 As String
	Dim OelSorte1 As String
	
	MaterialNr1 = "38260090"
	OelSorte1 = "Biodiesel"
	
	shWorksheet = Worksheets(getFirstVisibleSheet)
	If optOil1Checkbox.Value = True Then
		shWorksheet.Range("A7").Value = MaterialNr1
		shWorksheet.Range("B7").Value = OelSorte1
	Else ' Wenn das Häkchen wieder entfernt wird, auch die Werte wieder löschen '
		shWorksheet.Range("A7").clearcontents
		shWorksheet.Range("B7").clearcontents
	End if
	
End Sub

Function getFirstVisibleSheet() As Integer

	Dim iCounter As Integer
	
	' das erste sichtbare Sheet nach dem 1. herausfinden '
	For iCounter = 2 To Worksheets.Count
		If Worksheets(iCounter).Visible Then
			getFirstVisibleSheet = iCounter
			Exit For
		End If
	Next
	
End Function

 

[cumulonimbus8]

Und was stelle ich dann mit iCounter an?

Das Beispiel muss ins Codefenster des Blattes wo unsere Checkbox optOil1Checkbox zu liegen kommt, in einem Modul funktioniert es so wenig wie im Fenster eines anderen Blattes.

Es zeigt schön auf, dass das Makro Listen druchforsten muss um an Werte zu kommen. Hier werden sie ja starr zu Demonstration vorgegeben. Wie soll das Makro wissen welche Werte(quellen) es heranziehen muss?

Auch diese If-Konstruktion müsste, wenn nicht Werte indexiert werden - siehe eben, sehr Aufwendig kommen. Ich bleibe bei meiner Annahme, dass der Aufwand und die Idee dahinter nicht sehr praktisch sind. Warum soll das so gemacht werden, wem (nicht unbedingt: wessen) dient diese Idee, die Handhabung?

CN8

 

[Andreas_]

Und was stelle ich dann mit iCounter an?

Nichts. iCounter ist bloß lokal gültig und interessiert nicht weiter. Deswegen wurde die Variable ja lokal in einer Funktion deklariert.

Das Beispiel muss ins Codefenster des Blattes wo unsere Checkbox optOil1Checkbox zu liegen kommt, in einem Modul funktioniert es so wenig wie im Fenster eines anderen Blattes.

Wo ist da das Problem? Genau dort soll der Code ja auch hin?

Es zeigt schön auf, dass das Makro Listen druchforsten muss um an Werte zu kommen. Hier werden sie ja starr zu Demonstration vorgegeben. Wie soll das Makro wissen welche Werte(quellen) es heranziehen muss?

"Worksheets" ist eine Collection, die alle Tabellenblätter des aktuellen Excel-Dokuments enthält. Die ist immer gleich - also logischerweise von Microsoft "starr" vorgegeben. Siehe auch MSDN: Worksheets Object (Excel)
Worksheets(1) ist also immer das erste Tabellenblatt des aktuellen Dokuments. Egal wie das Exceldocument oder die einzelnen Tabellenblätter auch heißen. Deshalb starte ich ja auch bei 2, da Blatt 1 nach den Angaben des TE ja immer sichtbar bleibt.

Auch diese If-Konstruktion müsste, wenn nicht Werte indexiert werden - siehe eben, sehr Aufwendig kommen. Ich bleibe bei meiner Annahme, dass der Aufwand und die Idee dahinter nicht sehr praktisch sind. Warum soll das so gemacht werden, wem (nicht unbedingt: wessen) dient diese Idee, die Handhabung?

Wo ist der Aufwand? Mein Beispiel ist so gut wie fertig ... Die Click-Routine noch zweimal kopieren und dann die Bezeichnungen der Routinen an die Namen der Checkboxen anpassen. Es fehlt eigentlich nur der Test, ob das Excel-Dokument schon vollständig geladen ist, da die Click-Routinen ein erstes Mal bereits aufgerufen werden, wenn die Steuerelemente beim Laden des Dokuments initialisiert werden. Da davon aber nicht nur die 3 betreffenden Checkboxen betroffen sind, gehe ich davon aus, dass so ein Test schon existiert und nur von anderer Stelle kopiert werden muss ...

 

[Likedeeler]

Zuerst vielen Dank für alle Antworten. Ich werde erst mittags dazu kommen, wieder mit dem Code spielen zu können, aber gern schildere ich euch den gedachten Sinn, damit ihr es euch besser vorstellen könnt. Und noch eins: Ja, es ist wahrscheinlich nicht der beste Weg, die Sache anzugehen, aber mir fiel mit meinen Kenntnissen kein anderer ein und es soll auch ein Dienst an einer Kollegin sein, die mich mit Kuchen versorgt.


Ausgangssituation
Die Firma betreibt 220 Anlagen im ganzen Land, die jeweils eine Halbjahreswartung (HJW) und Jahreswartung (JW) haben müssen. Beide Wartungen erfordern unterschiedliche Materiallisten für die Servicetechniker vor Ort. Bislang existierten also 440 Excel-Materiallisten, die man aktuell halten musste. Ändert sich ein Schmierstoff, eine Artikelnummer oder fällt was weg oder kommt hinzu, wurden alle Dateien bearbeitet, damit die Servicetechniker aktuelle Materiallisten haben. Das ist natürlich .

Ich mache in der Abteilung einige Zeit Krankheitsvertretung und habe nun diese 440 Listen auf 8 zusammenfassen können, denn es gibt (wenn man das Öl erstmal weglässt) nur 4 Anlagentypen und dann jeweils eine HJW- und eine JW-Materialliste. Diese acht Listen sind fertig in der Mappe. Schaut mal aufs Bild: die Abfragen funktionieren wunderbar in alle Richtungen und die entsprechenden Tabellen werden ein- oder ausgeblendet, auch der Löschen-Button arbeitet wunderbar. Jetzt hänge ich praktisch "nur noch" am Öleintrag.


Plan
Wie gesagt: mit Mausklick auf eine Ölsorte soll in die nächste sichtbare Tabelle die Art-Nr. und das Öl eingetragen werden. Welches Öl in welche Anlage gehört, entnimmt man aus einer kleinen Übersicht rechts neben dem blauen Abfragefeld.

Dann noch ein Gehe-zu-Button zu der dann vollständigen Liste und dann kann gedruckt werden. Einen Druck-Button o.ä. kann ich dann einbauen. Ich möchte der aktuell kranken Kollegin halt nur die Arbeit ersparen. Wenn sich mal was ändert, muss sie maximal 8 Tabellen in einer Mappe bearbeiten. Das soll der Sinn sein. Mehr kann man ihr (und mir) von den Kenntnissen nicht zumuten.


Ich danke euch soweit

 

[Andreas_]

Der Code für Deinen "Gehe-zu-Button" (Ich benutze wieder die Funktion getFirstVisibleSheet aus dem vorigen Beispiel).

Private Sub butGeheZu_Click()
        Worksheets(getFirstVisibleSheet).Activate
End Sub

Das Drucken wird etwas umfangreicher, je nachdem, was Du alles Drucken willst.

Ja, es ist wahrscheinlich nicht der beste Weg, die Sache anzugehen, aber mir fiel mit meinen Kenntnissen kein anderer ein

Es mag nicht der eleganteste Weg sein, aber sicher der am schnellsten zu realisierende. Ich weiß nicht, welche Excel-Version Du benutzt, Wenn Sie nicht zu aktuell ist - ich habe irgendwo noch eine VM mit Office 2003 rumliegen und könnte mir das im Detail ansehen, falls Du Probleme hast ... aber erst wenn Du es vorher selber probiert hast und auf konkrete Probleme stößt.

 

[Likedeeler]

Hallo Andreas,

vielen Dank. Ich freue mich schon auf das Ergebnis. Ich habe nun eine Fehlermeldung bekommen und sie dir mal ausgeschnitten:

 

[Andreas_]

Ja, einer der Fehler, weil ich schon länger raus bin ... "Set" vergessen ... das muss man benutzen, wenn man Objekte zuweist (Set Statement)
das muss heißen:

Set shWorksheet = Worksheets(getFirstVisibleSheet)

 

[Likedeeler]

Der automatische Eintrag funktioniert jetzt. Vielen Dank dafür, Andreas! Es geht aber nur einmal, d.h. mein Löschen-knopf funktioniert nach dem ersten Sprung zur Liste nicht mehr korrekt und auch eine manuelle Abwahl oder neue Auswahl funktioniert nicht mehr. Der Laufzeitfehler aus meinm letzten Post kommt dann wieder.

Es wäre schön, wenn man das Prozedere mehrfach durchlaufen könnte. Hier mal die letzten Codezeilen:

'Löschen-Knopf'
Sub loeschen_Click()
Dim cbo As OLEObject
For Each cbo In ActiveSheet.OLEObjects
    If Left(cbo.progID, 11) = "Forms.Check" Then
        cbo.Object.Value = False
    End If
Next
End Sub



    Private Sub Addinol_Click()
        
        ' Zu beachten ist noch, dass die Click-Routine einer Checkbox auch bei der '
        ' Initialisierung aufgerufen wird. Das muss natürlich abgefangen werden. '
        
        Dim shWorksheet As Worksheet
        Dim MaterialNr1 As String
        Dim OelSorte1 As String
        
        MaterialNr1 = "123456789"
        OelSorte1 = "Addinol-test"
        
        Set shWorksheet = Worksheets(getFirstVisibleSheet)
        If Addinol.Value = True Then
            shWorksheet.Range("A12").Value = MaterialNr1
            shWorksheet.Range("B12").Value = OelSorte1
        Else
            shWorksheet.Range("A12").ClearContents
            shWorksheet.Range("B12").ClearContents
        End If
        
    End Sub
     
    Function getFirstVisibleSheet() As Integer
     
        Dim iCounter As Integer
        
        ' das erste sichtbare Sheet nach dem 1. herausfinden '
        For iCounter = 2 To Worksheets.Count
            If Worksheets(iCounter).Visible Then
                getFirstVisibleSheet = iCounter
                Exit For
            End If
        Next
        
    End Function
    
   'Gehe zu Liste'

    Private Sub gehezu_Click()
            Worksheets(getFirstVisibleSheet).Activate
    End Sub

   'Sofortdruck Liste'

    Private Sub drucke_Click()
            Worksheets(getFirstVisibleSheet).PrintOut
    End Sub
    
    'Druckoptionen'
    
    Private Sub druckop_Click()
            Application.Dialogs(xlDialogPrint).Show
    End Sub

 

[Andreas_]

Der automatische Eintrag funktioniert jetzt. Vielen Dank dafür, Andreas! Es geht aber nur einmal, d.h. mein Löschen-knopf funktioniert nach dem ersten Sprung zur Liste nicht mehr korrekt und auch eine manuelle Abwahl oder neue Auswahl funktioniert nicht mehr.

Eventuell liegt es daran, dass Du ActiveSheet benutzt. Mit dem Seitenwechsel ist ActiveSheet nicht mehr dasselbe.

Versuche mal "ActiveSheet.OLEObjects" durch "WorkSheets(1).OLEObjects" zu ersetzen.

Noch eins - ich hatte geschrieben, dass das Initialisieren der Checkboxen das Clickevent auslöst. Am einfachsten lässt es sich durch eine globale Variable umgehen. Füge die Zeilen

Dim bAutomatic As Boolean
bAutomatic = False

am Anfang der Seite ein.

Geänderter Loeschen-Knopf

'Löschen-Knopf'
Sub loeschen_Click()

    Dim cbo As OLEObject
    bAutomatic = True
 
    For Each cbo In WorkSheets(1).OLEObjects
        If Left(cbo.progID, 11) = "Forms.Check" Then
            ' die nächste Zeile löst bei jeder betroffenen Checkbox ein Click-Event aus.'
            cbo.Object.Value = False
        End If
    Next

    bAutomatic = False

End Sub

geänderte Click-Prozedure

    Private Sub Addinol_Click()
        
        Dim shWorksheet As Worksheet
        Dim MaterialNr1 As String
        Dim OelSorte1 As String
        
        ' Zu beachten ist noch, dass die Click-Routine einer Checkbox auch bei der '
        ' Initialisierung aufgerufen wird. Das wird mit der folgenden Anweisung unterbunden. '
        if bAutomatic = True Then 
            Exit Sub
        End If

        MaterialNr1 = "123456789"
        OelSorte1 = "Addinol-test"
        
        Set shWorksheet = Worksheets(getFirstVisibleSheet)
        If Addinol.Value = True Then
            shWorksheet.Range("A12").Value = MaterialNr1
            shWorksheet.Range("B12").Value = OelSorte1
        Else
            shWorksheet.Range("A12").ClearContents
            shWorksheet.Range("B12").ClearContents
        End If
        
    End Sub

Falls das noch nicht hilft, mach bitte wieder einen Screenshot, damit ich genau sehe, wo der Fehler hervorgerufen wird.