这个模块是由E-E的dlmille于2011年3月20日创建的。
这是一个练习,用于在工作簿上按表格保存Active-X控件的设置,以保留它们的设置,如果/当Excel变得“古怪”时形状大小会失调。虽然ListBox有一个IntegralHeight属性,其FALSE设置的副作用将使该控件不会失调,而命令按钮具有诸如随单元格移动/调整大小等属性,但其他控件则没有那么优雅。
setControlsOnSheet()例程:
1) 获取当前活动工作表上每个OLEObject(Active-X)控件的6个常见控件设置,
2) 将这些设置存储到一个字符串数组sControlSettings()中,
3) 添加/更新一个定义名称(隐藏),其中包含这些设置。
每个工作表上每个控件的定义名称都是基于活动工作表名称和控件名称(应该创建一个唯一实例)构建的。
过程:
用户创建将要放置在工作表上的任何控件,并且在任何时候,可以运行setControlsOnSheet()例程来初始存储所有控件的设置、刷新这些设置或添加新的设置(因为它对工作表上的每个控件都执行此操作)。
应注意确保所有设置“看起来正确”(例如,Excel尚未变得“古怪”,或者用户刚刚调整了太多控件并准备“保存”它们的设置。否则,任何大小不当的控件设置都将被存储。
为了避免这种例行程序过于繁琐,工作簿事件可在选定表格激活时“重新初始化”该表格上存在的所有控件的所有设置。这样,该表格上的控件设置将“恢复”到其最近保存的设置,从而“永远?”避免了Excel“古怪”的调整后果。
作为一个潜在的增强功能,该应用程序可以作为附加组件的一部分嵌入到类模块中,从而使任何相关代码都不会出现在用户的“正常”编程环境中。例如,表格激活事件捕获将被捕获在类模块中,而不是用户必须将其添加到他/她的ThisWorkbook模块中。
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
.Shapes
与.OLEObjects
一样存在分组问题。 - enderland