excel - Excel VBA - 使用分配的代码动态创建按钮
问题描述
我正在尝试动态创建一些按钮,并为它们分配代码。
以下代码有效
Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long
Set MyR = Cells(i + 1, 11) 'just an example - you get that from your own script
MyR_T = MyR.Top 'capture positions
MyR_L = MyR.Left '...
'create button
Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)
'set main button properties
With MyB
.Name = "MyPrecodedButton" 'important - code must exist ... see below
.Object.Caption = "MyCaption"
.Top = MyR_T
.Left = MyR_L
.Width = 50
.Height = 18
.Placement = xlMoveAndSize
.PrintObject = True 'or false as per your taste
End With
它在我的循环中创建按钮。但是,我想为点击分配一些东西,所以我使用以下代码
Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long
Set MyR = Cells(i + 1, 11) 'just an example - you get that from your own script
MyR_T = MyR.Top 'capture positions
MyR_L = MyR.Left '...
'create button
Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)
'set main button properties
With MyB
.OnAction = "interpHere"
.Name = "MyPrecodedButton" 'important - code must exist ... see below
.Object.Caption = "MyCaption"
.Top = MyR_T
.Left = MyR_L
.Width = 50
.Height = 18
.Placement = xlMoveAndSize
.PrintObject = True 'or false as per your taste
End With
Sub interpHere()
MsgBox "hi"
End Sub
我基本上已经添加了.OnAction = "interpHere"但是当我运行它时,我得到一个错误,无法设置 onaction 属性。
我哪里错了?
解决方案
试试这个代码
Sub CreateButtons()
Dim btn As Button
ActiveSheet.Buttons.Delete
Dim t As Range
For i = 2 To 6 Step 2
Set t = ActiveSheet.Cells(i, 3)
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "interpHere"
.Caption = "Btn " & i
.Name = "Btn" & i
End With
Next i
End Sub
Sub interpHere()
MsgBox "hi"
End Sub
推荐阅读
- perl - 计算特定子字符串之间的匹配
- php - 如何在 php 中执行 scrapy 脚本?
- python - 散景:绘制多个图形时的跨度
- css - CSS媒体查询选择宽度间隔的补码
- python - Python 2.7.6:使用 pip install 时“没有名为 etree.ElementTree 的模块”
- asp.net - '从'System.DateTime'到'System.TimeSpan'的无效转换。'
- kotlin - 无法使用 Kotlin 编译内联函数
- arrays - 从 mongodb 获取数据后渲染模板
- bash - 连接多个目录名称在bash中添加一个计数器
- java - 二叉搜索树按字母搜索字符串