vba 循环浏览工作表命令按钮并更改可见性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17038276/
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
looping through worksheet command buttons and change visibility
提问by user2473428
I'm trying to write a code to make a number of buttons visible depending on a cell value
我正在尝试编写代码以根据单元格值显示多个按钮
I have 10 command buttons all are invisible and I want to show only the first x x is the value of cell "A1" in "Sheet1" (will be from 1 to 10) Command buttons names are default names (CommandButton4, CommandButton5, ... , CommandButton13)
我有 10 个命令按钮都是不可见的，我只想显示第一个 xx 是“Sheet1”中单元格“A1”的值（将从 1 到 10）命令按钮名称是默认名称（CommandButton4、CommandButton5、.. , 命令按钮 13)
Note: I'm working with a worksheet not a userform
注意：我正在使用工作表而不是用户表单
This is my code but i need something shorter and more pro and efficient
这是我的代码，但我需要更短、更专业、更高效的东西
Private Sub CommandButton15_Click()
Dim i As Long
Dim CommandButton() As Variant
Application.ScreenUpdating = False
CommandButton = Array("CommandButton4", "CommandButton5", "CommandButton6", "CommandButton7", "CommandButton8", "CommandButton9", "CommandButton10", "CommandButton11", "CommandButton12", "CommandButton13")
For i = LBound(CommandButton) To LBound(CommandButton) + Sheet1.Range("A1").Value - 1
Sheet1.Shapes(CommandButton(i)).Visible = True
Next i
Application.ScreenUpdating = True
End Sub
Need ur help plz
需要你的帮助请
回答by MiVoth
As said in the comment you should rename your buttons. That just makes things easier.
正如评论中所说，您应该重命名按钮。这只会让事情变得更容易。
You could for example name them "btn1", "btn2", "btn3" .... Your code is ok and i can't see major errors. I don't know if you want to add new buttons later.
例如，您可以将它们命名为“btn1”、“btn2”、“btn3”……您的代码没问题，我看不到重大错误。不知道你以后要不要加新按钮。
If so i would recommend something more generic. If you rename the buttons to "btn1"... then you could use something like this:
如果是这样，我会推荐一些更通用的东西。如果您将按钮重命名为“btn1”...那么您可以使用以下内容：
Private Sub CommandButton15_Click()
Dim btn As OLEObject, name As String, i As Long
i = Sheets(1).Range("A1").Value + 1
For Each btn In ActiveSheet.OLEObjects
name = btn.name
If btn.OLEType = xlButtonOnly And InStr(name, "btn") = 1 Then
If Int(Right(name, Len(name) - 3)) < i Then
btn.Visible = True
Else
btn.Visible = False
End If
End If
Next
End Sub
So you can add new buttons name them with the "btn.." pattern and you don't have to change your Code.
因此，您可以添加新按钮并使用“btn..”模式为其命名，而不必更改代码。
