Checkbox kopieren m. Makro u. Zellverknüpfung +1

Forumthread: Checkbox kopieren m. Makro u. Zellverknüpfung +1

24.09.2014 12:33:19

Thomas

Hallo Freunde
Ich habe eine Tabelle erstellt in der am Anfang jeder Zeile in "A" eine Checkbox mit Verknüpfung auf drunter liegende Zelle ist, die je nach aktiv oder inaktiv die beiden darunterliegenden Zeilen ein oder ausblendet.
Nun habe ich ein Makro welches die selektierte Zeile um eins nach unten kopiert.
Dabei soll auch die Checkbox mit kopiert werden, mit samt Makro zum ein und ausblenden und der Zellverknüpfung. Nur soll das alles um den Offset +1 verschoben sein.
Als Beispiel
In A1 ist die Checkbox mit Verknüpfung auf A1. Wenn man diese aktiv setzt werden Zeile 2 u. 3 eingeblendet.
Nun soll durch einen Button die Zeile 1,2 u.3 kopiert werden, inklusive der Checkbox und der damit verbundenen Zellverknüpfung und Makros. Dies soll sich jetzt aber so verhalten, dass die Checkbox die Verknüpfung auf "A4" hat und die Zeilen 5 u.6 ein und ausblenden soll.
Ich hoffe ihr könnt mir folgen
Gruß Thomas

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Checkbox kopieren m. Makro u. Zellverknüpfung +1

24.09.2014 14:56:41

fcs

Hallo Thomas,
benutze die Checkboxen aus den Formularsteuerelementen, dann hast du hier weniger Probleme.
Als Code in einem allgemeinen Modul benötigst du dann nur:

'Code in einem allgemeinen Modul
Sub Ein_Ausblenden()
'Dieses Makro der(den) Checkbox(en) in Spalte A zuweisen
Dim objShape As Shape
Set objShape = ActiveSheet.Shapes(Application.Caller)
With objShape.TopLeftCell
If .Column = 1 Then
Range(Rows(.Row + 1), Rows(.Row + 2)).EntireRow.Hidden = _
objShape.ControlFormat.Value = -4146
.Value = Not objShape.ControlFormat.Value = -4146
End If
End With
End Sub
Sub Kopie_3_Zeilen()
'Kopiert die Zeilen 2 bis 4 und fügt sie vor Zeile 5 ein - inkl. Checkbox in Zeile 2
If Application.CopyObjectsWithCells = False Then Application.CopyObjectsWithCells = True
Range(Rows(2), Rows(4)).Copy
Range(Rows(5), Rows(7)).Insert
End Sub

Gruß
Franz

AW: Checkbox kopieren m. Makro u. Zellverknüpfung +1

25.09.2014 08:43:37

Thomas

Hallo Franz
Ich danke dir. Es hat alles geklappt wie es sollte. Habe es noch ein wenig umgeschrieben, damit er nicht immer in die selben Zeilen kopiert und an selber Stelle einfügt und schon hatte ich was ich wollte.
Jetzt ist ein Folgeproblem aufgetreten.
Die Zeile der Checkbox und die beiden drunter liegenden Zeilen werden ja nun kopiert und direkt unter die kopierten Zeilen gehängt.
Das Problem ist jetzt, dass in Abhängigkeit des Zustandes der Checkbox, entweder die eingeblendeten Zeilen im aktivierten Zustand oder im deaktivierten Zustand die darunter liegenden Zeilen, die manchmal auch schon ausgefüllt sein können, kopiert werden.
Mein Gedanke war jetzt, die Checkbox in der selektierten Zeile aktiv zu schalten und somit dafür zu sorgen, dass er in jedem Fall die 3 richtigen Zeilen kopiert.
Jetzt die Frage
Wie kann ich das Aktivieren der Checkbox noch mit in den Kopiervorgang einbauen?
Ich muss ja sagen, dass er sich die Checkbox die in der markierten Zeile ist raussuchen soll und diese
aktiv setzen muss bevor er anfängt zu kopieren. Allerdings kann ich die Checkbox auch nicht mit einem Namen ansprechen, da es ja je nach markierter Zeile eine andere Checkbox ist.
Gruß Thomas

AW: Checkbox kopieren m. Makro u. Zellverknüpfung +1

25.09.2014 15:26:40

fcs

Hallo Thomas,
wenn du einen beliebigen Zeilenblock kopieren und unterhalb einfügen willst, dann wird es etwas komplizierter. Es ist aber nicht unbedingt erforderlich, die Zeilen zu einer Checkbox ggf. erst einzublenden. Man kann es auch so steuern, dass erst in den kopierten Zeilen die Zeilen ggf. eingeblendet werden.
Gruß
Franz

Sub Kopie_3_Zeilen()
Dim Zeile As Long, objShape As Shape
Dim wks As Worksheet
Set wks = ActiveSheet
Zeile = ActiveCell.Row
'Checkbox in Spalte A in der Zeile mit der aktiven Zelle suchen
Set objShape = fncFindCheckbox(objWks:=wks, Zeile:=Zeile, Spalte:=1)
If objShape Is Nothing Then
MsgBox "Keine Checkbox in aktiver Zeile. " _
& "Bitte Zelle in Zeile mit Checkbox selektieren"
Else
With wks
If Application.CopyObjectsWithCells = False Then _
Application.CopyObjectsWithCells = True
'Zeilen kopieren und unterhalb des Blocks einfügen
.Range(.Rows(Zeile), .Rows(Zeile + 2)).Copy
.Range(.Rows(Zeile + 3), .Rows(Zeile + 5)).Insert
Zeile = Zeile + 3
'Prüfen, ob Checkbox deaktiviert ist
If objShape.ControlFormat.Value = -4146 Then
'kopierte Checkbox suchen
Set objShape = fncFindCheckbox(objWks:=wks, Zeile:=Zeile, Spalte:=1)
'Neue Checkbox auf True setzen und Zeilen einblenden
objShape.ControlFormat.Value = 1
.Cells(Zeile, 1) = True
.Range(.Rows(Zeile + 1), .Rows(Zeile + 2)).EntireRow.Hidden = False
End If
'Zelle in kopiertem Block selektieren
Cells(Zeile, 2).Select
'Altdaten in koierten Zeilen löschen --- ggf. weglassen
.Range(.Cells(Zeile, 2), .Cells(Zeile + 2, 10)).ClearContents
End With
End If
End Sub
Public Function fncFindCheckbox(objWks As Worksheet, Zeile As Long, _
Optional Spalte As Long = 1) As Shape
'Checkbox finden mit TopLeftCell in der Zeile und Spalte
Dim objShape As Shape
For Each objShape In objWks.Shapes
With objShape
'Prüfen der Steuerelementeigenschaften
If .TopLeftCell.Column = Spalte Then
If .TopLeftCell.Row = Zeile Then
If .Type = msoFormControl Then
If .FormControlType = xlCheckBox Then
Set fncFindCheckbox = objShape
Exit For
End If
End If
End If
End If
End With
Next
End Function

AW: Checkbox kopieren m. Makro u. Zellverknüpfung +1

26.09.2014 11:38:14

Thomas

Hallo Franz
Als Erstes mal 1000 Dank für deine Hilfe.
Alles funktioniert wunderbar.
Ich danke dir auch für das Einbauen der "Inhalte der neuen Zeilen löschen" Funktion
Hab hier und da noch ein wenig Anpassungen vorgenommen, da ich nicht alle neu kopierten Zeileninhalte löschen wollte. Es sollte nur Zeile 1 und Zeile 3 gelöscht werden. Zeile 2 sollte bleiben. Und hab dafür auch nur 6 Stunden gebraucht ;-), aber ich habs hinbekommen.
Mit bestem Dank und Gruß
Thomas

Infobox / Tutorial

Checkbox kopieren und Zellverknüpfung in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Checkbox erstellen:
- Gehe zu Entwicklertools > Einfügen und wähle das Kontrollkästchen aus den Formularsteuerelementen aus.
- Platziere die Checkbox in der gewünschten Zelle (z.B. A1).
Zellverknüpfung hinzufügen:
- Rechtsklicke auf die Checkbox und wähle Steuerelement formatieren.
- Unter dem Tab Steuerung kannst du den Zellbezug für die Verknüpfung eingeben (z.B. A1).

Makro erstellen:

Öffne den VBA-Editor (Alt + F11).
Füge ein neues Modul hinzu und verwende den folgenden Code, um die Checkbox und die Zeilen zu kopieren:

Sub Kopie_3_Zeilen()
   Dim Zeile As Long, objShape As Shape
   Dim wks As Worksheet
   Set wks = ActiveSheet
   Zeile = ActiveCell.Row

   Set objShape = fncFindCheckbox(objWks:=wks, Zeile:=Zeile, Spalte:=1)
   If objShape Is Nothing Then
       MsgBox "Keine Checkbox in aktiver Zeile."
       Exit Sub
   End If

   If Application.CopyObjectsWithCells = False Then _
       Application.CopyObjectsWithCells = True

   .Range(.Rows(Zeile), .Rows(Zeile + 2)).Copy
   .Range(.Rows(Zeile + 3), .Rows(Zeile + 5)).Insert
End Sub

Checkbox aktivieren:

Um die Checkbox zu aktivieren und die Zeilen einzublenden, ergänze den Code wie folgt:

If objShape.ControlFormat.Value = -4146 Then
   objShape.ControlFormat.Value = 1
   .Range(.Rows(Zeile + 1), .Rows(Zeile + 2)).EntireRow.Hidden = False
End If

Makro ausführen:
- Verknüpfe das Makro mit einem Button oder führe es direkt im VBA-Editor aus.

Häufige Fehler und Lösungen

Checkbox wird nicht kopiert:
- Stelle sicher, dass du die Checkbox als Formularsteuerelement verwendest und nicht als ActiveX-Steuerelement.
Zellverknüpfung funktioniert nicht:
- Überprüfe den Zellbezug in den Eigenschaften der Checkbox. Er sollte auf die Zelle zeigen, die du verknüpfen möchtest.
Makro läuft nicht:
- Achte darauf, dass das Makro in einem allgemeinen Modul gespeichert ist, nicht in einem Arbeitsblatt-Modul.

Alternative Methoden

Excel Checkbox kopieren ohne Makro:
- Du kannst die Checkbox manuell kopieren, indem du sie auswählst, Strg+C drückst und sie an der gewünschten Stelle mit Strg+V einfügst. Vergiss nicht, die Zellverknüpfung manuell anzupassen.
Zusätzliche Software:
- Verwende Tools wie Kutools for Excel, die erweiterte Funktionen zum Kopieren und Verknüpfen von Kontrollkästchen bieten.

Praktische Beispiele

Beispiel eines einfachen Arbeitsblatts mit Checkboxen in Spalte A, die die Zeilen 2 und 3 basierend auf dem Zustand der Checkbox in Zeile 1 ein- oder ausblenden.

Verwende den folgenden VBA-Code, um alle Checkboxen in einer Zeile zu aktivieren:

Sub AlleCheckboxenAktivieren()
   Dim objShape As Shape
   For Each objShape In ActiveSheet.Shapes
       If objShape.Type = msoFormControl Then
           If objShape.FormControlType = xlCheckBox Then
               objShape.ControlFormat.Value = 1
           End If
       End If
   Next objShape
End Sub

Tipps für Profis

Effiziente Nutzung von Zellverknüpfungen:
- Verwende klare und konsistente Zellbezüge, um die Verwaltung von Kontrollkästchen zu erleichtern.
Erweiterte VBA-Techniken:
- Nutze Arrays oder Collections, um mehrere Checkboxen effizient zu verwalten und zu kopieren.
Fehlerprotokollierung:
- Implementiere eine Fehlerbehandlung in deinem VBA-Code, um Probleme beim Kopieren der Checkboxen zu protokollieren.

FAQ: Häufige Fragen

1. Wie kann ich eine Checkbox mit einer Zelle verknüpfen?
Du kannst dies tun, indem du die Checkbox auswählst, mit der rechten Maustaste darauf klickst und dann im Menü Steuerelement formatieren den Zellbezug angibst.

2. Ist es möglich, mehrere Checkboxen gleichzeitig zu kopieren?
Ja, du kannst mehrere Checkboxen auswählen und sie dann kopieren. Achte darauf, die Zellverknüpfungen für jede Checkbox nach dem Einfügen anzupassen.

3. Was tun, wenn das Makro nicht funktioniert?
Überprüfe die Makrosicherheitseinstellungen in Excel und stelle sicher, dass Makros aktiviert sind. Prüfe auch, ob der VBA-Code richtig eingegeben wurde.

Beliebteste Forumthreads (12 Monate)

Checkbox kopieren m. Makro u. Zellverknüpfung +1

Forumthread: Checkbox kopieren m. Makro u. Zellverknüpfung +1

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Checkbox kopieren und Zellverknüpfung in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread