Excel VBA在单元格旁边创建一个按钮 [英] Excel VBA create a button beside cell

问题描述

对于我的问题，我想在单元格旁边创建一个不为NULL或的按钮。按钮的标题必须遵循旁边的单元格中的值。

for my question I would like to create a button beside the cell that is not NULL or "". The caption for the button must follow the value in the cell beside.

例如：

1. 我在范围（D3）中输入'EMPLOYEE'


2. 我希望宏创建一个名为 Range（C3）中的EMPLOYEE


3. 但是，我希望宏是动态的，所以每次输入值D中，左侧的单元格 C3 将会出现一个按钮。

1. I typed 'EMPLOYEE' in Range("D3")
2. I want the macro to create a button named "EMPLOYEE" in the Range("C3")
3. However I want the macro to be dynamic so that every time I type in the value in the column 'D', the cell on the left side - C3 will comes out a button.

因此，我已经弄清楚，我需要手动编写 CommandButton 是正确的？

Therefore, I've figured out that I needed to code for the CommandButton manually is that right?

尽管如此，百万感谢所有人。

Nevertheless, million thanks in advance for all.

推荐答案

你可以通过添加一个命令按钮来查看它的创建方式，然后结合花哨的部分来记录一个宏。注意OLE Command按钮对象的属性，要多注意一下。

You may record a macro by adding a command button to see how it's created and then incorporate the fancy parts. Note on properties of OLE Command button object, pay more attention to them.

例如 theButton.Name 尚未通过 theButton.Object.Caption 等设置标题。

e.g. theButton.Name yet for caption is set via theButton.Object.Caption etc.

以下是一段代码片段： -

Option Explicit

Sub createButtons()
Dim theButton As OLEObject
Dim rngRange As Range
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rngRange = Sheets(2).Range("B2")

    For i = 0 To 9
        If rngRange.Offset(i, 0).Value <> "" Then
        With rngRange.Offset(i, 1)
            Set theButton = ActiveSheet.OLEObjects.Add _
                (ClassType:="Forms.CommandButton.1", _
                Left:=.Left, _
                Top:=.Top, _
                Height:=.Height, _
                Width:=.Width)

                theButton.Name = "cmd" & rngRange.Offset(i, 0).Value
                theButton.Object.Caption = rngRange.Offset(i, 0).Value

                '-- you may edit other properties such as word wrap, font etc..
      End With
      End If
    Next i

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

输出：

这篇关于Excel VBA在单元格旁边创建一个按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！