因此,我必须将一堆微调器对象移到一组单元格的旁边。
对于每个微调器,我需要运行以下语句
Worksheets("Serial").spnAspect.Left = Worksheets("Serial").Range("I12").Left - Worksheets("Serial").spnAspect.Width
Worksheets("Serial").spnAspect.Top = Worksheets("Serial").Range("I12").Top + Worksheets("Serial").Range("I12").Height / 2 _
- Worksheets("Serial").spnAspect.Height / 2
所以我想使它更整洁,所以我做了以下内容。我在这里遇到的唯一问题是如何告诉它将每个微调器与哪个单元对齐。这是“ I12”,在每次迭代中都应该是集合中thisControl的键。
Sub MoveSpinners()
Dim myControls As New Collection
Dim thisControl As Object
Dim mySheet As Worksheet
myControls.Add Worksheets("Serial").spnHeight, "I11"
myControls.Add Worksheets("Serial").spnAspect, "I12"
myControls.Add Worksheets("Serial").spnCropleft, "I13"
myControls.Add Worksheets("Serial").spnCropRight, "I14"
myControls.Add Worksheets("Serial").spnCropTop, "I15"
myControls.Add Worksheets("Serial").spnCropBottom, "I16"
Set mySheet = Worksheets("Serial")
For Each thisControl In myControls
thisControl.Left = mySheet.Range("I12").Left - thisControl.Width
thisControl.Top = mySheet.Range("I12").Top + thisControl.Height / 2 _
- thisControl.Height / 2
Next
End Sub
这是与在Collection对象上获取项目的键相同的问题,在这种情况下,该问题通过“使用字典对象代替”来回答,但在我的情况下,它不起作用,因为它不像粘贴粘贴那样整洁同一行很多次
我正在考虑的其他替代方法是fMoveSpinner(thisSpinner作为对象,myDestination作为范围),但是我希望保持这个小的MoveSpinner子例程独立。
如果您有更好的主意,我将很高兴听到它!
脚本字典的键和项都可以是对象。在这里,我将控件存储为键,将范围存储为项。这样,当您访问按键控件时,您可以轻松获取项目范围作为参考。
Sub MoveSpinners2()
Dim myControls As Object
Dim thisControl As Object
Dim mySheet As Worksheet
Dim x As Long
Set myControls = CreateObject("Scripting.Dictionary")
With Worksheets("Serial")
myControls.Add .spnHeight, .Range("I11")
myControls.Add .spnAspect, .Range("I12")
myControls.Add .spnCropleft, .Range("I13")
myControls.Add .spnCropRight, .Range("I14")
myControls.Add .spnCropTop, .Range("I15")
myControls.Add .spnCropBottom, .Range("I16")
End With
For Each thisControl In myControls
thisControl.Left = myControls(thisControl).Left - thisControl.Width
thisControl.Top = myControls(thisControl).Top + thisControl.Height / 2 _
- thisControl.Height / 2
Next
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句