LinkedCell bei CheckBox in Schleife
28.01.2003 09:31:43
Michael
ich habe da noch ein Problem. Wenn ich in einer Schleife CheckBox in einem Tabellenblatt erstelle und diese genau auf eine Zelle plaziere, wie bekomme ich es hin der CheckBox genau Diese Zelle als LinkedCell zu zuweisen. Nachstehend der Code den ich bisher benutze.
Vielen Dank für eure Hilfe
Private Sub Workbook_Open()
Set Mappe = Workbooks("Lager.xls")
Set STABI = Mappe.Worksheets("Stabilisatoren")
Dim rng As Range
Dim vCol As Variant
Dim ZB215 As Long
Dim ZB220 As Long
Dim ZB900 As Long
Dim ZB921 As Long
Dim ZChima As Long
ZB215 = (STABI.[b12].End(xlDown).Offset(0, 2).Row)
For i = 12 To ZB215
Worksheets("Stabilisatoren").Select
Cells(12, 2).Activate
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, IconLabel:="", Left:=Range("A12").Left, Top:=Range("A" & i).Top, Width:=12, Height:= _
12).Select
Next i
ZB220 = (STABI.[e12].End(xlDown).Offset(0, 2).Row)
For i = 12 To ZB220
Worksheets("Stabilisatoren").Select
Cells(12, 2).Activate
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, IconLabel:="", LinkedCell:=Range("D" & i), Left:=Range("D12").Left, Top:=Range("D" & i).Top, Width:=12, Height:= _
12).Select
STABI.OLEObjects(i).
Next i
ZB900 = (STABI.[h12].End(xlDown).Offset(0, 2).Row)
For i = 12 To ZB900
Worksheets("Stabilisatoren").Select
Cells(12, 2).Activate
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, IconLabel:="", Left:=Range("G12").Left, Top:=Range("G" & i).Top, Width:=12, Height:= _
12).Select
Next i
ZB921 = (STABI.[k12].End(xlDown).Offset(0, 2).Row)
For i = 12 To ZB921
Worksheets("Stabilisatoren").Select
Cells(12, 2).Activate
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, IconLabel:="", Left:=Range("J12").Left, Top:=Range("J" & i).Top, Width:=12, Height:= _
12).Select
Next i
ZChima = (STABI.[n12].End(xlDown).Offset(0, 2).Row)
For i = 12 To ZChima
Worksheets("Stabilisatoren").Select
Cells(12, 2).Activate
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, IconLabel:="", Left:=Range("M12").Left, Top:=Range("M" & i).Top, Width:=12, Height:= _
12).Select
Next i
'Worksheets("Stabilisatoren").Unprotect Password:="27823142782"
Set rng = Range(Cells(12, 1), Cells(Cells(12, 2).End(xlDown).Row, 15))
ActiveSheet.PageSetup.PrintArea = rng.Address
vCol = 3
Set rng = Cells(Rows.Count, CInt(vCol)).End(xlUp)
Range(rng.Address(False, False)).Select
For i = STABI.OLEObjects.Count To 1 Step -1
If Mid((STABI.OLEObjects(i).Name), 1, 8) = "CheckBox" Then
STABI.OLEObjects(i).Select
Selection.Delete
End If
Next i
End Sub