Control Format Object Example

This example resets all the list boxes, drop-downs, scrollbars, spinners, and checkboxes on the sheet:

Sub ResetFormControls() Dim oShp As Shape Dim oCF As ControlFormat

'Loop through all the shapes in the sheet For Each oShp In ActiveSheet.Shapes 'Is this a Forms control? If oShp.Type = msoFormControl Then

'Yes, so get the ControlFormat object Set oCF = oShp.ControlFormat

'Reset the control as appropriate Select Case oShp.FormControlType Case xlListBox, xlDropDown oCF.RemoveAllItems

Case xlSpinner, xlScrollBar oCF.Value = oCF.Min

Case xlCheckBox oCF.Value = xlOff

End Select End If Next End Sub

0 0


  • maximilian
    What is controlformat object?
    8 years ago

Post a comment