使用 VBA 设置旋转按钮表单控件的最小值/最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20333719/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set Min/Max of Spin Button Form Control with VBA
提问by Trevor Eyre
I have a spin button on my worksheet (not in a userform), and I need to set the minimum and maximum values in VBA. Easy, right? I tried worksheetName.Shapes("shapeName").Min = xbut I get Run-time error 438: Object doesn't support this property or method.
我的工作表上有一个旋转按钮（不在用户表单中），我需要在 VBA 中设置最小值和最大值。很简单，对吧？我试过了，worksheetName.Shapes("shapeName").Min = x但我收到运行时错误 438：对象不支持此属性或方法。
I used Excel's macro recorder and changed the min and max of the spin button, and it recorded the following:
我使用 Excel 的宏记录器并更改了旋转按钮的最小值和最大值，它记录了以下内容：
ActiveSheet.Shapes("shapeName").Select
With Selection
.Min = x
.Max = y
End With
How is it that if I select the shape, I can then access its properties, but if I reference the shape directly I am unable to access the same properties? This does not make sense to me. Obviously, I would like to avoid selecting the shape and referencing "selection," as this generally is not best practice.
如果我选择形状，我可以访问它的属性，但如果我直接引用该形状，我将无法访问相同的属性，这是怎么回事？这对我来说没有意义。显然，我想避免选择形状并引用“选择”，因为这通常不是最佳实践。
回答by Doug Glancy
You can use the Shape.ControlFormatproperty:
您可以使用该Shape.ControlFormat属性：
Sub test()
Dim shp As Excel.Shape
Set shp = ActiveSheet.Shapes("Spinner 1")
With shp.ControlFormat
.Min = 2
.Max = 33
End With
End Sub
回答by David Zemens
This answer will be helpful, although not particularly intuitive...
这个答案会很有帮助，虽然不是特别直观......
Excel-VBA: Getting the values from Form Controls
As I mention in the comments above, it is peculiar. The SpinButton is a member of the worksheet's Shapescollection, but it does not allow you to access those properties as a shape directly (see Doug's answer for how to do this another way which is probably better).
正如我在上面的评论中提到的，它很奇怪。SpinButton 是工作表Shapes集合的成员，但它不允许您将这些属性作为形状直接访问（请参阅 Doug 的回答以了解如何以另一种方式执行此操作，这可能更好）。
Try:
尝试：
With ActiveSheet.Spinners("spinbutton1")
.Min = x
.Max = y
End With
Likewise, you can delcare a variable and iterate if you have multiple controls like this:
同样，如果您有多个像这样的控件，您可以删除一个变量并进行迭代：
Dim spinbtn as Spinner 'or As Variant
For each spinbtn in ActiveSheet.Spinners
spinbtn.Min = x
spinbtn.Max = y
Next
Etc.
等等。
回答by RamanaRaju
Simply you can try this:
你可以试试这个：
Private Sub SpinButton1_SpinDown()
TextBox3.Text = val(TextBox3.Text) - 1
If TextBox3.Text < 0 Then TextBox3.Text = 0
End Sub
