Check Boxes from Form Controls!

Information about check boxes from form controls. You can check, reset, move, create and other things. The "Create Button" is to demonstrates that also. e.g. for a COMMANDBUTTON (ActiveX Controls) in the worksheet the code to set or reset the hook has to be changed. The files at the end of the article are Excelfiles of the version 2003 and 2007.


The following code belonged in "Module1"


Option Explicit
Sub CHECK_Formular_CheckBox()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If shpBox.Type <> msoOLEControlObject Then
If shpBox.FormControlType = xlCheckBox Then
shpBox.ControlFormat.Value = 1
End If
End If
Next
End Sub
Sub Reset_Formular_CheckBox()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If shpBox.AlternativeText Like "Box*" Then
shpBox.ControlFormat.Value = False
End If
Next
End Sub
Sub Reset_Formular_CheckBox_1()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If shpBox.FormControlType = xlCheckBox Then
shpBox.ControlFormat.Value = False
End If
Next
End Sub
Sub Reset_Formular_CheckBox_2()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If shpBox.FormControlType = 1 Then
shpBox.ControlFormat.Value = 0
End If
Next
End Sub
Sub Create_Button()
If Sheet1.OLEObjects.Count >= 1 Then Exit Sub
With Sheet1.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=435, Top:=225, Width:=60, Height:=30)
.Name = "CButton"
.Object.Caption = "Test"
End With
End Sub
Sub Delete_Button()
On Error Resume Next
Sheet1.Shapes("CButton").Delete
End Sub
Sub Reset_Formular_CheckBox_3()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If shpBox.Type <> msoOLEControlObject Then
If shpBox.FormControlType = xlCheckBox Then
shpBox.ControlFormat.Value = False
End If
End If
Next
End Sub
Sub Step_Right()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If shpBox.Type <> msoOLEControlObject Then
If shpBox.FormControlType = xlCheckBox Then
shpBox.Left = shpBox.Left + 50
End If
End If
Next
End Sub
Sub Step_Left()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If shpBox.Type <> msoOLEControlObject Then
If shpBox.FormControlType = xlCheckBox Then
shpBox.Left = shpBox.Left - 50
End If
End If
Next
End Sub
Sub CheckBox_Linked_Cell()
Dim shpBox As Shape
For Each shpBox In ActiveSheet.Shapes
If Left(shpBox.Name, 5) = "Check" Then
shpBox.ControlFormat.LinkedCell = shpBox.TopLeftCell.Offset(0, 1).Address
Debug.Print shpBox.ControlFormat.LinkedCell
End If
Next
End Sub
Sub CheckBox_Create()
Dim objBox As Object
Set objBox = ActiveSheet.CheckBoxes.Add(0, 0, 0, 0)
With objBox
.Left = Cells(22, 3).Left
.Top = Cells(22, 3).Top
.Height = 17.25
.Width = 96
.Caption = "New 11"
End With
End Sub


Sample 2003

Sample 2007

Kommentare

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...