我正在尝试创建一个按钮,允许我附加Word/Excel/PDF文件。我读了很多其他的文章和教程,所以我现在有了按钮和VBA宏,它给了我一个对话框,可以浏览到我选择的文件。可以选择该文件并将其嵌入Excel中。
我遇到的问题是如何将嵌入文件的位置放在我创建的按钮旁边。目前,它总是默认在活动表的左上角,尽管我尽了最大努力在中硬编码一个不同的位置
所以有两个问题:
- 如何设置OLEObject的位置
- 有没有一种方法可以识别命令按钮的单元格引用/位置,然后设置OLEObject相对于它的位置?例如,按钮右侧的两列
感谢
到目前为止,这是我的代码:
Sub AttachFile()
'Identify the cell the command button is in and set the location for attachment icon to be 3 columns to the right
Dim buttonName As String
Dim buttonAddress As String
Dim buttonLocation As Range
Dim iconLocation As Range
buttonName = ActiveSheet.Shapes(Application.Caller).Name
buttonAddress = ActiveSheet.Shapes(buttonName).TopLeftCell.Address
Set iconLocation = Range(buttonAddress).Offset(0, 3)
'Browse for the file
Dim vFile As Variant
vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert")
If LCase(vFile) = "false" Then Exit Sub
'Embed the selected file
Dim attachment As OLEObject
Set attachment = ActiveSheet.OLEObjects.Add( _
Filename:=vFile, _
Link:=False, _
DisplayAsIcon:=True)
'Reposition the icon to be next to the command button
ActiveWindow.Zoom = 100
With attachment
.Top = iconLocation.Top
.Left = iconLocation.Left
End With
ActiveWindow.Zoom = 70
End Sub
我认为你有正确的方法。尝试将您的代码更改为类似的代码
With attachment
.Top = cells("H89").top
.Left = cells("H89").left
End With
我目前还没有接近VBA,但它有点像