在单元格中放置一个命令按钮 MS Excel vba

Place a command button in a cell MS Excel vba

提问by Ank

I want to place a command button in a cell though VBA code. Say position B3. I used macro recorder for this purpose but it gives me the top bottom values of the button. I don't want that cuz if I take my code to some other computer with another screen resolution, the code will fail. A cell position (example B3) will be an absolute position.

我想通过 VBA 代码在单元格中放置一个命令按钮。说位置 B3。我为此使用了宏记录器，但它为我提供了按钮的顶部底部值。我不想要那个，因为如果我将我的代码带到其他具有其他屏幕分辨率的计算机上，代码将失败。单元格位置（示例 B3）将是绝对位置。

Can you suggest me a way to do this.

你能建议我一种方法来做到这一点。

P.S Its an activeX button

PS它是一个activeX按钮

Thanks

谢谢

回答by Tim Williams

You can't place any object "in" a cell, only over it. You can set the button's Left and Top properties to the Cell's Left/Top.

您不能将任何对象“放在”单元格中，只能放在它上面。您可以将按钮的 Left 和 Top 属性设置为 Cell 的 Left/Top。

Sub Tester()
    Dim rng As Range
    Set rng = ActiveSheet.Range("B3")
    With ActiveSheet.OLEObjects("CommandButton1")
        .Top = rng.Top
        .Left = rng.Left
        .Width = rng.Width
        .Height = rng.RowHeight
    End With
End Sub

回答by John

An Alternative to "putting a button in a cell"

“在单元格中放置按钮”的替代方法

Make a cell selection execute code, directly, using the worksheet event: Worksheet_SelectionChange. Place the code in the specific sheet's module. Color/Border/Text the cell as you please. A cell-is-a-cell on any computer or screen. I use this to reference help loaded into a userForm, from a look up on a help sheet, when the user clicks on a short label/description. Using cell/buttons avoids Active-X objects complaints from IT.

请使用工作表事件的小区选择执行代码，直接：Worksheet_SelectionChange。将代码放在特定工作表的模块中。根据需要为单元格着色/边框/文本。任何计算机或屏幕上的单元格就是一个单元格。当用户单击短标签/描述时，我使用它来引用加载到用户窗体中的帮助，从帮助表上的查找。使用单元格/按钮可避免来自 IT 的 Active-X 对象投诉。

Things to think on, with the sample code, following:

需要考虑的事情，使用示例代码，如下：

1. Target.Address returns absolute addresses, using the "$" character
2. Use the Select Casein your code, even if you have one cell/button. This eases the path to adding cell/buttons, later
3. consider using named ranges on the spreadsheet, and reference them in the code. That way, VBA won't care if you move the cell/button
4. If you have merged cells for which you create a named range, remember that the named range, in the spreadsheet, only points to the top-left cell
   • However, the Target.Addressfor the merged area returns the full range, not just one cell. If your Select Caserefers to the address of the Target's top-left cell, you avoid this problem.
   • use Target.Cells(1,1).Address
5. Bad choice for merged cells: don't use MergeArea.Address(MergeAreawill not work on merged cells [only works on single cells]; it returns the merged range within which a cell lives.

1. Target.Address 使用“$”字符返回绝对地址
2. 使用Select Case在你的代码，即使你有一个单元/按钮。这简化了稍后添加单元格/按钮的路径
3. 考虑在电子表格上使用命名范围，并在代码中引用它们。这样，VBA 不会在乎您是否移动单元格/按钮
4. 如果您合并了单元格并为其创建了命名范围，请记住，电子表格中的命名范围仅指向左上角的单元格
   • 但是，Target.Address合并区域的 返回整个范围，而不仅仅是一个单元格。如果您Select Case指的是Target左上角单元格的地址，则可以避免此问题。
   • 用 Target.Cells(1,1).Address
5. 合并单元格的错误选择：不要使用MergeArea.Address(MergeArea不适用于合并单元格 [仅适用于单个单元格]；它返回单元格所在的合并范围。

*Sample Code*

*示例代码*

'How to Make Cells into Buttons that execute code
' place code in the specific Worksheet module of interest
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   ' in this example, I create named ranges on the spreadsheet '
            ' [complicated names, here, so you can read what's what]:
      ' one cell:  "Button_OneCellNameRange"
      ' one set of merged cells:  "Button_MergedCellNameRange"
         ' [the reference is the top-left cell, only]

   ' a VBA created cell/button location [not very useful for later sheet edits]
      Dim myVBACellButton As Range
      Set myVBACellButton = Range("B2")

         Debug.Print "Target Address: " & Target.Address
               'merged cells will return a range: eg "$A:$D"

         Debug.Print "Target.Cells(1,1).Address: " & Target.Cells(1, 1).Address
               'merged cells will return the top left cell, which would match
                  ' a named reference to a merged cell
   Select Case Target.Cells(1, 1).Address
        'if you have merged cells, you must use the ".cells(1,1).address"
        ' and not just Target.Address

      Case Is = "$A"
         MsgBox "Hello from: Click on A1"

      Case Is = myVBACellButton.Address
         MsgBox "Hello from: Click on B2, a VBA referenced cell/button"
            ' "myCellButton" defined as range in VBA

      'using a range named on the spreadsheet itself ...
         ' named ranges allow one to move the cell/button freely,
         ' without VBA worries
      Case Range("Button_OneCellNameRange").Address
         MsgBox "Hello From: Button Click on Button_OneCellNameRange"

      Case Range("Button_MergedCellNamedRange").Address
         'note that the address for merged cells is ONE CELL, the top left
         MsgBox _
            "Hello from: Button_MergedCellNamedRange.Address: " _
                  & Range("Button_MergedCellNamedRange").Address _

      Case Else ' normally you wouldn't be using this, for buttons
         MsgBox "NOT BUTTONS"

   End Select
End Sub