Wrote the following to capture the default state 'location' properties of all Active X controls in workbook and pass to an array. I would like to expand this functionallity to do the exact same thing for Form Controls. Any assistance or insight is greatly appreciated!
For Each wsWorksheet In ThisWorkbook.Sheets
If wsWorksheet.OLEObjects.Count > 0 Then
For Each oleControl In wsWorksheet.OLEObjects
If oleControl.progID = "Forms.CommandButton.1" Or _
oleControl.progID = "Forms.ToggleButton.1" Or _
oleControl.progID = "Forms.CheckBox.1" Or _
oleControl.progID = "Forms.OptionButton.1" Or _
oleControl.progID = "Forms.SpinButton.1" Then 'add more types if needed
With oleControl
ReDim Preserve pubvarControls(1 To 6, 1 To n)
pubvarControls(1, n) = .Parent.Name
pubvarControls(2, n) = .Name
pubvarControls(3, n) = .Height
pubvarControls(4, n) = .Width
pubvarControls(5, n) = .Left
pubvarControls(6, n) = .Top
End With
Else
n = n - 1 'reset index if control is not the right type, preventing empty entries
End If
n = n + 1
Next
End If
Next
Bookmarks