如何在VBA中以编程方式添加activeX按钮，将所有行填充到一列中 [英] How to add activeX buttons programmatically in VBA, filling all rows down a column

问题描述

我在这里的第一篇文章，但是一段时间以来，已经成功地从该网站获得了解决方案和想法。因此，感谢您收集解决方案和想法。

My first post here, but have been successfully sourcing solutions and ideas from this website for a while now. So thanks for the collection of solutions and ideas.

基本上，我有一个电子表格应用程序，要求第一列Column A在每个单元格中都填充有 Active X按钮，并按给定数量循环。我在下面发布了一个这样的工作解决方案，该解决方案利用了表单类型按钮和一个模块。这体现了我认为最喜欢的工作按钮示例。一旦操作，按钮的列将对应于同一行上的相对数据，并且在单击时将打开相应的文件夹以及以后的开发中的用户窗体。

Basically, I have a spread sheet application requiring the first column, Column A, to be filled with "Active X" buttons in every cell, looping through for a given quantity. I have posted one such working solution below which makes use of "form type buttons" and a Modules. This exemplifies what I consider my most favored example with working buttons. Once operational the column of buttons will correspond to relative data on the same row, and when clicked will open corresponding folders, and userforms in later developments.

第二篇文章使用了Range功能，但显然没有包含任何与之交互的按钮。但是，在此范围上单击鼠标显然会激活Worksheet_Selection更改过程中的任何代码。很抱歉，这很明显！

The second post uses the Range function, but obviously doesn't incorporate any buttons to interactive with. However, a mouse click over this Range will obviously activate any code from within the Worksheet_Selection Change procedure...Sorry just stating the obvious!

我一直试图实现的是使用 activeX命令按钮的代码版本，但是在研究了一些很棒的教程并注入了一系列编程概念之后，我仍然惨不忍睹使用OLEObjects。

What I have been trying to achieve is a version of code employing "activeX" Command Buttons, but after having studied some great tutorials and poured over a range of programing concepts, I still fail miserably to employ OLEObjects.

如何在VBA中以编程方式在某些工作表单元格数据旁边添加按钮？

第1张程序：
子列A_Buttons（）

Sheet 1 Procedure: Sub ColumnA_Buttons()

    Dim buttons As Button
    Dim rng As Range
    Dim LineQty As Variant

        Application.ScreenUpdating = False
        ActiveSheet.buttons.Delete

    LineQty = 5

    For i = 1 To LineQty
        Set rng = ActiveSheet.Range(Cells(i, 1), Cells(i, 1))
        Set buttons = ActiveSheet.buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)

            With buttons
                .OnAction = "Buttons"
                .Caption = "Line " & i
                .Name = "Line " & i
                End With
        Next i

        Application.ScreenUpdating = True

End Sub

Public Click_Button As Variant ' Make Variable Public for Userform1

'

表单按钮模块：

Sub Line_Buttons()

 Click_Button = Application.Caller

    MsgBox Click_Button & " was Clicked"

        UserForm1.Show 'Launch custom userform

End Sub

下一个要考虑的选项是范围检测

And the next option to be considered is a range detection

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' e.g., range(A1:E1) is clicked
       If Not Application.Intersect(Target, Range("B2:B12")) Is Nothing Then
            MsgBox "You clicked " & Target.Address
       End If
End Sub

推荐答案

好。我在此处根据此帖子发布了一些我正在使用的代码：多个活动的X复选框... 。看来我现在仍然站在他们上次发表的文章的立场上：

Ok. I'm posting some code that I've been working on based on this post here: Multiple active X checkboxes... . It seems I've now come to the same stand still they did as descibed in their last post :

是的，这是单独的复选框。您可以在
VBA中模拟控制数组，以便每个复选框使用相同的单击事件代码，但这可能是
可能会导致IMO失效。

"Yes it is individual checkboxes. You can emulate control arrays in VBA so that each checkbox uses the same click event code, but that is probably overkill IMO. "

如果我读了杰森（Jason）的上述文章，这就是他对事件代码的质疑。

And if I read Jason's post above, this is what he's questioning regarding the event code.

欢迎大家协助完成此代码，因为我根据上面的表单按钮模块，尚未看到将其与单个事件关联的有效示例。

Any assistance welcomed in completing this code, as I have Not yet seen a working example which interlocks it to a single event, as per the form button module above.

    Sub Macro1()

Dim objCmdBtn As Object
Dim i As Integer
Dim Rnge As Range

Set ColumnRange = Range("A:A") ' Set width & height of column A
    ColumnRange.ColumnWidth = 5: ColumnRange.RowHeight = 15.75

'Delete previous objCmdBtn
For Each objCmdBtn In ActiveSheet.OLEObjects
    If TypeName(objCmdBtn.Object) = "CommandButton" Then objCmdBtn.Delete
    Next objCmdBtn 'TypeName Function returns the data-type about a variable - TypeName(varname is objCmdBtn)



    With ActiveSheet

        For i = 1 To 25

            Set Rnge = ActiveSheet.Range(Cells(i + 1, 1), Cells(i + 1, 1))
            Set objCmdBtn = Sheets("Sheet1").OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                                     Link:=False, _
                                     DisplayAsIcon:=False, _
                                     Left:=Rnge.Left, _
                                     Top:=Rnge.Top, _
                                     Width:=Rnge.Width, _
                                     Height:=Rnge.Height)

                                     With objCmdBtn
                                        'set a String value as object's name
                                        '.Name = "CommandButton1"

                                        With .Object
                                             .Caption = i
                                             With .Font
                                                  .Name = "Arial"
                                                  .Bold = True
                                                  .Size = 7
                                                  .Italic = False
                                                  .Underline = False
                                             End With
                                        End With
                                    End With
        Next
    End With

End Sub

这篇关于如何在VBA中以编程方式添加activeX按钮，将所有行填充到一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！