0

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:

  1. How do I set the position for an OLEObject?
  2. Is there a way I can identify the cell reference/position of the command button, and then set the position of the OLEObject relative to it? For example, two columns to the right of the button.

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
Teamothy
  • 2,000
  • 3
  • 16
  • 26
JKay85
  • 27
  • 7

1 Answers1

0

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

Luboš Suk
  • 1,526
  • 14
  • 38
  • Thanks Lubos. The position of the embedded file has moved from A1 - unfortunately it's not gone to H89 but E93 though. – JKay85 May 09 '16 at 08:39
  • @JKay85 Please update your code to reflect what you actually have now. – Rory May 09 '16 at 08:40
  • @JKay85 so take a look at `ActiveWindow.Zoom` i think you need to set it to 100 for this manipulatios (dunno why microsoft cant calculate with their own zoom) and also check, if any of your object dont have whitespaces around them – Luboš Suk May 09 '16 at 08:54
  • So I need to set zoom to 100%, set the position as you described, and then reset the zoom to what I want? Seems odd, but I'll give it a try – JKay85 May 09 '16 at 09:06
  • @JKay85 yeah but there is much more odd issues with MS excel VBA – Luboš Suk May 09 '16 at 09:15
  • It worked! Cheers @LubošSuk :-) So that's Q#1 sorted... Now does anyone have any idea how I make this dynamic rather than having to hardcode the cell references? – JKay85 May 09 '16 at 09:54
  • @JKay85 it depens on what exactly you wana. You can count shapes and depending on that make some cell offset, so shapes will be next to each other or beneath and above... this question is so unclean – Luboš Suk May 09 '16 at 10:01
  • Apologies for the lack of 'cleanliness'... I would like to click on a form button that I have assigned the above macro to and have the icon for the embedded file to be located two columns to the right of the button's location. – JKay85 May 09 '16 at 10:04
  • @JKay85 so you can get button position, width and from it you can calculate icon position. Almost each object in VBA have `.top`, `.left`, `.height` and `.width` – Luboš Suk May 09 '16 at 10:09
  • How to identify the button that has been clicked though? I'm assigning the same macro to 10 different buttons. – JKay85 May 09 '16 at 10:10
  • @JKay85 look for something like `Me.Caller` or `application.Caller` not sure right now which is correct. But it will returns the name of object which called macro. And with that name you can achieve your goal – Luboš Suk May 09 '16 at 10:12
  • Sorted it! Used the following to identify the button that was clicked, get the address and use it to set a relative reference to be used in the previously described procure. 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) – JKay85 May 16 '16 at 08:48