I'm trying to create a button that will allow me to attach Word/Excel/PDF files. I've read a bunch of other articles and tutorials, so I now have the button and a VBA macro that gives me a dialogue box to browse to my file of choice. The file can be selected and embeds in the Excel fine.
Issue I'm having is with getting the position of the embedded file to sit next to the button I've created. At the moment it always defaults to the top left of the active sheet, despite my best efforts to hard code a different position in.
So two questions:
Thanks
Here's my code so far:
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
I think you have correct approach. Try change your code to something like this
With attachment
.Top = cells("H89").top
.Left = cells("H89").left
End With
im not currently near VBA, but it was something like that