删除VBA按钮的集合 [英] Deleting a collections of VBA buttons

问题描述

我正在使用以下脚本在excel中生成按钮，范围就是我希望放置的位置。

  Sub CreateAddButton（rng As Range）
 Dim btn As Button 
使用Worksheets（User）
设置btn = .Buttons.Add（rng.Left，rng.Top， 
 btn 
 .name =添加
 .Caption =添加列
 .OnAction =CreateVariable
结束
结束
 End Sub 
  

只有问题是，我想一种可以删除此方法生成的所有按钮的方法？如果可能，我想要摆脱全局变量。任何帮助将被感激地收到。
James

解决方案

我建议Tim的方法使用一个特定的名字 - 这不需要全局变量。例如，您可以为每个按钮名称添加一个
_ || ForDeletion后缀，然后在删除例程上查找

 .Name =Add_ $ c>表单按钮确实提供了另一个选择（不是双关语），您可以在属性下的 AlternativeText 中存储文本Web，并将其用作删除例程的标识符。
 
 
 
底部的删除例程向后运行，以避免在循环遍历范围时出现错误
 
 
 
  
  Sub TesMe（）
调用CreateAddButton（[a2]）
 End Sub 
 
 Sub CreateAddButton（rng As Range）
 Dim btn As Button 
带工作表（User）
设置btn = .Buttons.Add（rng.Left，rng.Top，rng.Width，rng.Height）
使用btn 
 .Name =添加
 .Capt ion =Add Column
 .OnAction =CreateVariable
 .ShapeRange.AlternativeText =MyCollection
 End with 
 End With 
 End Sub 
 
 
 Sub GetMyButtons（）
 Dim btns As Object 
 Dim lngRow As Long 
设置btns = Sheets（User）。按钮
对于lngRow = btns.Count到1步-1 
如果btns（lngRow）.ShapeRange.AlternativeText =MyCollection然后
 MsgBox找到一个，vbCritical 
 btns（lngRow）.Delete 
结束如果
下一个
结束子
  
 
I'm using the following script to produce buttons in excel, the range is just where I'd like it to be placed.
Sub CreateAddButton(rng As Range)
    Dim btn As Button
    With Worksheets("User")
        Set btn = .Buttons.Add(rng.Left, rng.Top, rng.width, rng.Height)
        With btn
           .name = "Add"
           .Caption = "Add Column"
           .OnAction = "CreateVariable"
        End With
    End With
End Sub
Only problem is, I'd like a method which can delete all the buttons produced by this method? I want to steer away from global variables if possible. Any help would be gratefully received.
James
解决方案
I'd suggest Tim's method using a specific name - which does't need a global variable. For example you could add a
"_||ForDeletion" suffix to each button name and then look for it on a delete routine
 .Name = "Add_||ForDeletion"
A Forms button does provide another alternative though (no pun intended), you can store a text sting in the AlternativeText under "Properties" then "Web" and use this as an identifier for a delete routine.

The delete routine at bottom works backwards to avoid errors when looping through a range

Sub TesMe()
    Call CreateAddButton([a2])
End Sub

Sub CreateAddButton(rng As Range)
    Dim btn As Button
    With Worksheets("User")
        Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
        With btn
            .Name = "Add"
            .Caption = "Add Column"
            .OnAction = "CreateVariable"
            .ShapeRange.AlternativeText = "MyCollection"
        End With
    End With
End Sub


Sub GetMyButtons()
    Dim btns As Object
    Dim lngRow As Long
    Set btns = Sheets("User").Buttons
    For lngRow = btns.Count To 1 Step -1
        If btns(lngRow).ShapeRange.AlternativeText = "MyCollection" Then
            MsgBox "Found one", vbCritical
            btns(lngRow).Delete
        End If
    Next
End Sub


                        
这篇关于删除VBA按钮的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！