This module was Created by dlmille of E-E March 20, 2011
This is an exercise to store active-x control settings in a workbook, on a sheet basis, preserving their settings if/when Excel gets "quirky" and shape sizes go askew
While the ListBox has an IntegralHeight property whose side-affect of a FALSE setting will keep that control from going askew, and while command buttons
have properties such as move/size with cells, etc., other controls are not as graceful.
The routine setControlsOnSheet():
1) obtains the 6 common control settings, for every OLEObject (active-x) control on the active sheet, and
2) stores those settings into a string array, sControlSettings(), and
3) adds/updates a defined name (which is hidden) with those settings.
The defined name for each control on a sheet is built up based on the active sheet name and the control name (which should create a unique instance)
Process:
The user creates whatever controls are going to be on the worksheet, and at any point, the setControlsOnSheet() routine can be run, to either
initially store the settings for all controls, refresh those settings, or add new settings (as it does this for every control on the sheet).
Care should be taken to ensure all settings "look right" (e.g., Excel has as yet to get "quirky", or the user has just adjusted one to many of
his controls and is ready to "save" their settings. Otherwise, any improperly sized controls' settings would get stored.
Rather than make this routine process intensive, the ThisWorkbook event for sheet activate will "reinitialize" all settings for all controls
that exist on the sheet that was just selected. This way, the control settings on the sheet are "restored" to their most recently saved settings,
thus "forever?" avoiding the Excel "quirky" resizing consequence.
As a potential enhancement, this app could be embedded in a class module as part of an add-in, thus keeping any related code out of the
users "normal" programming environment. E.g., the sheet activate event trapping would be captured in the class module, rather than the user having
to add it to his/her ThisWorkbook module.
Const CONTROL_OPTIONS = "Height;Left;Locked;Placement;Top;Width" 'some potentially useful settings to store and sustain
Function refreshControlsOnSheet(sh As Object)'routine enumerates all objects on the worksheet (sh), determines which have stored settings, then refreshes those settings from storage (in the defined names arena)
Dim myControl As OLEObject
Dim sBuildControlName As String
Dim sControlSettings As Variant
For Each myControl In ActiveSheet.OLEObjects
sBuildControlName = "_" & myControl.Name & "_Range" 'builds a range name based on the control name
'test for existance of previously-saved settings
On Error Resume Next
sControlSettings = Evaluate(sBuildControlName) 'ActiveWorkbook.Names(sBuildControlName).RefersTo 'load the array of settings
If Err.Number = 0 Then ' the settings for this control are in storage, so refresh settings for the control
myControl.Height = sControlSettings(1)
myControl.Left = sControlSettings(2)
myControl.Locked = sControlSettings(3)
myControl.Placement = sControlSettings(4)
myControl.Top = sControlSettings(5)
myControl.Width = sControlSettings(6)
End If
Err.Clear
On Error GoTo 0
Next myControl
End Function
Private Sub storeControlSettings(sControl As String)
Dim sBuildControlName As String
Dim sControlSettings(1 To 6) As Variant ' set to the number of control settings to be stored
Dim oControl As Variant
Set oControl = ActiveSheet.OLEObjects(sControl)
'store the settings to retain, so they can be reset on demand, thus avoiding Excel's resizing "problem"
'create array of settings to be stored, with order dictated by CONTROL_OPTIONS for consistency/documentation
sControlSettings(1) = oControl.Height
sControlSettings(2) = oControl.Left
sControlSettings(3) = oControl.Locked
sControlSettings(4) = oControl.Placement
sControlSettings(5) = oControl.Top
sControlSettings(6) = oControl.Width
sBuildControlName = "_" & sControl & "_Range" 'builds a range name based on the control name
Application.Names.Add Name:="'" & ActiveSheet.Name & "'!" & sBuildControlName, RefersTo:=sControlSettings, Visible:=False 'Adds the control's settings to the defined names area and hides the range name
End Sub
Public Sub setControlsOnSheet()
Dim myControl As OLEObject
If vbYes = MsgBox("If you click 'Yes' the settings for all controls on your active worksheet will be stored as they CURRENTLY exist. " & vbCrLf & vbCrLf _
& "Are you sure you want to continue (any previous settings will be overwritten)?", vbYesNo, "Store Control Settings") Then
For Each myControl In ActiveSheet.OLEObjects 'theoretically, one could manage settings for all controls of this type...
storeControlSettings (myControl.Name)
Next myControl
MsgBox "Settings have have been stored", vbOKOnly
End If
Application.EnableEvents = True 'to ensure we're set to "fire" on worksheet changes
End Sub
