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.