Class ControlFormat (Excel VBA)
Contains Microsoft Excel control properties.
The main procedure of class ControlFormat is AddItem
Methods
This is the main method of the ControlFormat class
AddItem - Adds an item to a list box or a combo box.
With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
For x = 1 To 10
lb.ControlFormat.AddItem x
Next
End With
Other Methods
RemoveItem - Removes one or more items from a list box or combo box.
Set lbcf = Worksheets(1).Shapes(2).ControlFormat
lbcf.RemoveItem lbcf.ListIndex
Properties
DropDownLines returns or sets the number of list lines displayed in the drop-down portion of a combo box.
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=10, Top:=10, Width:=100, Height:=10)
.ControlFormat.DropDownLines = 10
End With
Enabled true if the object is enabled.
LargeChange returns or sets the amount that the scroll box increments or decrements for a page scroll (when the user clicks in the scroll bar body region).
Set sb = Worksheets(1).Shapes.AddFormControl(xlScrollBar, _
Left:=10, Top:=10, Width:=10, Height:=200)
With sb.ControlFormat
.LinkedCell = "D1"
.Max = 100
.Min = 0
.LargeChange = 10
.SmallChange = 2
End With
LinkedCell returns or sets the worksheet range linked to the control's value. If you place a value in the cell, the control takes this value. Likewise, if you change the value of the control, that value is also placed in the cell.
With Worksheets(1)
Set cb = .Shapes.AddFormControl(xlCheckBox, 10, 10, 100, 10)
cb.ControlFormat.LinkedCell = "A1"
End With
ListCount returns the number of entries in a list box or combo box. Returns 0 (zero) if there are no entries in the list.
Set cf = Worksheets(1).Shapes(1).ControlFormat
cf.DropDownLines = cf.ListCount
ListFillRange returns or sets the worksheet range used to fill the specified list box. Setting this property destroys any existing list in the list box.
With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
lb.ControlFormat.ListFillRange = "A1:A10"
End With
ListIndex returns or sets the index number of the currently selected item in a list box or combo box.
Set lbcf = Worksheets(1).Shapes(2).ControlFormat
lbcf.RemoveItem lbcf.ListIndex
LockedText true if the text in the specified object will be locked to prevent changes when the workbook is protected.
Worksheets(1).ChartObjects(1).LockedText = True
Max returns or sets the maximum value of a scroll bar or spinner range. The scroll bar or spinner won't take on values greater than this maximum value.
Set sb = Worksheets(1).Shapes.AddFormControl(xlScrollBar, _
Left:=10, Top:=10, Width:=10, Height:=200)
With sb.ControlFormat
.LinkedCell = "D1"
.Max = 100
.Min = 0
.LargeChange = 10
.SmallChange = 2
End With
Min returns or sets the minimum value of a scroll bar or spinner range. The scroll bar or spinner won't take on values less than this minimum value.
Set sb = Worksheets(1).Shapes.AddFormControl(xlScrollBar, _
Left:=10, Top:=10, Width:=10, Height:=200)
With sb.ControlFormat
.LinkedCell = "D1"
.Max = 100
.Min = 0
.LargeChange = 10
.SmallChange = 2
End With
MultiSelect returns or sets the selection mode of the specified list box. Can be one of the following constants: xlNone, xlSimple, or xlExtended.
Set lb = Worksheets(1).Shapes.AddFormControl(xlListBox, _
Left:=10, Top:=10, Height:=100, Width:100)
lb.ControlFormat.MultiSelect = xlSimple
Parent returns the parent object for the specified object. Read-only.
PrintObject true if the object will be printed when the document is printed.
SmallChange returns or sets the amount that the scroll bar or spinner is incremented or decremented for a line scroll (when the user chooses an arrow).
Set sb = Worksheets(1).Shapes.AddFormControl(xlScrollBar, _
Left:=10, Top:=10, Width:=10, Height:=200)
With sb.ControlFormat
.LinkedCell = "D1"
.Max = 100
.Min = 0
.LargeChange = 10
.SmallChange = 2
End With
Value returns or sets a Long value that represents the name of the specified control format.