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

Question

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

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

PS 它是一个 activeX 按钮

谢谢

Answer 1

您不能将任何对象放置在单元格“中”，只能放置在单元格上方。您可以将按钮的 Left 和 Top 属性设置为单元格的 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

Answer 2

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

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

需要考虑的事项，示例代码如下：

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

在合并单元格上[

'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$1:$D$3"

         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$1"
         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

仅适用于单个单元格 .jpg" rel="nofollow noreferrer">