Excel VBA 在单元格旁边创建一个按钮
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13994014/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA create a button beside cell
提问by 4 Leave Cover
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.
对于我的问题，我想在单元格旁边创建一个不是 NULL 或“”的按钮。按钮的标题必须跟在旁边单元格中的值之后。
For example:
例如：
- I typed 'EMPLOYEE' in
Range("D3") - I want the macro to create a button named "EMPLOYEE" in the
Range("C3") - 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 -
C3will comes out a button.
- 我输入了“员工”
Range("D3") - 我希望宏在
Range("C3") - 但是我希望宏是动态的，这样每次我在“D”列中输入值时，左侧的单元格 -
C3都会出现一个按钮。
Therefore, I've figured out that I needed to code for the CommandButtonmanually is that right?
因此，我发现我需要CommandButton手动编码，对吗？
Nevertheless, million thanks in advance for all.
尽管如此，万提前感谢所有。
回答by bonCodigo
You may record a macroby 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.
您可以通过添加命令按钮来记录宏以查看它是如何创建的，然后合并花哨的部分。注意OLE Command 按钮对象的属性，多加注意。
e.g. theButton.Nameyet for caption is set via theButton.Object.Captionetc.
例如theButton.Name尚未通过theButton.Object.Caption等设置标题。
Here is a code snippet to get you going:-
这是一个让您前进的代码片段：-
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
Output:
输出：
回答by StoriKnow
Try this out.
试试这个。
Public Sub Worksheet_Change(ByVal Target As Range)
Dim col As Integer
Dim row As Integer
col = Target.Column
row = Target.row
If Not IsNull(Target.Value) And Not IsEmpty(Target.Value) Then
Application.EnableEvents = False
Buttons.Add Cells(row, col - 1).Left, Cells(row, col - 1).Top, Cells(row, col - 1).Width, Cells(row, col - 1).Height
Application.EnableEvents = True
End If
End Sub
Open up the Developer Tab --> Visual Basic, double click "Sheet1", then paste this code in there. Test it by typing text in a cell on Sheet1 then moving away from that cell (e.g. by pressing Enter).
打开 Developer Tab --> Visual Basic，双击“Sheet1”，然后将这段代码粘贴到那里。通过在 Sheet1 上的单元格中键入文本然后移开该单元格（例如通过按 Enter）来测试它。
