如何在excel中创建动态按钮 [英] How to create a dynamic button in excel

问题描述

我正在使用数据库值填充我的电子表格。现在如果我填写了30行，那么我想在第31行或第32行上创建一个动态按钮来执行某些操作。要填充的行数不是固定的。我如何做到这一点。

解决方案

我假设您将在查询填满表后循环浏览记录一个条件和做东西。因此，因此，您要放置按钮的位置由ActiveSheet（）中的Range（）对象表示。

所以让我们在那里创建一个动态ActiveX按钮位置：

  Sub CreateDynamicButton（）
 Dim MyR As Range，MyB As OLEObject 
 Dim MyR_T As Long ，MyR_L As Long 
 
 
设置MyR = Range（C110）只是一个例子 - 你从你自己的脚本获得
 MyR_T = MyR.Top'捕获位置
 MyR_L = MyR.Left'... 
'创建按钮
设置MyB = ActiveSheet.OLEObjects.Add（ClassType：=Forms.CommandButton.1，Link：= False，DisplayAsIcon： = False）
 
'设置主按钮属性
与MyB 
 .Name =MyPrecodedButton重要 - 代码必须存在...见下面
 .Object。 Caption =MyCaption
 .Top = MyR_T 
 .Left = MyR_L 
 .Width = 50 
 .Height = 18 
。 = xlMoveAndSize 
 .PrintObject = True'或假根据您的口味
结束与
 
结束Sub 
  

如果提前 - 您在活动工作表中创建了以下例程

  Private Sub MyPrecodedButton_Click（）
 MsgBoxCo-Cooo！ 
 End Sub 
  

然后按一下上面创建的按钮，会出现一个不错的消息框测试在XP / SP2 + Excel 2003）。

创建例程不询问是否存在同名的按钮，您需要采取措施只创建一次具有相同的名称。如果你调用例程两次，那么 .Name =...将会默默地失败，并开始命名按钮CommandButton1和up。

所以你应该有所有的配料来创建你的按钮。如果他们需要采取不同的行动，他们每个人都将需要一个预编程的程序。我应该提到，在OLE对象的安装之后，您无法调试（逐步）创建例程，因为控件在Excel之外传输 - 这不是一个错误，这是一个功能！

我不得不承认我听起来有点不寻常，我可能不喜欢安装动态按钮作用于预先编码的Sub，而是在查询之前做一个初始对话框，通过复选框，如截断之后X行（Y / N）等 - 但你会有很好的理由做你的方式。

希望有帮助 - 祝你好运

I'm populating my spreadsheet with Database values.Now if I 30 rows were filled then I want to create a dynamic button on say 31st or 32nd row for doing some action.The number of rows that will be populated is not fixed.How can I do this.

解决方案

I assume that you will cycle through the records after the query has filled your table, search for a condition and "do stuff". I therefore asume that the location where you want to place the button is represented by a Range() object within the ActiveSheet()

So let's create a dynamic ActiveX button at that location:

Sub CreateDynamicButton()
Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long


    Set MyR = Range("C110") 'just an example - you get that from your own script
    MyR_T = MyR.Top         'capture positions
    MyR_L = MyR.Left        '...
    'create button
    Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)

    'set main button properties
    With MyB
        .Name = "MyPrecodedButton"     'important - code must exist ... see below
        .Object.Caption = "MyCaption"
        .Top = MyR_T
        .Left = MyR_L
        .Width = 50
        .Height = 18
        .Placement = xlMoveAndSize
        .PrintObject = True            'or false as per your taste
    End With

End Sub

If - in advance - you have created following routine within the active sheet

Private Sub MyPrecodedButton_Click()
    MsgBox "Co-Cooo!"
End Sub

then a nice message box will appear once you press the button created above (tested under XP/SP2 + Excel 2003).

The Create routine doesn't ask if a button of same name exists, you need to take measures to create it only once with the same name. If you call the routine twice, the .Name = "..." will silently fail and start naming the button "CommandButton1" and up.

So you should have all ingredients now to create your button(s). Each of them will need to have a precoded procedure if they need to act differently. I should mention you cannot debug (step through) the Create routine after the cration of the OLE object, because control is transfered outside Excel - "it's not a bug, it's a feature!"

I have to admit for me it sounds a bit unusual and I would probably prefer not to install dynamic buttons acting on pre-coded Sub's, instead I would do an initial dialog before the query giving options via checkboxes like "Truncate after X rows (Y/N)" and the like - but you will have good reasons for doing it your way.

Hope that helps - good luck

这篇关于如何在excel中创建动态按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！